Ask Your Question
0

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

asked 2016-11-04 19:12:25 +0100

EasyTrieve gravatar image

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".

edit retag flag offensive close merge delete

Comments

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)?

Ratslinger gravatar imageRatslinger ( 2016-11-04 19:37:54 +0100 )edit

@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.

EasyTrieve gravatar imageEasyTrieve ( 2016-11-04 21:54:16 +0100 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2017-01-17 02:02:07 +0100

EasyTrieve gravatar image

updated 2017-03-07 06:50:50 +0100

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.

image description

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.)

image description

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.

edit flag offensive delete link more

Comments

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

Ratslinger gravatar imageRatslinger ( 2017-01-18 02:45:57 +0100 )edit
2

answered 2016-11-05 01:27:53 +0100

Ratslinger gravatar image

updated 2017-01-20 20:35:16 +0100

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.

edit flag offensive delete link more

Comments

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

Ratslinger gravatar imageRatslinger ( 2016-11-05 01:32:41 +0100 )edit

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 gravatar imageEasyTrieve ( 2016-11-05 03:28:55 +0100 )edit

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

EasyTrieve gravatar imageEasyTrieve ( 2016-11-05 03:30:55 +0100 )edit

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

Ratslinger gravatar imageRatslinger ( 2016-11-08 07:13:53 +0100 )edit

@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 gravatar imageEasyTrieve ( 2017-01-17 02:05:45 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-04 19:12:25 +0100

Seen: 610 times

Last updated: Mar 07 '17