Runing a query from VBA

[Guest]

I have the following code to step through records in a query and I need help
adding a parameter to the query.

Public Sub Command23_Click()
Dim rst As DAO.Recordset
Dim DAO As DAO.Database
Dim strUser As String
Dim MyEmplNum As Long
strUser = Environ("Userprofile")
strPos = InStr(4, strUser, "\")
strlen = InStr(strPos, strUser, "\")
MyEmplNum = Mid(strUser, strPos + 1, strlen)


Set db = CurrentDb()
Set rst = db.OpenRecordset("Qry_recSet_User")
With rst
If Not (.EOF And .BOF) Then
Do Until .EOF
MsgBox ("ID = " & rst.Fields("ID"))
rst.MoveNext
Loop

"Qry_recSet_User" is the name of the following select query

SELECT Tbl_XFDP.ID, Tbl_XFDP.Firms_Number, Tbl_XFDP.VCP_IT_Liaison
FROM Tbl_User INNER JOIN Tbl_XFDP ON Tbl_User.EmpName =
Tbl_XFDP.VCP_IT_Liaison
WHERE (((Tbl_User.EmpNum)=595930));

I would like to either replace the hard coded parameter in the Where clause
(595930) with a variable - MyEmplNum or completely define the query in VBA.

Your help is much appercaited.

 

[Jayyde]

Create another string and pass it instead of the name of the existing query:

Dim strSQL

strSQL = "SELECT Tbl_XFDP.ID, Tbl_XFDP.Firms_Number, Tbl_XFDP.VCP_IT_Liaison
" _
& "FROM Tbl_User INNER JOIN Tbl_XFDP ON Tbl_User.EmpName =
Tbl_XFDP.VCP_IT_Liaison " _
& "WHERE (((Tbl_User.EmpNum)=" & yourVariableHere & "));"

set rst = db.OpenRecordset(strSQL)


hth
-Jayyde

 

[Guest]

A few tweaks here and there, mainly syntax, and it works like a charm.
Thanks !!!