Help your users by adding an easy-to-implement search control to your Access forms.
- 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.
- Name the text box txtSearch.
- Open the form’s module by clicking the Code button on the Form Design toolbar.
- Enter the following code for the txtSearch control’s After Update event:
'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.Bookmark = Me.RecordsetClone.Bookmark
MsgBox "Error No: " & Err.Number & "; Description: " & _
End SubWith 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.)