Add a search control to an Access form

Populating an Access form is a simple task thanks to bound forms and controls. In fact, a wizard will walk you through the whole setup. The results, however, aren’t always easily searchable. There are a number of ways to add a search feature, but a text box or a combo box in the header is one of the simplest to create and implement.
The first step isn’t revamping the form though. The first step is determining which value you’ll use as search criteria. A primary key value might be your first choice. Often, the primary key value isn’t practical. For example, users might want to search by a last name value, which may or may not be part of the data’s primary key.
Once you know how users will want to search the data, you can create and implement a search solution. For example, the following instructions add a search control to the Orders form (in Northwind, the demo database that comes with Access):

1. With the Orders form in Design view, open the header and insert an unbound text box control. You don’t have to put the search control in the header, but doing so is a good way to offset the search feature from the rest of the form.
2. Name the text box txtSearch.
3. Open the form’s module by clicking the Code button on the Form Design toolbar.
4. Enter the following code for the txtSearch control’s After Update event:
   

Private Sub txtSearch_AfterUpdate()

'Find record based on contents of txtSearch.

  Dim strSearch As String

  On Error GoTo errHandler

  'Delimited for text search.

  'strSearch = "OrderID = " & Chr(39) & Me!txtSearch.Value & Chr(39)

  'Delimited for numeric values.

  strSearch = "OrderID = " & Me!txtSearch.Value

  'Find the record.

  Me.RecordsetClone.FindFirst strSearch

  Me.Bookmark = Me.RecordsetClone.Bookmark

  Exit Sub

errHandler:

  MsgBox "Error No: " & Err.Number & "; Description: " & _

   Err.Description

End Sub

With the form in Form view, enter an order number in the new search control and press Enter. Executing the control’s After Update event searches the OrderID field for a match to the current value in txtSearch and updates the form and the subform, accordingly.

If there’s no match, the form displays the first record in the recordset. Entering an invalid data type will generate an error. The error handling routine is rudimentary, so you’ll want to test it thoroughly and include appropriate actions as necessary.
When applying this technique to your own forms, you should update two areas:

• Choose the appropriately delimited search statement and comment out the other. The code includes a sample statement, which is commented out, for searching a text field. This example searches a numeric field (OrderID in the Orders table).
• Substitute the explicit reference to OrderID with the appropriate field name. (See the highlighted statement above.)

There are many ways to implement a search feature, but this is one of the simplest. You’ll find it works well as long as you’re searching a single field. If the list of search values is relatively short, you can use a combo box instead of a text box and display the search values in the control’s dropdown list for easy selection.