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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

mbizupCommented:
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
mbizupCommented:
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

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

From novice to tech pro — start learning today.