Selecting data from table and storing it in a global variable.

I'm trying to write some code that will ask the user for their username and password. Once it validates this, it should select information about that user and store it in global variables to be used later.

In a module, I've created the following global variables:
Option Compare Database

Global g_txtUserName As String
Global g_txtLastName As String
Global g_txtFirstName As String
Global g_intGroup As Integer

On the login form, there is a login button which runs the following code when clicked:
Private Sub Command4_Click()
    Dim rs As Recordset
    Set rs = CurrentProject.Connection.Execute("Select count(*) from tblLogin where Username='" & txtUser & "' and Password = '" & txtPassword & "'")
    If Not rs.EOF Then
        If rs(0) > 0 Then
            '***store user's info in global variable for later use***
            g_txtUserName = txtUser
            g_txtLastName = CurrentProject.Connection.Execute("Select Lastname from tblLogin where Username = g_txtUserName ")
            g_txtFirstName = CurrentProject.Connection.Execute("Select Firstname from tblLogin where Username = g_txtUsername ")
            g_intGroup = CurrentProject.Connection.Execute("Select Group from tblLogin where Username = g_txtUsername ")
            Dim stLinkCriteria As String
            Select Case g_intGroup
            Case 1
                DoCmd.OpenForm "frmAdminPanel", , , stLinkCriteria
            Case 2
                DoCmd.OpenForm "frmManagementPanel", , , stLinkCriteria
            Case 3
                DoCmd.OpenForm "frmNormalUserPanel", , , stLinkCriteria
            Case Else
                MsgBox g_txtUserName, " has not been assigned to a group. Please report this error to the administrator."
            End Select
            DoCmd.Close acForm, Me.Name, acSaveNo
            MsgBox "Login Incorrect"
        End If
    End If
End Sub

This code is based on code someone else wrote for me. It validates the username and password and sets the g_txtUserName global just fine. It crashes on the next line when it tries to set the g_txtLastName global. It gives me a type mismatch error.

I'm pretty new to programming in VB, so I'm sure I'm probably missing something pretty simple.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

declare your vaialbes like

Public myString As String


global myString As String
navynuke04Author Commented:
It works with g_txtUserName declared as a global. Why wouldn't it work for the others?

I made the changes you described, and it still crashes at the same place with the same error.
didnt see your code properly

>> CurrentProject.Connection.Execute("Select Lastname from tblLogin where Username = g_txtUserName ")

This will return a recordset object, not a string, hence the error

Use dlookups instead

g_txtLastName = dLookup("[Lastname]", "tblLogin" "Username = '" & g_txtUserName & "'")

for example


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
navynuke04Author Commented:
Awesome! Changing it to use dLookups worked. I just had to add a comma between "tblLogin" and "Username =

Like I said, I'm pretty much a beginner at VB. Thanks for your help!
Glad you sused it out

>> I'm pretty much a beginner at VB

I think you're better than a beginner :-)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.