Base: Adding multiple pull down record selectors to a table editor form

[EasyTrieve]

How can I install drop down, record selectors (plural) at the top of a form?

---

What I have tried so far: I found this answer which shows how to do this with one selector, but the method doesn’t appear to lend itself to multiple selectors. Also the form’s record back and forward buttons don’t work as expected.

Example: Say you have a table of customers, where each customer has a first name, a last name, and a phone#. I want a form to lookup a customer either by first name, or by last name. So at the top of this form I want two pull down (or drop down) boxes to quickly select a customer. The first pull down is organized (sorted) by “first-name last-name”, and the second one is organized by “last-name, first-name”. We can call these boxes FindByFirstLast and FindByLastFirst.

When you click on FindByFirstLast, a drop down list of all customers appears sorted by first name, then by last name. When I select a customer, the form below updates to be able to edit the fields for this customer (like their Phone number and address) .

---

I have used something like this in MS Access for years, and before that in dbase and other databases. In MS Access the “changed” event fires from the drop down list (they call them “Combo Boxes”) and runs a visual basic sub to set the form’s record via a “bookmark”.

[Ratslinger]

Is the only purpose of two list boxes to visually present the same selection differently? Are you, as the footnote implies, looking for this to be done through a macro(s)?

[EasyTrieve]

@Ratslinger, Not sure what you are asking, but no, I don’t think having multiple list boxes is to visually present the same selection differently. For example, it is also nice to have another drop down to find who owns a phone number, or to see which records were edited last, or to see which records are newest, or to see who lives on which street, etc. On one Access table I keep I have about 12 different pull downs to FIND records in the table using a variety of cuts and sort of the data.

[Ratslinger]

This is a difficult question to answer when reading between the lines. First, Base is not Access. There are a lot of things in Access which can be done with a click or two. This is not the case with Base. Most things can be done but require specific coding in macros, hiding controls, filter tables and more.

This sample is in direct answer to the original question and uses a macro. The two list boxes are almost identical with first/last names reversed in the List content property (Data tab of the controls’ properties). The same macro is used for both listboxes. The calling of the macro is done by the controls’ Item status changed Event (on Event tab of contols’ properties). Since first and last name can be duplicated, the CUSTNUMBER was included to make it unique. When the macro is executed, oEvent contains the SelectedValue for whichever listbox was selected. The CUSTNUMBER is extracted and used to filter the table in the subform. This all probably could have been done using a single listbox and just changing the List content with a radio button selection (thus the comment).

Sample:SelectCustomerByDifferentListBoxes.odb

Other reasons brought up in your comment for multiple listboxes must be taken on a case-by-case situation. LO does not have successive filtering ( see this post).

Most of the situations will need to deal with object properties. OOME is the best reference. The are also object inspection tools - MRI and Xray tool.

And if you really need to stick with some of the Access coding there is Access2Base already included in LO.

Edit 1/19/17 -
The following sample provides for different drop down selectors and walking through records with multiple matches on the selection criteria using push buttons for next & previous records. I’ve also included a third list box with made up (but working) criteria which only demonstrates different ways of accomplishing tasks. Although not included, I can certainly see the possibility of multi-level filtering if one was so inclined.

The sample does NOT use any “looping” in the code to find records. FindFirst ( in Access and Access2Base) is little more than Select X Where Y = Z.

CustomerSelectionByMacro.odb

Edit 1/20/17

Note: Filtering need not be used as in the sample. The data from the result set can be directly moved into the individual fields. Using a filter just simplifies the process.

[Ratslinger]

One other comment regarding the Record Navigator. Since this is only selecting a single record it is meaningless.

[EasyTrieve]

Very good!! You get a gold star for this one. (I’m hunkered down reading about LO BASIC. Next is to get record motion buttons to work properly, and to update the pull downs as record motion occurs, but I’ll work on this myself, that is not as important. You got me over the big hurdle. Thanks.)

[EasyTrieve]

I HOPE THIS TOPIC DOES NOT GET CLOSED AS THERE ARE STILL IMPROVEMENTS COMING…

[Ratslinger]

Please see link at bottom of my answer of this post. The mentioned samples have more multi-dropdown examples you may be interested in.

[EasyTrieve]

@Ratslinger, The example in the other new answer I just posted here (“Finally got things to work…”) is what I have been trying to get to work for so long, where you can have multiple, simple record selectors to jump to a record. I sure appreciate all of your help.

[EasyTrieve]

Here’s the updated form design I promised.

• It now allows simple or complex (e.g. Last, First name) sorted lookups.
• It now works with both HSLQDB 1.8 and MySQL (currently it’s configured for HSQLDB 1.8).
• It’s Basic code is much easer to setup and install than before.
• It fixes an issue with the combo box selectors to make for a better user experience.

I’ll keep the original, simpler version below for reference.

---

Finally got things to work! Here’s a working example of what I have been trying to get to work for weeks of my spare time:

Lookup2.odb (Edit: fixed tabbing. Thanks Ratslinger.)

And this leaves filtering available for other uses.

As noted in the code the solution uses a loop rather than a fast, indexed lookup function. Perhaps someday LO can add a findFirst type of function to support this using indexes.

[Ratslinger]

In your sample, tabbing through all records leaves a blank which if a selection is then made, a Function sequence error is generated.