Select Statement Help

[mafhobb]

Hi

I have a table called "tbl-users" and I need to get from it the UserID that has a HCAUserID that matches strloginname. strloginname is actually a number.

I am trying to use a SELECT statement.

Dim Name As String

Name = "SELECT [UserID] FROM [Tbl-Users] WHERE [HCAUserID=Strloginname]"

MsgBox Name

All I get in the message box is what's between quotations.

What am I doing wrong?

 

[pbaldy]

You can't use a SELECT statement that way (and the syntax is wrong anyway). You'd have to open a recordset on the SQL. You're simplest solution is probably a DLookup().

 

[hk1]

Here's the DLookup code:

Dim sName As String

sName = Nz(DLookup("UserID", "Tbl-Users", "HCAUserID = '" & Strloginname & "'"), "")

MsgBox sName

 

[mafhobb]

I am trying to use the dlookup function:

Dim Name As String

MsgBox "strLoginName " & StrLoginName

Name = DLookup("[EngineerID]", "Tbl-Users", "PKEnginnerID = StrLoginName")

MsgBox Name

strloginname returns as the number 19 in the first message box.

When I run it I get the following error: Run-time error 2471. The expression you entered as a query parameter produced this error: '[StrLoginName]'

Something is not right...

mafhobb

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

 

[mafhobb]

KH1, you posted as I was typing my answer...

Your code is right, with one modification, and that is that strloginname is actually a number so I modified it by removing the ' and that took care of it. It now works!

I'll now go back and compare it with what I wrote to see what I did wrong. Hopefully I'll learn!

Thank you very much

mafhobb

 

[mafhobb]

Thanks PBaldy for the link. I'll make sure I read it.

mafhobb

 

[hk1]

Couple things. I wouldn't name my variable strLoginName if it's a number field and not a name. Suggest you rename it to something like: intUserID, lngUserID, intLoginID, lngLoginID

Also, it's a bad practice to name your variable "name". "name" is a reserved keyword. Try to use sName or strName instead. And do a search for MS Access Reserved Keywords.