Access: Finding RecordsBy
One of the reasons to use a database is to find information quickly. In many databases, for example, users need to track and find contacts and their related information. You can find contacts using the name in the contact table or by searching in another table for the information linked to that contact, such as a phone number or address.
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.
These 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:
SELECT C.CID, C.Contact
FROM c_Contact AS C
ORDER BY C.Contact;
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:
SELECT C.CID, P.PhoneID, P.Phone, C.Contact
FROM c_Contact AS C INNER JOIN c_Phone AS P ON C.CID = P.CID
ORDER BY P.Phone, C.Contact;
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
If IsNull(.Value) Then Exit Sub
sWhere = “CID=” & .Value
.Value = Null ‘reset control
‘save record If changes were made
If .Dirty Then .Dirty = False
If Not .NoMatch Then
Me.Bookmark = .Bookmark
On Error Resume Next
MsgBox Err.Description _
, , ERROR & Err.Number _
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.
Combo boxes for finding records can display information from the table you’re searching as well as from any related table.