Takeaway: Adding search capability to an Access form is pretty easy, but users might appreciate a bit more flexibility by letting them also set an offset value.
To add the search controls, open the form in Design view and expose the header section. Then, insert two text controls; the positioning isn’t particularly important. Name the search string control, txtSearch; name the offset control txtSearchOffset. Then, set txtSearchOffset’s Default Value property to -3. Check the form’s Has Module property - it must be set to Yes.
Now you’re ready to add the code that runs the search. Click the View Code option in the Tools group (in Access 2003, the button’s on the toolbar) to launch the form’s module in the Visual Basic Editor and add the following code:
Private Sub txtSearch_AfterUpdate()
'Find record based on contents of txtSearch
'using an offset value.
Dim strSearch As String
Dim intOffset As Integer
Dim varBookmark As Variant
On Error GoTo errHandler
'Delimited for text values.
strSearch = "Company = " & Chr(39) & Me!txtSearch.Value & Chr(39)
'Set offset value.
intOffset = Me.txtSearchOffset
'Find the record.
Me.RecordsetClone.FindFirst strSearch
Me.Bookmark = Me.RecordsetClone.Bookmark
On Error Resume Next
Me.Recordset.Move intOffset
Me.Bookmark = Me.RecordsetClone.Bookmark
Exit Sub
errHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
End Sub
Return to Access and view the form in Form View. The offset control defaults to -3, which your users can change to suit themselves, but let’s work with the default value for now. Access won’t change the current record set if you search for a company that’s visible in the form. It’ll just change the selected record.
Now, search for a company that’s not visible on screen, such as Company AA. After defining the search string and offset variables, the FindFirst method finds the first matching record. The next statement sets the Bookmark property to the current record. Then, the Move method changes the selection using the offset variable (-3, which means three records before the currently selected record). Finally, the last line uses the previously defined bookmark setting to reposition the selection.
If a user searches for the first record in the form’s recordset, Access selects the first record. If there are more previous records showing than necessary to satisfy the offset value, the offset value has no effect. If a user changes the offset to 0 or a positive value, Access will select the first matching record, with no offset. Access ignores an offset value that’s greater than the number of records.
If a user enters a text value, Access will display an error message. Right now, the error handling routine is basic. Test the procedure thoroughly and enhance this routine to reflect your application’s use.