How Do I creat a query in VB on the fly

[DAHMB]

I want to create a sql qry in vb that will give me info that I can use further down in the code.

I want to access the fields Rank and HireDate in my query qryEmployees for the EmployeeID used in my form that I am running the code from (these fields are not used in the form but EmployeeID from the same query is). I then want to use the values obtained freom these fields in an If statement in my code.

Thanks
Dan

[DonRayner]

Warning this is air code
Replace YourTableName, YourForm and YourControl with your own names.

dim db as dao.database, rs as dao.recordset
set db = currentdb()
set rs = db.openrecordset("YourTableName")
rs.movefirst
do while not rs.eof
if rs!EmployeeID = forms!YourForm!YourControl then
 
add your code here
 
rs.movelast
else
rs.movenext
end if
loop
 
rs.close
set rs = nothing
set db = nothing
 
rest of your code here

[DAHMB]

Thank You! that was it!!!!

[DonRayner]

You're quite welcome, glad I could help.

[NeoPa]

I suspect you are better off simply using the DLookup() function.