Retrieve the Current User & Set the Combo Box: Access 2003

It’s been six years since I did any real Microsoft Access development.  I am currently on a short engagement to enhance an Access database for a “Major Nationwide Electronics and Stuff Retailer.”  In reality, I’m building them a completely new database.   I am hoping that this will lead to an overhaul of their department operations, which I hope will lead to a lengthy .NET conversion project.  A lot of applications actually start out this way.  I’ve worked on several Microsoft Office application to .NET conversions.   Access programming may not be as glamorous as say building .NET driven robots, but it is still pretty fun.


Retrieve the UserName


To retrieve the currently logged in user, created a helper class, and place the following method in it:



Public Function GetCurrentUserName() As String



GetCurrentUserName = Environ(“USERNAME”)


End Function


In the old days of Access programming, a complicated API call had to be made to achieve the above.   The example will also work in other Office applications.



Retrieve the UserID from the Users Table



Create a table of Users that contains the UserID, UserName, FirstName, LastName, and so on.   Then match the value returned from GetCurrentUserName() with the value that is in the UserName field of the table.  Use the following method to retrieve any given value from the Users table based upon the UserName:



Public Function GetUserNamePart(returnPart As String) As String



Dim user As String
user = GetCurrentUserName


Dim cmdText As String
cmdText = “SELECT ” & returnPart & ” FROM Users WHERE UserName = ‘” & user & “‘”


Dim cmd As New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = cmdText


Dim rst As New ADODB.recordset
Set rst = cmd.Execute


Dim namePartValue As String



If Not rst.BOF And Not rst.EOF Then


    namePartValue = rst(0).value

Else


    namePartValue = user
   
End If


rst.Close
Set rst = Nothing
Set cmd = Nothing


GetUserNamePart = namePartValue


End Function



For example, if the UserID is need, make the following call to the above method:


Public Function GetUserID() As Long



GetUserID= GetUserNamePart(“UserID”)


End Function



Set the Default Value of the Combo Box


I have forms that track various bits of data that can be associated with a person other than the current user.  I created a Combo Box that is bound to the UserID of the table that is bound to the form, while the Combo Box gets its display data from the Users table.  When the value of the Combo Box changes, it will be stored in the table that is bound to the form.  Without making any changes, the Combo Box will default to the first row that is retrieved.  This can become painful if the user has a last name that starts with “S” or a latter letter in the alphabet and has to key in 100+ items.


On the Form_Load() event of the form, add the following code:



UserList.DefaultValue = HelperFunctions.GetUserID


This will tell the Combo Box to default to the currently logged in user, and will also give the user the ability to change the value.



Additional Resources:
http://blogs.officezealot.com/charles/archive/2004/12/10/3574.aspx
http://www.vba-programmer.com/

–Mark
 


This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

One Response to Retrieve the Current User & Set the Combo Box: Access 2003

  1. BigDutch says:

    hi. just what i was looking for – i think. I am not a total noob in Access but certainly am more active in the GUI rather than VB. I using Access2000 and whilst everything you said makes sense i`m not sure where to save the code you have typed. I have user table (tblBBGStaff) which has field (tblBBGStaffNetLogin) which matches the Windows Logon name of any user on the network and want to “auto populate” a combo box which currently saves the tblBBGStaffID in a staff field in the “returns” table.

Leave a Reply