Easier Way to Evaluate Multiple Conditions

"Multiple Users" Example

9/21/01 2:18:50 PM

One common scenario is to have multiple possibilities and conditions, and different actions to perform based on them all. This can create some very "fat" and bloated code if you're not careful.

One example: I had a form which had a "requistioned by" field which was, basically, supposed to plug in the current user into the field. But of course, Fran McCarty was logged in as fran, and I wanted this field to show the former (the full name), not the latter (technical user name). So I wrote code for each user, substituting the user name for the full name. It looked like the following:

If Not IsNull(Me.requisitioned_by) Then
GoTo 10
End If
If CurrentUser() = "fran" Then
Me.requisitioned_by = "Fran McCarty"
End If
If CurrentUser() = "larry" Then
Me.requisitioned_by = "Larry R Harrison Jr"
End If
If CurrentUser() = "hkreis" Then
Me.requisitioned_by = "Henry Kreis"
End If
If CurrentUser() = "jay" Then
Me.requisitioned_by = "Jay Tome"
End If
If CurrentUser() = "pat" Then
Me.requisitioned_by = "Pat Trillo"
End If

You can see how, for every new user, 3 lines of code were added. It worked, but it was bloated & inefficient. I wanted to improve this. I posted to a newsgroup, and someone suggested creating a new table which contained the user name & full names, and use DLookup to plug in the appropriate full name. I received the following suggested code (naturally, I changed the field and table names to fit what I had):

Private Sub requisitioned_by_Enter()
Dim strQuote As String
strQuote = Chr(39) ' single quote
If IsNull(Me.requisitioned_by) Then
Me.requisitioned_by = DLookup("[full_name]", "users_table", "[user_id] = " & strQuote & CurrentUser() & strQuote)
End If
End Sub

You are to do the following:

Created a new table

Enter in the data (I entered "pat" in the user_id field, and "Pat Trillo" in the full_name field)

Substitute in the proper table/field names to fit the situation

In the above scenario, the table was called users_table, the user id was called user_id, and the full name of the person was called full_name.