DLookup Not Working?

[galvinjaf]

Good Morning,

I've attempted to create a User box that would show which user is logged into the database on the main form. When the log on box pops up, you put your username and password in and click 'ok'. Which works fine. Once logged in, you're on a navigation screen and can click to open the main 'Employee' form which is where I have a special box that I want to show which user is logged in to the employee screen. The code I have in place is written below. I don't receive any kind of error, but my field that is suppose to read which user is blank. Any ideas? I've also attached a screen grab of the form.

Private Sub Form_Load()
Me.User = Environ("Username")
Me.FullName = DLookup("User_Name", "tblUser", "User_Login ='" & Me.User & "'")
End Sub

 

[Alc]

As a starting point, if you replace

 "User_Login ='" & Me.User & "'")

with a hard-coded value that you KNOW to be valid, does it work?
If so, the problem is something to do with the way Me.User is assigned.
If not, the problem is the DLookup statement itself.

 

[galvinjaf]

Truth be told, me.user was a code I found from another source. I don't really want the 'computer name/id' to be populated as that is simply a random set of initials. I really just want the lookup statement to reference my user table and grab the user name that had logged into the form. Am I close to that with the code? I'm no programmer, so I'm not sure how to go about that.

 

[Grumm]

Can you try this :

Private Sub Form_Load()
Dim sUser as String
sUser  = Environ("Username")
Me.FullName = DLookup("User_Name", "tblUser", "User_Login ='" & sUser & "'")
End Sub

I'm not sure what that Me.User is. (Computer ID)
Do you store the Username or the Computer name ? (Both are environ variables)

Of it is not working, please give us more info about the structure of tblUser (fields) and what you use to login.

Edit : After I saw your reply, what code do you use in the logon screen ?
I guess you need to save the username in that form.

 

[galvinjaf]

The computer ID is simply the computer logon name which is not what I want to show in the user form. Here is the code for the log on form, which I suspect is not saving the user name upon closing the log on form.

Private Sub Command1_Click()
Dim userLevel As Integer

If IsNull(Me.txtLoginID) Then
MsgBox "Please enter LoginID", vbInformation, "LoginID Required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please enter password", vbInformation, "Password Required"
Me.txtPassword.SetFocus
Else
'process the job
If (IsNull(DLookup("User_Login", "tblUser", "User_login ='" & Me.txtLoginID.Value & "'"))) Or _
(IsNull(DLookup("Password", "tblUser", "Password ='" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect LoginID or Password"
Else
userLevel = DLookup("UserSecurity", "tblUser", "User_Login = '" & Me.txtLoginID.Value & "'")
DoCmd.Close
If userLevel = 1 Then
MsgBox "LoginID and Password Correct"
DoCmd.OpenForm "frmNavigation"
Else
DoCmd.OpenForm "frmNavigation_controlled_user"
End If



End If
End If
End Sub

 

[Grumm]

Ok, that is the info we need to help you out.
What you need to do is save the username that successfully logged in.
There are many ways to do this.
My preference goes to the use of TempVars.
(They don't lose the content if a specific form crashes)

So all you need to do is add 1 line in the log on form :

[...]
If userLevel = 1 Then
MsgBox "LoginID and Password Correct"
TempVars.Add "sUsername", Me.txtLoginID.Value
DoCmd.OpenForm "frmNavigation"
Else
DoCmd.OpenForm "frmNavigation_controlled_user"
End If

(You may have to move that DoCmd.Close just before opening a new form.)

Then you can use

Private Sub Form_Load()
Me.FullName = DLookup("User_Name", "tblUser", "User_Login ='" & TempVars("sUsername") & "'")
End Sub

 

[galvinjaf]

Thanks Grumm.

This worked like a charm and was exactly what I was looking for! I've been able to learn quite a bit from users such as yourself, and am very appreciative. Thank you

-J