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

Posted on 2004-11-24
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
    LVL 34

    Expert Comment

    declare your vaialbes like

    Public myString As String


    global myString As String

    Author Comment

    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

    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

    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

    Glad you sused it out

    >> I'm pretty much a beginner at VB

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


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now