How to use a select case VBA to set a value to a text box in access based on information in a table?

I have a table that I'm using to store a username.. I need my vba to look at that table and depending on what username is in the table I want it to return the full name of that person.

Here is an example of what I'm trying to which I just get a blank textbox.

Private Sub UserName()

Dim us As DAO.Recordset

        Set us = CurrentDb.OpenRecordset("SELECT UserName FROM tblUserName")
        
    Select Case us!UserName
    
    Case "jschmo"
    txtUName.Value = "Joe Schmo"
    Case "jdoe"
    txtUName.Value = "Jane Doe"
    Case Default
    txtUName.Value = "Unknown"
            
End Sub

Open in new window


I don't know that much about VBA and was simply piecing this together based off of some other formulas I got help with so It's possible the formula listed above is no where near functional:)
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
You can also rewrite this as a function and place it in a seperate module (named something like modMyfunctions):

Function GetUserName()

Dim us As DAO.Recordset

        Set us = CurrentDb.OpenRecordset("SELECT UserName FROM tblUserName")
    
    if us.Recordcount = 0 then
      msgbox "No records found"
       Exit Function
    end if 

    Select Case us!UserName
    
    Case "jschmo"
    GetUserName = "Joe Schmo"
    Case "jdoe"
    GetUserName = "Jane Doe"
    Case Else
    GetUserName = "Unknown"
    End select
            
End Sub 

Open in new window



With this code, you could set the control source of your textbox to

= GetUserName()

Open in new window

Or call it from some event handler:

Me.txtUName = GetUserName

Open in new window

0
 
mbizupConnect With a Mentor Commented:
There are a ciouple of corrections here:
Private Sub UserName()

Dim us As DAO.Recordset

        Set us = CurrentDb.OpenRecordset("SELECT UserName FROM tblUserName")
    
    if us.Recordcount = 0 then
      msgbox "No records found"
       Exit sub
    end if 

    Select Case us!UserName
    
    Case "jschmo"
    txtUName.Value = "Joe Schmo"
    Case "jdoe"
    txtUName.Value = "Jane Doe"
    Case Else
    txtUName.Value = "Unknown"
    End select
            
End Sub 

Open in new window

0
 
Jarred MeyerProduction ManagerAuthor Commented:
Both of these will work! Thanks a bunch!
0
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.

All Courses

From novice to tech pro — start learning today.