Passing Variable (Long) to SQL WHERE clause in VBA

[LiziM]

Hi all I am new to SQL in Access VBA, so I am unsure of some syntax. The code below is producing the following error at the "DoCmd.RunSQL (SQLForceScanOUT)" line:
"Syntax error (missing operator) in query expression '[Transaction_ID]=&maxID&'"

I imagine it has something to do with my passing the "maxID" variable to my SQL insert statement. I have tried googling and searching forums, but I can only find answers regarding how to pass a date. The data type for the field "Transaction_ID" is of type Autonumber, so I don't think that maxID being a Long number should be a problem? When I try and make maxID an Integer it produces an "Overflow" error...

Private Sub btn_scan_IN_Click()
Dim maxID As Long
Dim SQLInsert, SkippedWarehouse, varCategory, ExpectedFormWarehouse, varBarcodeIdentifier, SQLForceScanOUT As String
Dim PreceedingWarehouse, ExpectedPreceedingWarehouse As Integer
Dim varProductSerialNumber As String
Dim varWarehouse As Integer
Dim MsgBoxAnswer, MsgBoxOUT As Variant
Dim varEmployeeOUT, varWarehouseOUT As Integer
Dim strEmployeeOUT, strWarehouseOUT As String
varWarehouse = Me.cb_Warehouse.Value
varProductSerialNumber = Me.tb_ProductSerialNumber.Value
maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=[Forms]![frm_scan_IN]![tb_ProductSerialNumber].Value")

''''''''''''''''''''''''''''''''''''''
'error handling check if scanned out
If CheckScanOut(varProductSerialNumber) = True Then 'if out_employee does exist in previous warehouse
    GoTo LinePreceedingWarehouse
Else
    varEmployeeOUT = DLookup("[IN_Employee]", "tbl_Transaction_Master", "[Transaction_ID] =" & maxID)
    varWarehouseOUT = DLookup("[Warehouse]", "tbl_Transaction_Master", "[Transaction_ID] =" & maxID)
    strEmployeeOUT = DLookup("[Employee]", "tbl_Employees", "[ID]=" & varEmployeeOUT)
    strWarehouseOUT = DLookup("[Warehouse]", "tbl_Warehouses", "[ID]=" & varWarehouseOUT)
    MsgBoxOUT = MsgBox("This product was not scanned out of " & strWarehouseOUT & " by " & strEmployeeOUT & "." & " Would you like to scan it out for them?", vbYesNo + vbCritical, "This product was not scanned out of the previous warehouse")
      
       If MsgBoxOUT = vbYes Then
            SQLForceScanOUT = "UPDATE tbl_Transaction_Master SET [OUT_Employee]=Forms![frm_scan_IN].[cb_Employee].Value WHERE [Transaction_ID] = &maxID&" 'need to figure out how to pass variable here
            DoCmd.RunSQL (SQLForceScanOUT)
            MsgBox ("The product has now been scanned out of " & strWarehouseOUT & "." & vbCrLf & "Please click submit again to scan the product into " & varWarehouse & ".")
            'GoTo LineCancel
       ElseIf MsgBoxOUT = vbNo Then
            MsgBox ("Ask " & strEmployeeOUT & " to scan this item out of " & strWarehouseOUT & ". Then you may try again.")
            
       End If
GoTo LineCancel

Thanks in advance!! 
End If

 

[arnelgp]

if it is a Form control, use & (concatenante), eg:

..
..
'If ProductSerialNumber is Text
maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber='" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] & "'")

'Or
'If ProductSerialNumber is Numeric
'maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber])

 

[LiziM]

if it is a Form control, use & (concatenante), eg:

..
..
'If ProductSerialNumber is Text
maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber='" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] & "'")

'Or
'If ProductSerialNumber is Numeric
'maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber])

maxID works perfectly fine and has a value. It is only when I pass that value to the SQL statement that an error occurs.
I did try your solution just in case, unfortunately I still get the same error though.

 

[arnelgp]

you have problem here?

SQLForceScanOUT = "UPDATE tbl_Transaction_Master SET [OUT_Employee]=Forms![frm_scan_IN].[cb_Employee].Value WHERE [Transaction_ID] = &maxID&" 'need to figure out how to pass variable here

the correct:

SQLForceScanOUT = "UPDATE tbl_Transaction_Master SET [OUT_Employee]=Forms![frm_scan_IN].[cb_Employee].Value WHERE [Transaction_ID] = " & maxID 'need to figure out how to pass variable here


'---------------
again, if Numeric:

"Update ... Where [Transaction_ID] = " & maxID

If Transaction_ID is Text:

"Update ... Where Transaction_ID] = '" & maxID & "'"

 

[LiziM]

Oka

you have problem here?

SQLForceScanOUT = "UPDATE tbl_Transaction_Master SET [OUT_Employee]=Forms![frm_scan_IN].[cb_Employee].Value WHERE [Transaction_ID] = &maxID&" 'need to figure out how to pass variable here

the correct:

SQLForceScanOUT = "UPDATE tbl_Transaction_Master SET [OUT_Employee]=Forms![frm_scan_IN].[cb_Employee].Value WHERE [Transaction_ID] = " & maxID 'need to figure out how to pass variable here


'---------------
again, if Numeric:

"Update ... Where [Transaction_ID] = " & maxID

If Transaction_ID is Text:

"Update ... Where Transaction_ID] = '" & maxID & "'"

Okay, I see now. Sorry about that. I did not realise you could save an SQL statement to a variable and not enclose the entire thing in quotation marks. Note taken. Thanks again!