How do I use OpenRecordset?

[mackao]

Hello experts.

I'm rather green in the use of VB6 but it seems to be rather fun to make my own .exe files instead of just making MSAccess databases.

Now to my question:
In MSAccess I offen use HLookup and for what I can tell I can't use it in VB6
But I saw in this forum that i can use OpenRecordset to simulate HLookup an get the same result.

When I run this code I get a stange message:
Syntax error(missing operator) in query expression 'strUserName= John Smith'.

My code looks like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DAO.DBEngine(0).OpenDatabase("C:\Users\Marcus\Marcus jobb\Rekvisitioner vb6\Rekvisitioner-97.mdb")
Set rs = db.OpenRecordset( _
"Select [strPassword] From tblUsers Where strUserName = txtUser")
 

The data I want is from a table called tblUsers, the column is strUserName, and I'm using a listbox called txtUser as a reference.

Thank you for your help

[debasisdas]

try this

Set rs = db.OpenRecordset( _
"Select [strPassword] From tblUsers Where strUserName = " & txtUser )

[Killer42]

Don't forget to put quotes around the value, since it's a string.

Set rs = db.OpenRecordset( _
  "Select strPassword From tblUsers Where strUserName = '" & _
   txtUser & "'")

Note, since apostrophes (') are used as the string delimiters, this simple code is likely to have problems should you encounter a user name which contains an apostrophe. But that's another story.