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

Posted on 2004-11-24
Medium Priority
Last Modified: 2012-06-27
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.
Question by:navynuke04
  • 3
  • 2
LVL 34

Expert Comment

ID: 12670715
declare your vaialbes like

Public myString As String


global myString As String

Author Comment

ID: 12670735
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.
LVL 34

Accepted Solution

flavo earned 2000 total points
ID: 12670774
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


Author Comment

ID: 12670795
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!
LVL 34

Expert Comment

ID: 12670843
Glad you sused it out

>> I'm pretty much a beginner at VB

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


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question