Chapter 25: Advanced Data Access with VBA
885
The next block of code is
If Not IsNull(cboQuickSearch.Value) Then
DoCmd.FindRecord cboQuickSearch.Value
End IfThis block of code first checks to make sure that cboQuickSearch contains a value (is not null)
before using the FindRecord method. If a value is found in the combo box, FindRecord uses
the combo box’s value (which is the selected item’s Product ID) to search for the selected product’s
record. Access matches the value in cboQuickSearch with the ProductID in the recordset
bound to the form.
The first value found by the FindRecord method is determined by a series of parameters, includ-
ing whether the case is matched and whether the search is forward, backward, or the first record
found. Enter DoCmd.FindRecord in the code window and press the spacebar, to see all available
options. The FindRecord method finds only one record at a time, while allowing all other
records to be viewed.
Using a bookmark
The FindRecord method is a good way to search when the control you want to use to find a
record is displayed on the form. It’s also a good way if the value being searched for is a single
value. However, many times multiple values are used as lookup criteria. A bookmark is another way
of finding a record.
frmProductsExample2 contains the code for this example.
Figure 25.5 shows the combo box’s AfterUpdate event procedure. This code uses a bookmark
to locate the record in the form’s recordset matching the search criteria.
The first several lines are
Dim rs As DAO.Recordset
Dim strCriteria As String
If IsNull(cboQuickSearch) Then
Exit Sub
End If
Set rs = Me.RecordsetCloneThe first two lines declare a recordset named rs and a string named strCriteria. These will be
used later in the code. Next, the procedure ends if cboQuickSearch is null, which means the
user didn’t actually select anything in the combo box. The following line sets the recordset to a
copy of the form’s bound recordset (the RecordsetClone).
A RecordsetClone is exactly what its name implies: an in-memory clone of the form’s recordset
that you can use when searching for records. If you used the form’s bound recordset instead, your
search will move the current record away from the record displayed in the form. If the search tar-
get is not found in the form’s bound recordset, the form ends up positioned at the last record in
the bound recordset, which is sure to confuse users.