Solved SQL Select Query in VBA- How to view results

[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 the eror occurs

MsgBox (SQLPreceeding)
End If
End Sub

 

[CJ_London]

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]

Duplicate thread ?

Solved - Print SQL Select Result in VBA

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...

www.access-programmers.co.uk