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