Solved Print SQL Select Result in VBA (1 Viewer)

LiziM

New member
Local time
Today, 03:45
Joined
Feb 3, 2021
Messages
21
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).
SQL:
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
1612341278553.png
 

Minty

AWF VIP
Local time
Today, 02:45
Joined
Jul 26, 2013
Messages
8,198
You need to concatenate the results.
SQL:
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

New member
Local time
Today, 03:45
Joined
Feb 3, 2021
Messages
21
You need to concatenate the results.
SQL:
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

AWF VIP
Local time
Today, 02:45
Joined
Jul 26, 2013
Messages
8,198
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

Enthusiastic Amateur
Local time
Today, 02:45
Joined
Sep 21, 2011
Messages
7,906
So show the result of Debug.Print (SQLPreceeding) after each line?

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

LiziM

New member
Local time
Today, 03:45
Joined
Feb 3, 2021
Messages
21
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.
 

Users who are viewing this thread

Top Bottom