Jump to first matching record based on control value

[RosettaStone]

On a regular form (not continuous) I want to have little buttons (don't care where they are placed) that I can press to jump to the first record where a selected field starts with that button's text -- and then be able to have "next record" and "previous record" form controls work intuitively.

First approximation to the ideal solution would have static text on the buttons... e.g., "A" "B" "E" "K" "L". Pressing the "K" button would cause the form to display the first record with that starts with "K" -- e.g., K0001, and then pressing the "Next" button on the form would cause the form to display record K0002, then K0003, etc.

The function/code/whatever can't assume that the recordset is sorted on the target control. It's OK to force a sort on the target, however -- there's no context to lose.

The next approximation to the ideal solution would have 20 buttons, with text that varies depending on the distribution of data in the underlying table. There would be some sort of "recalculate" button on the form (or elsewhere) to re-determine the button text values. I assume that these values would be stored in a table somewhere to give persistence between invocations of the form.

The underlying problem is the need to scan through a relatively large database that's indexed on email addresses. I want to be able to quickly determine if a particular record is present or not, without having to type in the email address somewhere. In addition, the problem is compounded by the need to access the same table (form) using two other index values -- a serial number and a product code, both of which are 17-digit random values.

In the final solution, I envision 3 rows of buttons, one for email, one for serial number, and one row for product code (total of 60 buttons). Pressing the appropriate button would first sort the recordset, then jump to the appropriate record.

Finally, I should note I'm still using Access 97. Also, I realize that there's probably a more elegant way to do this than using 60 buttons.... I'm open to ideas. Thanks in advance.

 

[RickSpr]

Let me address the 60-button alternative first. How about a combo box with a list of field names the user can sort/search by? When the combo box is changed, you use the new value to set the form's OrderBy property, which immediately causes a resorting of the recordset. (You'll also need to set the OrderByOn property to True.) Then, you can have 26 search buttons "A"-"Z" or whatever, or maybe a text box into which the leading characters of the search value can be typed, with a "Go" or "Search" button to activate the search.

The search logic depends on whether you want to just find a match, or filter by it. If you use find, the user will still be able to use the first/next/previous/last buttons to see other records, that don't match the search criteria. If you use filtering, the other records will not be accessible until the appropriate search criteria is entered. Note that in either case, the recordset has already been sorted on the field to be searched, by virtue of the user's having chosen the field in the combo box as described above.

To do a find, you create a Recordset object variable and set it to the form's RecordsetClone property. Next, you use the recordset variable's FindFirst method, specifying the criteria as:
[combo box control].Value & " >= " & [command button control].Caption
or:
[combo box control].Value & " >= " & [text box control].Value
Assuming a record was found (the recordset variable's NoMatch property is False), you then assign the recordset's Bookmark property value to the form's Bookmark property, causing the form to position to the record.

To do a filtering, set the form's Filter property to a criteria string such as:
[combo box control].Value & " LIKE " & [command button control].Caption & "*"
or:
[combo box control].Value & " LIKE " & [text box control].Value & "*"
You must also set the form's FilterOn property to True. Note that if no matching records are found, and the form doesn't allow record additions, the whole form will go blank, including the sorting/searching controls. The user will then have to remove the filter using the menu or toolbar in order to get the controls back to try again. Rick Sprague