Implement a search offset in an Access form

By Susan Harkins

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.

When searching a continuous form, Access highlights the matching record without moving any records, if possible. When Access must change the visible records in order to select a matching record, Access will position the matching record at the top of the form. Some users would rather see the matching record along with a few of the previous records instead. To accommodate these users, you can add a simple offset value to the search, and doing so is easier than you might think.

To illustrate this flexible searching solution, we’ll add a couple of search controls to the simple continuous form (a phone list) shown below. This form displays data from the Customers table in Northwind.accdb, the sample database that comes with Access.



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.