enter value into variable from sql statement. 1

[cranebill]

Each user in the employee table (linked from an odbc connection) has a unique userid. I would like to use this userid to see if a user is a team lead using the windows login.

This is what I have on a form load event

Dim strUser As String
Dim user_priv As String
strUser = fWin2KUserName
user_priv = DoCmd.RunSQL "Select team_lead_flag from qa_employee_list where userid = " & strUser

I am trying to enter the value from my query into the variable user_priv but am getting a syntax error.

Anyone have any ideas?

 

[RuralGuy]

I'm pretty sure RunSQL is for Action Queries and not Select queries. Use DLookUp() instead and since strUser will be a string value you will need to surround it with single quotes.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[PaulF]

try using DLookUp()

user_priv = DLookUP("[team_lead_flag]","qa_employee_list","[userid] = " & strUser )

PaulF

 

[LarrySteele]

The RunSQL method requires a "valid SQL statement for an action query or a data-definition query." Basically you use the RunSQL method to insert or update a table row, or modify a table definition. It's not used to return data, as you're attempting to do.

For your situation, DLookup() would be a better method. You can search Help for DLookup() or you can navigate to DLookup Function (Access 2003 VBA Language Reference). Even though it says Access 2003, the information is valid for Access 97-2007, and probably earlier versions too.

HTH,
Larry

 

[LarrySteele]

Paul,

You're missing a couple of string delimiters:

[tt]user_priv = DLookUP("[team_lead_flag]","qa_employee_list","[userid] = '" & strUser & "'" )[/tt]

- Larry

 

[PaulF]

Larry,
yes I did, thanks for correcting the error.

PaulF

 

[cranebill]

Ok... yeah dlookup looks like the way to go but I am coming up with an error...

Invalid use of null and higlights the dlookup function

so I put in a msgbox to make sure it is pulling my user id from windows... which is is...

this is what I have:

Dim strUser As String
Dim user_priv As String
strUser = fWin2KUserName
MsgBox strUser
user_priv = DLookup("[team_lead_flag]", "qa_employee_list", "[userid] = '" & strUser & "'")
MsgBox user_priv
End Sub

 

[PHV]

user_priv = Nz(DLookup("[team_lead_flag]", "qa_employee_list", "[userid] = '" & strUser & "'"), "?")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cranebill]

PHV if strUser is being populated with the windows name and then I verify that with the message box previous to the dlookup function how will a NZ help here since str_user is not null... I know I exist in the table therefore it would pull 1 record (userid is unique)

 

[cranebill]

I found the issue...

my windows login is lower case and in the table it is upper case.

Problem solved....

Thanks guys