|

Access: Finding Records

By Crystal Long
April 1, 2020
0 comments

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.

 

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.

 

 

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;

 

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.

 

Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. Visit www.MsAccessGurus.com for information.
0 No Comments

You may also like