Solved SQL Select Query in VBA- How to view results (1 Viewer)

LiziM

New member
Local time
Today, 16:17
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).
Code:
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 the eror occurs

MsgBox (SQLPreceeding)
End If
End Sub
1612341463493.png
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:17
Joined
Feb 19, 2013
Messages
13,584
you need to reference you external values when you build your sql

....ster.Transaction_ID)=maxID) AND.... should be .....ster.Transaction_ID)=" & maxID & ") AND....

...._Master.ProductSerialNumber)=[Forms]![frm_scan_IN]![tb_ProductSerialNumber].... should be _Master.ProductSerialNumber)=" & [Forms]![frm_scan_IN]![tb_ProductSerialNumber] & ".....

if this code is in the frm_scan_IN form then use

_Master.ProductSerialNumber)=" & Me.[tb_ProductSerialNumber] & ".....

and since serial number is text, enclose with single quotes

_Master.ProductSerialNumber)='" & Me.[tb_ProductSerialNumber] & "'.....
 

Minty

AWF VIP
Local time
Today, 14:17
Joined
Jul 26, 2013
Messages
8,912
Duplicate thread ?
 

Users who are viewing this thread

Top Bottom