Using Multiple Row Sources For a Combo Box
Let's say you have an application that includes a table of client names, addresses and other important information. The client is assigned a "friendly" ID, such as the first four letters of their last name followed by a couple of numbers. In addition, Access assigns the actual unique primary key ID.
When you want to pull up the client's record in a form, you would like to be able to search by the "user friendly" ID, but also be able to find them based on a last name, first name combination. Additionally, it is useful to be able to locate them by the Access-assigned primary key.
A combo box with some VBA in the backend will accomplish this goal. The key to making this function propertly is to dynamically assign the combo box's RowSource property. Using the above example where the user would like to look up a client in three different ways, the developer can create three different queries to be used as the RowSource property.
One technique to provide an interface to select which of the three methods to query is a radio button group. The client chooses which of the three methods by clicking the correct radio button, and then clicks the drop down box.
This combination of radio buttons and a combo box appears as follows:
The combo box drop down has three columns, so that the user can see the three keys for each client, regardless of which method is used to search for the record. Each of the three queries is structured so that the third column is the Internal Access-assigned primary key. This is the key that is used to locate the record, since it is guaranteed to be unique. In other words, the third column is the BOUND column value for the combo box search result value. The property sheet for the combo box is shown below:
The code below shows the code for the radio buttons and the combo box.
Program Code
Private Sub cmbClientSelected_GotFocus()
' *******************************************************
' When The Combo Box receives the focus, set the
' RowSource property based on which radio button
' is active. The values of 1, 2 and 3 are the
' radio button values which depend on which radio
' button was active at the time the combo box received
' the focus.
' *******************************************************
cmbClientSelected = ""
Select Case frmSearchOption
Case 1
cmbClientSelected.RowSource = "qryByCANClientID"
Case 2
cmbClientSelected.RowSource = "qryByName"
Case 3
cmbClientSelected.RowSource = "qryByInternalID"
End Select
End Sub
Private Sub cmbClientSelected_AfterUpdate()
' *******************************************************
' When The User Clicks the Combo Box and then selects
' a client, this sub is activated. The value of the
' combo box object is the third BOUND column of the
' query.
' *******************************************************
Dim rst As DAO.Recordset
Dim lngClientContactID As Long
Dim bkmBookmark As Variant
' *****************************************************************
' Exit If The User Deleted the Content of the Combo Box
' *****************************************************************
If IsNull(Me.cmbClientSelected) Or Me.cmbClientSelected = "" Then
Exit Sub
End If
On Error GoTo Bookmark_Err
' *********************************************************************
' Using The Form's RecordsetClone, Locate The Record Selected
' From the Combo Box
' *********************************************************************
lngClientContactID = CLng(Me.cmbClientSelected)
Set rst = Me.RecordsetClone
rst.FindFirst "CCClientContactID = " & lngClientContactID
If rst.NoMatch Then
MsgBox "Record not found"
rst.Close
Set rst = Nothing
Exit Sub
End If
bkmBookmark = rst.Bookmark
' *********************************************************************
' Position the form record to the Selected Value
' *********************************************************************
Me.Bookmark = bkmBookmark
' *********************************************************************
' Close The Recordset Clone
' *********************************************************************
rst.Close
Set rst = Nothing
Bookmark_Exit:
Exit Sub
Bookmark_Err:
MsgBox Error$
Resume Bookmark_Exit
End Sub