insert into query not working

[davyre]

Hi,

I have this query

PONumber = PObox.Value
UDD = DLookup("customerUDD", "TblCustOrder", "PONumber='" & PONumber & "'")
custID = DLookup("customerID", "TblCustOrder", "PONumber='" & PONumber & "'")
custOrderID = DLookup("custOrderID", "TblCustOrder", "PONumber='" & PONumber & "'")
qry = "SELECT * FROM TblCustOrderUnit WHERE OrderID=" & DLookup("CustOrderID", "TblCustOrder", "PONumber='" & PONumber & "'")
MsgBox (PONumber & vbCrLf & UDD & vbCrLf & custID & vbCrLf & custOrderID)
Set rst = dbs.OpenRecordset(qry)
rst.MoveFirst

Do While rst.EOF = False
    UnitID = rst("UnitID")
    qty = rst("Qty")
    MsgBox ("unitID=" & UnitID & vbCrLf & "qty=" & qty)
    [highlight #FCE94F]insertTblOrderUnitQry = "INSERT INTO TblOrderUnit([OrderID],[UnitID],[QtyOrdered],[PONumber],[CustomerID]) " _
                          & "VALUES (" & custOrderID & "," & UnitID & "," & qty & ",'" & PONumber & "'," & custID & ")"[/highlight]
    DoCmd.SetWarnings False
    DoCmd.RunSQL insertTblOrderUnitQry
    DoCmd.SetWarnings True
    rst.MoveNext
Loop

Why my query does not work? It does not add a record to TblOrderUnit at all, eventhough there is no error showing. Any help?
Thanks!

 

[dhookom]

I'm not sure why you use DLookup("custOrderID", "TblCustOrder", "PONumber='" & PONumber & "'") twice.

What do you get if you add

debug.Print insertTblOrderUnitQry

in your loop

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

I tried to add debug.print and there are nothing came up on screen

The dlookups I used are different, one to look up custOrderId and the other one is to look up customerID. Both are two different fields but in one table

 

[dhookom]

The DLookup() I mentioned is used twice in your code. Where did you place the debug.print in your code?

Duane
Hook'D on Access
MS Access MVP

 

[davyre]

ok, I made some changes in my code :

Private Sub OKbutton2_Click()

Dim PONumber As String
Dim UDD As Date
Dim custID As Long
Dim CustOrderID As Long
Dim UnitID As Long
Dim dbs As Database
Dim rst As DAO.Recordset
Dim OrderID As Long
Dim Qty As Long
Dim ModelID As Long
Dim insertTblOrderUnitQry As String
Dim qry As String

Set dbs = CurrentDb

PONumber = PObox.Value
UDD = DLookup("customerUDD", "TblCustOrder", "PONumber='" & PONumber & "'")
custID = DLookup("customerID", "TblCustOrder", "PONumber='" & PONumber & "'")
CustOrderID = DLookup("custOrderID", "TblCustOrder", "PONumber='" & PONumber & "'")
qry = "SELECT * FROM TblCustOrderUnit WHERE OrderID=" &[highlight #F57900] CustOrderID[/highlight]
MsgBox (PONumber & vbCrLf & UDD & vbCrLf & custID & vbCrLf & CustOrderID)
Set rst = dbs.OpenRecordset(qry)
rst.MoveFirst

Do While rst.EOF = False
    UnitID = rst("UnitID")
    Qty = rst("Qty")
    MsgBox ("unitID=" & UnitID & vbCrLf & "qty=" & Qty)
    insertTblOrderUnitQry = "INSERT INTO TblOrderUnit([OrderID],[UnitID],[QtyOrdered],[PONumber],[CustomerID]) " _
                          & "VALUES (" & CustOrderID & "," & UnitID & "," & Qty & ",'" & PONumber & "'," & custID & ")"
    DoCmd.SetWarnings False
    DoCmd.RunSQL insertTblOrderUnitQry
    DoCmd.SetWarnings True
    [highlight #F57900]Debug.Print insertTblOrderUnitQry[/highlight]
    rst.MoveNext
Loop

Set rst = Nothing
Set dbs = Nothing

DoCmd.Close acForm, "FrmDialogAutoAdd"
Forms!FrmOrder1.ListOrderID.Requery
End Sub

That was my complete sub code. I made changes in "qry", so I used the variable CustOrderID instead of dlookup again. And I placed the debug.print after the query "insertTblOrderUnitQry" is being run. But nothing happened and the result still the same. Notice that I placed some msgbox to show the values of the variables, and it did show values that supposed to be placed in the tblOrderUnit. Any idea?

 

[davyre]

update: In my immediate window, there is a line:
INSERT INTO TblOrderUnit([OrderID],[UnitID],[QtyOrdered],[PONumber],[CustomerID]) VALUES (10,6,1,'025539',56)

but still no values/records added in the tblOrderUnit

 

[davyre]

update: OK I found the problem. The OrderID in the loop was wrong, instead of using custOrderID, I should've used frmOrderID(OrderID in order form). Thank you