Base Macro - List Box as Quick Form Navigation

[PhLo]

This should be easy, but I can’t figure it out.

I want to use a drop-down list box as a quick way to navigate through a form instead of having to click previous and next buttons a bunch of times. The list box contains all the same members that the form contains. The challenge for me is that the members in the list box are sorted alphabetically by name, whereas the form’s records are sorted ascending by primary id integers so that the first entered record is first and the last entered is last. This is intentional. I don’t want the order to match. The list box is alphabetical to make it quicker to locate a name in the list. If the list box members and form records were in the same order I could probably do something like oForm.absolute(2) to get the 2nd record to match the 2nd list box member. In my case, #2 in the list box could be record #38 in the form.

FYI, the list box doesn’t have a true “bound field” that connects to underlying data. In other words the “Data Field” in the Data tab of the control dialog is intentionally blank. This is because the list box is NOT being used to update the records, only to navigate to a record in the form. Technically the “bound field” is set to 1, which means the second column in my SQL command to populate the list box is the primary id (but again, it’s not “connected” to the database as an input, just a navigation aid). I can successfully get the primary id of the list box record in my macro with: msgbox oListBox.getCurrentValue() on the Execute Action event of the list box. But I don’t know how to use that id to navigate to the matching record in the form, especially since they are sorted differently.

Sorry the screenshot shows an unfilled form. I didn’t feel like photoshopping fake info into it.

The list box at the top labeled Quick Select contains all the Names that show up in the Name text box below. When I select a Name from the list box, I want that record to become the active one. This is not to be done via any subform trickery. It needs to be accomplished by a macro, by a method similar to oForm.absolute(rowNumber) where the primary id matches the one provided from the list box selection (via a macro attached to the list box Execute Action event).

What is my “missing link” of code to get the primary key from the list box to navigate to the matching record?

[charlie.it]

Hello, maybe if you attach an example of your file we understand the problem better and we can help you.

[PhLo]

Sorry, not possible. Nothing would work for anyone else in my file because it connects to a MySQL database on the backend, plus I don’t want to provide private information anyway. I feel as though the question is pretty well described though. Basically, I want a list box to serve as a form navigation tool so that all the records normally accessed by next and previous buttons can be “jumped to” directly via the list box.

[PhLo]

Imagine there are 10 records available in this Contacts form: 1 - John, 2 - Frank, 3 - Melissa, 4 - Kyle, 5 - Thomas, 6 - Margaret, 7 - Brian, 8 - Garret, 9 - Tanya, 10 - Fred. The numbers represent the primary keys of the records that would appear in the “Contact #” box. When the form initially loads, it would show the information for John because the form source is sorted by primary key, and he is #1. The list box would contain the same 10 names, but they would be sorted alphabetically as follows: Brian, Frank, Fred, Garret, John, Kyle, Margaret, Melissa, Tanya, Thomas. I want to be able to choose “Brian” from the list box (#1 alphabetically), with the result that the form jumps to record #7 based on the primary key connection pulled from the list box.