Solved Print SQL Select Result in VBA

[LiziM]

Hello I am new to VBA/ SQL in Access and require some help.

I am designing a production tracking database that records the date and time when a product enters a new workstation/warehouse. I would like to run a query through VBA which takes the product serial number that a user scans in and then finds the previous warehouse it was in (by going to the last entry in the master table "tbl_Transaction_Master" and returning the field "Warehouse" for the last record with that serial number. The trouble is, I would like to print the result somehow so I can check it is behaving as expected, but when I try use the "OpenRecordset" function, I get an error "Too few parameters, 2 expected". I have added the code and a screenshot of the table (I have ben using the serial number "MT1AG14572H" to test with).

Private Sub Command6_Click()
Dim maxID As Long
Dim SQLInsert, SQLPreceeding As String
Dim result As DAO.Recordset
If Me.cb_Warehouse <> 1 Then
maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=[Forms]![frm_scan_IN]![tb_ProductSerialNumber].Value")
SQLPreceeding = "SELECT [tbl_Transaction_Master.Transaction_ID], [tbl_Transaction_Master.Warehouse] FROM tbl_Warehouses INNER JOIN tbl_Transaction_Master ON tbl_Warehouses.ID = tbl_Transaction_Master.Warehouse WHERE (((tbl_Transaction_Master.Transaction_ID)=maxID) AND ((tbl_Transaction_Master.ProductSerialNumber)=[Forms]![frm_scan_IN]![tb_ProductSerialNumber]))"
Debug.Print (SQLPreceeding)
Set result = CurrentDb.OpenRecordset(SQLPreceeding) 'this is where my error occurs
End If
End Sub

 

[Minty]

You need to concatenate the results.

Private Sub Command6_Click()
    Dim maxID As Long
    Dim SQLInsert, SQLPreceeding As String
    Dim result As DAO.Recordset
    
    If Me.cb_Warehouse <> 1 Then
        maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=[Forms]![frm_scan_IN]![tb_ProductSerialNumber].Value")
        SQLPreceeding = "SELECT [tbl_Transaction_Master.Transaction_ID], [tbl_Transaction_Master.Warehouse] FROM tbl_Warehouses INNER JOIN tbl_Transaction_Master ON tbl_Warehouses.ID = tbl_Transaction_Master.Warehouse "
        SQLPreceeding = SQLPreceeding  & " WHERE (((tbl_Transaction_Master.Transaction_ID)= maxID) AND ((tbl_Transaction_Master.ProductSerialNumber)= '" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] "')) "
        Debug.Print (SQLPreceeding)
        Set result = CurrentDb.OpenRecordset(SQLPreceeding) 'this is where my error occurs
    End If
    
End Sub

 

[LiziM]

You need to concatenate the results.

Private Sub Command6_Click()
    Dim maxID As Long
    Dim SQLInsert, SQLPreceeding As String
    Dim result As DAO.Recordset
   
    If Me.cb_Warehouse <> 1 Then
        maxID = DMax("Transaction_ID", "tbl_Transaction_Master", "ProductSerialNumber=[Forms]![frm_scan_IN]![tb_ProductSerialNumber].Value")
        SQLPreceeding = "SELECT [tbl_Transaction_Master.Transaction_ID], [tbl_Transaction_Master.Warehouse] FROM tbl_Warehouses INNER JOIN tbl_Transaction_Master ON tbl_Warehouses.ID = tbl_Transaction_Master.Warehouse "
        SQLPreceeding = SQLPreceeding  & " WHERE (((tbl_Transaction_Master.Transaction_ID)= maxID) AND ((tbl_Transaction_Master.ProductSerialNumber)= '" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] "')) "
        Debug.Print (SQLPreceeding)
        Set result = CurrentDb.OpenRecordset(SQLPreceeding) 'this is where my error occurs
    End If
   
End Sub

Thanks for the response. Unfortunately that produces a syntax error "Expected end of statement" at the line with "SQLPreceeding=SQLPreceeding & "WHERE..."I am not sure why as I am not familiar with SQL syntax in Access VBA...

 

[Minty]

Apologies typo in my text editor, missed an & near the end:

SQLPreceeding = SQLPreceeding & " WHERE (((tbl_Transaction_Master.Transaction_ID)= maxID) AND ((tbl_Transaction_Master.ProductSerialNumber)= '" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] & "')) "

 

[Gasman]

So show the result of Debug.Print (SQLPreceeding) after each line?

I would remove the ( from Select ( to start with?

 

[LiziM]

Apologies typo in my text editor, missed an & near the end:

SQLPreceeding = SQLPreceeding & " WHERE (((tbl_Transaction_Master.Transaction_ID)= maxID) AND ((tbl_Transaction_Master.ProductSerialNumber)= '" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] & "')) "

Thank you very

Apologies typo in my text editor, missed an & near the end:

SQLPreceeding = SQLPreceeding & " WHERE (((tbl_Transaction_Master.Transaction_ID)= maxID) AND ((tbl_Transaction_Master.ProductSerialNumber)= '" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] & "')) "

Thanks for the help! I ultimately opted to use DLookup instead though.