Access: Finding Records

COMBO BOXES

One way to help users quickly find a record is to include combo boxes for lookups. Figure 1 shows three such combo boxes and a sample of their content. Depending on the information the user has about a contact, he or she can use the combo box to find the correct record.

hese boxes are located on a main form that contains contact information as well as two subforms showing addresses and phone numbers. The first column in each combo box is the unique contact ID, an AutoNumber field called “CID.” As you can see in Figure 1, that column is hidden. (This is done by setting its column width to 0 in the ColumnWidths property.) While the user doesn’t need to see this field, it’s important to include because it will be used to find the appropriate record.

FIND BY NAME

The combo box to find a contact by name gets information from the c_Contact table, which is aliased as “C.” Its primary key field is CID. The “Contact” field is calculated in the table using an expression that combines the individual parts of the name, such as last name and first name. Here is the SQL for the combo box to find a contact by name:

The ColumnCount property is 2, and the ColumnWidths property is 0";3".

The “c_” table name preface refers to a table from a free, downloadable contact management system. There are two versions available: MyContacts (bit.ly/2m0MeBN) is a simple, easy-to-understand version, while the Contact Template (bit.ly/3aTnXSo) is more complex with more features.

FIND BY ADDRESS

The second combo box shows cities and street addresses from the c_Address table (aliased as “A”) and joins the contact table to show names. The second column, also hidden, is the unique address ID—called “AdrID”—which could be used to also make that address record active once a contact record is found. The ColumnCount property is 4, and the ColumnWidths property is 0";0";2.5";2.5". Here’s the SQL for the combo box to find a contact by address:

SELECT C.CID, A.AdrID, [city] & ", " & [addr1] AS CityAddress, C.Contact FROM c_Contact AS C INNER JOIN c_Address AS A ON C.CID = A.CID WHERE ( A.Addr1 Is Not Null) OR ( A.City Is Not Null) ORDER BY A.City, A.Addr1, C.Contact;

FIND BY PHONE

The third combo box shows phone numbers from the c_Phone table (aliased as “P”) and joins the contact table to show names. The second column, also hidden, is the unique phone number ID, called PhoneID, which could be used to also make that phone record active once a contact record is found. The ColumnCount property is 4 and the ColumnWidths property is 0";0";1.2";2.5". Here is the SQL for the combo box to find a contact by phone number:

VBA CODE

While these three lists appear to be different, the code to find a record is the same for each because they’re all storing the CID field. Figure 2 shows the VBA that all three of the combo boxes run on their AfterUpdate event, which happens after the user chooses something from the list.

Private Sub FindMyContact()

'181012 strive4peace, 200308

'Set up error handler

On Error GoTo Proc_Err

'dimension variable For Where clause

Dim sWhere As String

'construct Where clause for Find

With Me.ActiveControl

If IsNull(.Value) Then Exit Sub

sWhere = "CID=" & .Value

.Value = Null 'reset control

End With

'save record If changes were made

With Me

If .Dirty Then .Dirty = False

End With

'find contact

With Me.RecordsetClone

.FindFirst sWhere

If Not .NoMatch Then

Me.Bookmark = .Bookmark

End If

End With

Proc_Exit:

On Error Resume Next

Exit Sub

Proc_Err:

MsgBox Err.Description _

, , ERROR  & Err.Number _

&    FindMyContact

Resume Proc_Exit

Resume

End Sub

The procedure declaration declares the name of the function, FindMyContact, and then an error handler is set up. Although this example doesn’t do anything in the form’s BeforeUpdate event that might prevent a record from being saved, an error would occur if it couldn’t be saved or if the form is filtered and the record the user wants to find isn’t in the form’s record set.

A string variable, sWhere, is dimensioned for the Where clause. With the active control, whichever combo box it is, the procedure exits if there is no value. If there is a value, a Where clause is constructed to find CID. Then the combo is cleared.

Before going to another record, any changes made to the current record are saved. The code then searches through a copy of the records for a CID equal to what the user picked. If a match is found, then the bookmark for the form is set to the bookmark for the clone, which shows the chosen contact record.

The code then exits. If there was an error, and execution went to the error handler instead of exiting normally, the user will get an error message before exiting.

Download this month’s database: SF2004_FindRecords.

SF SAYS

Combo boxes for finding records can display information from the table you’re searching as well as from any related table.