Using the FindRecord method

Let's take a look at how the quick search combo box on frmProductsExample1 works. Selecting a product from cboQuickSearch fires the AfterUpdate event. Code in the AfterUpdate event procedure performs the search on the form, and the form instantly displays the selected record.

The FindRecord method locates a record in the form's bound recordset. This is equivalent to using the binoculars on the Ribbon to find a record in a datasheet.

When performing a search on a datasheet, you begin by clicking the column you want to search, perhaps LastName. Next, you click the binoculars on the Ribbon to open the Find and Replace dialog box, and enter the name you want to find in the recordset. Access knows to use the LastName field because that's the column you selected in the datasheet. When you enter Smith as the search criteria, Access moves the datasheet record pointer to the first row that contains Smith in the LastName field.

When you use code to search through the contents of a bound Access form, you actually perform these same steps using VBA statements.

Perform the following steps to create an AfterUpdate event procedure behind the combo box:

1. Display frmProductsExample1 in Design view, click cboQuickSearch, and then press F4 to display the Property Sheet.
2. Select the Event tab and select the AfterUpdate event.
3. Click the combo box arrow in the AfterUpdate event property and select Event Procedure.
4. Click the Builder button that appears in the right side of the property. The procedure appears in a separate VBA code window. The event procedure template (Private Sub cboQuickSearch_AfterUpdate()…End Sub) is automatically created in the form's code module. As you've learned, whenever you create an event procedure, the names of the control and event are part of the subprocedure.
5. Enter the four lines of code exactly as shown in Figure 29.4.

The first line checks to make sure that cboQuickSearch contains a value (is not null). If cboQuickSearch is null, the program flows to the End If statement and no search takes place. If cbQuickSearch has a value, the code inside the If block is executed, starting with this statement:

Me.txtProductID.SetFocus

This statement moves the cursor to the txtProductID control. Just as you need to manually move the cursor to a column in a datasheet in order to use the Find icon on the Ribbon, you must place the cursor in the bound control you want to use as the search's target. In this case, you're moving the cursor to the control containing the ProductID value because the search will look for a particular ProductID in the form's bound recordset.

The next statement in the If block is:

DoCmd.FindRecord Me.cboQuickSearch.Value

In this statement, the FindRecord method 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, including 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. A bookmark is another way of finding a record.

frmProductsExample2 contains the code for this example.

Figure 29.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 rsClone As DAO.Recordset
Dim sCriteria As String
Const sSEARCHFLD As String = "[ProductID]"
If Not IsNull(Me.cboQuickSearch.Value) Then
    Set rsClone = Me.RecordsetClone

The first three lines declare a recordset named rsClone, a string named sCriteria, and a constant named sSEARCHFLD that is set to the name of the field to search. These will be used later in the code. Next, the procedure checks whether cboQuickSearch has a value, which means the user selected something 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 target 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.

The Recordset object's FindFirst method requires a search string containing criteria to look up in the recordset. (Yes, that's correct—you're actually asking the RecordsetClone to search itself for a record, based on some criteria.)

The criteria string can be as complicated as needed. The following statement concatenates [ProductID] (our constant), an equal sign, and the value of cboQuickSearch:

sCriteria = sSEARCHFLD & " = " & Me.cboQuickSearch.Value

Assuming the value of cboQuickSearch is 17, sCriteria is now:

[ProductID] = 17

After the criteria string is completed, use the recordset's FindFirst method to search for the record in the RecordsetClone. The following line uses the FindFirst method of the recordset, passing the criteria string as the argument:

rsClone.FindFirst sCriteria

The next lines are used to determine whether the record pointer in the form should be moved. Notice the Bookmark property referenced in the following code block. A bookmark is a stationary pointer to a record in a recordset. The FindFirst method positions the recordset's bookmark on the found record.

If Not rsClone.NoMatch Then
  Me.Bookmark = rsClone.Bookmark
End If

If no record is found, the recordset's NoMatch property is True. Because you want to set the bookmark if a record is found, you need the computer equivalent of a double negative. Essentially, it says if there is “not no record found,” and then the bookmark is valid. Why Microsoft chose NoMatch instead of Match (which would reduce the logic to If rsClone.Match Then…) is a mystery to everyone.

If a matching record is found, the form's bookmark (Me.Bookmark) is set to the found recordset's bookmark (rsClone.Bookmark) and the form repositions itself to the bookmarked record. This doesn't filter the records—it merely positions the form's bookmark on the first record matching the criteria. All other records are still visible in the form.

The last lines of code simply close the recordset and remove it from memory.

Using the FindFirst or Bookmark method is preferable to using FindRecord because it allows for more complex criteria and doesn't require the control being searched to be visible. You don't have to preposition the cursor on a control to use the recordset's FindFirst method.

With code

Figure 29.6 shows the two lines of code necessary to create and apply a filter to a form's recordset. Each form contains a Filter property that specifies how the bound records are filtered. By default, the Filter property is blank and the form shows all the records in the underlying recordset.

The first line of code sets the form's Filter property:

Me.Filter = "ProductID = "& Me.cboQuickSearch.Value

Notice that this is exactly the same string used as the criteria passed to the recordset's FindFirst property.

The second line of code (Me.FilterOn = True) turns on the filter. You can put all the criteria that you want in a filter property, but unless you explicitly set the FilterOn property to True, the filter is never applied to the form's recordset. The filter hides all the records that do not meet the criteria, showing only the records meeting the filter's value.

Me.FilterOn = True

Whenever you turn on a filter, it's useful to provide a way to turn off the filter. There is a small button (cmdClearFilter) next to the combo box on frmProductsExample3. This button turns off the filter and sets the form's Filter property to an empty string (vbNullString). The second procedure shown in Figure 29.6 is the button's Click event procedure:

Private Sub cmdClearFilter_Click()

  Me.Filter = vbNullString
  Me.FilterOn = False
  Me.cboQuickSearch.Value = Null

End Sub

With a query

You might want to have one form control another. Or you might want a recordset to display selected data based on ad hoc criteria entered by the user. For example, each time a report is run, a dialog box is displayed and the user enters a set of dates or selects a product or customer. One way to do this is to use a parameter query.

Creating a parameter query

A parameter query is any query that contains criteria based on a reference to a variable, a function, or a control on a form. Normally, you enter a value such as SMITH, 26 or 6/15/12 in a criteria entry area. You can also enter a prompt such as [Enter the Last Name] or a reference to a control on a form such as Forms!frmProducts![cboQuickFind].

The simplest way to create a parameter query is to create a select query, specify the query's criteria, and run the query to make sure it works. Then change the criteria to the following:

Like [<some prompt>] & "*"

or:

Like "*" & [<some prompt>] & "*"

where <some prompt> is the question you want to ask the user. Figure 29.7 shows a parameter query that prompts the user whenever the query is run to enter the Product Category.

Anytime the query is run, even if it's used as the record source for a form or report or the row source for a list or combo box, the parameter dialog box is displayed—and depending on what's entered, the query criteria filters the query results. Figure 29.8 shows the parameter dialog box open, asking for the product category value required by the query.

You may remember learning that the Like operator allows for wildcard searches. For example, if you want to filter the query records for any product category that starts with “car” (or “CAR”), you enter CAR in the parameter dialog box. Without the parameter, you would have to enter Like "CAR*" in the criteria area of the query. Also, because the wildcard (*) is included as part of the parameter, users don't have to include the wildcard when they respond to the parameter dialog box.

A consequence of adding the asterisk to the parameter is that, if the user doesn't enter a parameter value, the criteria evaluates to "LIKE *", and the query returns all records except the nulls. Leaving the asterisk out of the criteria expression results in no returned records if the user fails to provide a product category.

Figure 29.9 shows the Query Parameters dialog box (opened by right-clicking the query's upper area and selecting Parameters from the shortcut menu). You use the Query Parameters dialog box to specify parameters that require special consideration, such as date/time entries or specially formatted numbers. One text entry has been entered in the Query Parameters dialog box to show how it works. You enter the parameter text and choose the parameter's data type.

Unfortunately, Access parameter queries don't provide a way to supply default values for parameters. Your best bet is to always include the asterisk in your criteria expression so that, if the user closes the parameter dialog box without entering a value, the query will return all records because the criteria expression will resolve to Like "*".

Creating an interactive filter dialog box

The problem with parameter queries is that they're only suitable for simple parameters. The users have to know exactly what to type into the parameter dialog box, and if they enter the parameter incorrectly, they won't see the results they expect. Also, using parameter queries for entering complex criteria is fairly difficult.

A better technique is to create a simple form, place controls on the form, and reference the controls from a query as parameters. In other words, the query uses the form's controls to get its parameter values. This is a huge advantage to the users because the controls can help the user select the criteria by presenting lists or drop-down menus of the acceptable parameter values. Plus, code can be added to each control's AfterUpdate event to validate the user's input to ensure that the query will actually run. The content of controls like combo boxes or list boxes can be dynamic and contain actual values from the underlying tables. This means that the criteria controls might contain only the names of customers who've placed orders, or product categories actually in the database at the moment.

Figure 29.10 shows frmFilterProducts in Design view. cboCategory is filled with the data from qryCategories, which sorts the records in tblCategories in alphabetical order.

cboCategory's DefaultValue property is set to Cars because this is the most commonly used criteria for the Products form. In this case, LimitToList is set to Yes because we want to force users to select only from the categories actually in tblCategories.

Figure 29.11 shows qryProducts_FormParameter. This query selects all fields in tblProducts based on the category retrieved from cboCategory on frmFilterProducts. Notice the criteria expression in the Category column:

= [Forms]![frmFilterProducts]![cboCategory]

As the query runs, it automatically retrieves the criteria value from cboCategory. The combo box returns Cars, unless the user has choosen a different category.

In normal operation, the user selects a product category from frmFilterProducts and clicks OK. Code behind the button opens frmProductsExample4, which is bound to qryProducts_FormParameter. The criteria for the Category field in qryProducts_FormParameter looks up the selected value in cboCategory on frmFilterProducts, and magically frmProductsExample4 opens with just the selected product category loaded.

The only issue facing developers working with tightly integrated database objects like this (in this case, frmFilterProducts, qryProducts_FormParameter, and frmProductsExample4) is that it may not be obvious that the objects work together. Removing or modifying any of these objects might break the workflow or cause problems for the users.

You might choose to use a naming convention that implies the relationship between the two forms and the query, such as giving each item the same name, but with different prefixes. Or, you could use the custom groups in the Navigation pane, and add the objects to a single group. Very often things that are obvious to you—the original designer and developer—may not be as clear to someone else, so it pays to take advantage of simple techniques that help document your applications.

Linking the dialog box to another form

The frmFilterProducts dialog box (refer to Figure 29.10) does more than just create a value that can be referenced from a query. It also contains code to open frmProductsExample4.

Figure 29.12 shows the cmdCancel_Click and cmdOK_Click event procedures behind the Cancel and OK buttons found on frmFilterProducts.

The cmdOK_Click event procedure code opens frmProductsExample4, sets the focus on it, and then re-queries the form to make sure that the latest selection is used on the form. The SetFocus method is necessary to move focus to the form that is opened. The Requery method isn't strictly required, because a form automatically re-queries its record source the first time it's opened. However, if the form is already opened—for example, if you use the dialog box a second time to search for another record—the Requery method ensures that the form displays fresh data.

Although not implemented in frmFilterProducts, the cmdOK_Click event procedure could also contain a DoCmd.Close statement to close the dialog box after it has opened frmProductExample4. Or, you may elect to keep the dialog box open to make it easy for users to select another product category to view.