SQL INSERT using variable problem

Hi Im having trouble with this insert statement

pstrDetailSQL = "INSERT INTO tblOrderDetail " & _
"(fldOrderID)" & _
" VALUES ('" & glngNewOrderID & "')"

MR_OS.gdbCurrent.Execute pstrDetailSQL
datOrderDetail1.RecordSource = "SELECT * FROM tblOrderDetail"
datOrderDetail1.Refresh

I know the variable glngNewOrderID is valid I used it
to populate a textbox. glngNewOrderID is of type long.
I also know the database is a valid open database

I get no error with that one but it does not insert a new record into that table. I have tried alot of different
syntax for the Insert and get syntax error messages among others.

[Glenn]

If glngNewOrderID is a long, then you don't need to enclose it in single quotes. :) :)

Yes I thought of that and tried it without out and it still does not work

pstrDetailSQL = "INSERT INTO tblOrderDetail " & _
"(fldOrderID)" & _
" VALUES (glngNewOrderID)"

I tried it several other ways and still no luck.

[Glenn]

Did you try this ?



pstrDetailSQL = "INSERT INTO tblOrderDetail " & _
"(fldOrderID)" & _
" VALUES (" & glngNewOrderID & ")"



Also what database are you using ? If it is MS SQL you could try the same insert using a tool such as ISQL.

MS Access database
I tried that and no error but still does not insert a record into tblOrderDetail...
strange but this works

pstrSQL = "INSERT INTO tblOrderMaster " & _
"(fldOrderID,fldCustomerID,fldOrderDate,fldShipDate)" & _
" VALUES ('" & glngNewOrderID & "'" & "," & _
glngCustomerID & "," & _
"#" & OrderDate & "#" & "," & _
"#" & ShipDate & "#" & ")"
MR_OS.gdbCurrent.Execute pstrSQL

this inserts a new record into the tblOrderMaster, but I am unable to insert a record into the tblOrderDetail for some reason.

the tblOrderDetail has 3 fields of type long (fldOrderID,fldPartID,fldQtyOrdered), I was under the understanding that the other columns (fields) that I did not add new values to would defualt to null values.

[Glenn]

The other fields will default as long as they are setup in the Access database as not required.

Are the OrderDetail and the OrderHeader tables linked by OrderID ? If they are, you need to make sure that OrderHeader table is filled first.

Yes they are, strange thing in tblOrderMaster there are 19 fldOrderID's 1-19 and in fldOrderDetail there are only 11 and some are duplicates... that dont make sense to me. should be 1-19 fldOrderID's in both tables since there linked by that field I would think.

[Glenn]

What you have between these two tables is called a one to many relationship. For each record in the order header table (the one) you have may records in the order detail table. Meaning each order can have multiple items.

Duplicates in the detail table are perfectly normal. The key is that an order must exist in the header table before it can be inserted into the detail table. Attempting to insert an order into the detail table without an accompanying header record will fail.

I hope this helps. :) :)

Ahh that sounds like the problem, thx

The tblOrderMaster has
fldOrderID
1-19

tblOrderDetail has
1
1
1
2
3
14
14
14
15
16

I search the tblOrderMaster and get the max fldOrderID add one to that and then add a new record to tblOrderMaster.

then dispaly a dbgrid of tblOrderDetail so the user can specify the part ID and Quantity

Seems like there should be 19 fldOrder ID's in tblOrderID Its like there were new records added to the tblOrderDetail before without records added to tblOrderDettail... thats the way the DB was when I got it.

but Ill try to add a record according to you last post to see if the insert will work. thx