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