Skip to main content
A
Anonymous
October 27, 2009
Answered

Linked fields not working correctly- PLEASE HELP DEADLINE LOOMING!

  • October 27, 2009
  • 2 replies
  • 7291 views

I have two tables. The first is called Orders and contains a autonumber field named ID. This field is linked to a field named OrderID in another table named ProductOrders. I am using a submission form with a cfinclude to submit to both tables. There should only be one Orders.ID and numerous ProductOrders.OrderID (but they should be the same number). When I submit the form there are no error messages but the ProductOrders.OrderID is blank and therefore not linked to Orders.ID. What should I be looking for? I have linked the fields in Access in properties and relationships. What else?

This topic has been closed for replies.
Correct answer Dan_Bracuk

I'm a little unclear on the SELECT statement, specifically the WHERE clause. Also, would the select statement be between query tags?

<cftransaction>

<cfinsert datasource="manna_premier" formfields="SaleDate,TerritoryManager,Distributor,DealerID,PONumber,Variable,ISOCheck,ISOCheckNumber,ISOCheckAmount,CallBack,Comments,UserID" tablename="Orders">


SELECT MAX(ID) AS OrderID
FROM Orders

where field1 = the value you just entered

and field2 = the value you just entered

<cfinsert datasource="manna_premier" formfields="OrderID,Product,Quantity,BagsCases,NewExisting,OrderComments" tablename="ProductOrders">
</cftransaction>


Yes the sql has to be in a cfquery tag.  You'll also have to convert your 2nd cfinsert to a cfquery because the orderid is not coming from a form.

This:

where field1 = the value you just entered

and field2 = the value you just entered

would resemble something like

where saledate = <cfqueryparam value = "#form.saledate#">

and TerritoryManager = <cfqueryparam value = "#form.TerritoryManager#">

etc

If this is a school assignment, you might be ok.  If it's for real, it needs a lot of work to userproof it.

2 replies

N
Nonamez
Participant
October 31, 2009

Where could I find an example of the type of form I need to use? My form is returning a comma delimited list but I don't know how to work around this. How would I structure the input form to accept multiple products with the same http://www.karanlikdunya.net OrderID number? Any help on this would be greatly appreciated.

-
-__cfSearching__-
Inspiring
October 31, 2009

I would suggest you start a new thread for your question. It is generally considered bad form to tack on a new question to someone else's thread.

ilssac
ilssac
Inspiring
October 27, 2009

First question are you reading data or writing data?

I think you are writing data, if so you have to write the orderID to all the tables that need it.  Just having the fields linked in the Access database properties and relationships is not going cause the value to propergate in some magical manner.

A
Anonymous
October 27, 2009

Thanks for your reply Ian, and yes I am trying to write data. Bear with me, I'm teaching myself this stuff and know enough to frustrate the crap out of me and not much else. I don't understand how to instruct the database to enter the Orders.ID into all corresponding ProductOrders.OrderID. Since the Orders.ID generates on submission how do I carry it over to the other table?

D
Dan_Bracuk
Inspiring
October 27, 2009

To get the order id of the record you just entered, do something like this:

select max(order_id) orderid

from your_table

where field1 = the value you just entered

and field2 = the value you just entered

etc

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices