Creating password screen in form (1 Viewer)

[gellerche]

I have a form I am using as a password screen. What I want is for the user to type in an ID in the txtID field, run SQL to compare txtID to the primary key field of one of my tables, and put any output in a recordset object. I am trying to start with this:

Dim CustObj As Recordset
Set CustObj = dbs.OpenRecordset(dbOpenSnapshot)
DoCmd.RunSQL "SELECT Customer!ID FROM Customer WHERE Customer!ID = Me!txtID"

and then put the SQL result in the CustObj. The only problem is that I can't put the result in CustObj. Can someone give me advice on this, or am I way off base on this?

Thank you,

Steve Geller

 

[Pat Hartman]

You need to change the DoCmd statement slightly so that VBA can resolve the variable reference since SQL cannot.

DoCmd.RunSQL "SELECT Customer.ID FROM Customer WHERE Customer.ID = '" & Me!txtID & "';"

I included single quotes around the CustomerId field. If that field is numeric, remove the single quotes.

 

[jfi]

I guess I could be reading this wrong, but if he wants to get the result of the query into the recordset shouldn't he use the SQL statement as the parameter for the openrecordset?

Set CustObj = dbs.OpenRecordset("SELECT Customer.ID FROM Customer WHERE Customer.ID ='" & Me!txtID & "'", dbOpenSnapshot)

-Joshua