Syntax Error in String in Query Expression - Run time error 3075

[rolltide]

I've seen many similar threads, but despite repeated efforts I cannot figure out my problem. I am running Access 2003, VB 6.5, Office XP Pro. Code excerpt is below (you can see where I've tried debugging myself). My problem is in the DLookup command.

UserName = Me.cboUserName.Value
Debug.Print "User Name is "; UserName
strPassword = DLookup("Password", "Employees", "EmpName ='" & UserName)
Debug.Print "Password is "; Password
If Me.txtPassword.Value = strPassword Then
DoCmd.Close acForm, "DatabaseLogin", acSaveNo
DoCmd.OpenForm "MenuMain", acViewNormal
Exit Sub

"cboUserName" is a combo box on form "DatabaseLogin". That is working fine. On same form users enter a password into text box "txtPassword". I am attempting to compare value of "txtPassword" to a cell "Password" in table "Employees" where "EmpName" is equal to value of "cboUserName". For explanation purposes only, let's say value of "cboUserName" is "Davis". With syntax as shown above, I receive error message {Syntax error in string in query expression 'EmpName ='Davis'.} (items inside braces are verbatim). "EmpName" is a text field. "Password" is also a text field.

I have tried multilple variations of syntax, getting other error messages in the process (compile error, you canceled the previous operation ,etc.). What am I doing wrong? Thanks in advance for your help.

[ajalwaysus]

Try this:

strPassword = DLookup("Password", "Employees", "EmpName ='" & UserName & "'")
Exit Sub

You need to wrap the UserName in single quotes on both ends of the value.

Hope this helps,
-AJ

[rolltide]

Thank you! It worked perfectly.

[NeoPa]

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[rolltide]

Thank you, NeoPa for your insight. It's helpful to a novice like me.

[NeoPa]

I'm very pleased to hear that.

Good luck and Welcome to Bytes!