Help with dropdown boxes in LO Base forms

[stratacast1]

My forms are being used to select business names easily without having to type the names each time, which will save time and prevent inconsistencies in the database. Currently, I can get a dropdown box to work with a combo box, however it will only change the company name. What I hope to accomplish is to select a company name in the dropdown box, which will then select that row of information (eg the company’s ID, phone #, location, etc) that I have for the company. So basically auto-populating the rest of the info in the form. This form also has a subform attached to it and I would hope it would sort out the proper info in the subform as well since the tables are connected via relations.

I’m using LO Base for the first time and it has been a steep learning curve, but this is the last piece of the puzzle that I am currently stuck on. Thank you in advance for your help

[Ratslinger]

You can use a filter to obtain the information. Attached is a sample. Select a company in the drop down list box then click the select button. This brings up the selected company information which then displays the employee table information related to the company. This is all accomplished using a filter table (temporary storage - just one record) for the selection.

On the main form is the listbox. The list is generated from the Company table Names (see the Data tab of the properties box in MainForm) and the selection is stored in the first record of the Filter table. The remainder of the controls reside on the subform named SubForm. The subform points to the Company table and is linked to the Filter table (see the Data tab of the properties box in SubForm). When you press the button, the subform is Refreshed (set in button property General tab), and using the Company in the Filter table, reads the record in the Company table and displays it. The Employee table control in a subform to the subform (thus subsubform) gets all associated records for the selected company.

Sample: SelectCompanyByFilter.odb

[EasyTrieve]

@Ratslinger, In the example given when I open the Form Navigator I can see that there is a MainForm, a SubForm and even a SubSubForm. The Select button is in the SubForm, not the MainForm, so the Action: Refresh Form occurs in SubForm and SubSubForm, not in MainForm. MainForm only holds the Company drop down box. Any ideas on how to have a macro triggered by a new Company do the Refresh Form action?

[EasyTrieve]

In the example given when I open the Form Navigator I can see that there is a MainForm, a SubForm and even a SubSubForm. The Select button is in the SubForm, not the MainForm, so the Action: Refresh Form occurs in SubForm and SubSubForm, not in MainForm. MainForm only holds the Company drop down box. Any ideas on how to have a macro triggered by a new Company do the Refresh Form action?

[Ratslinger]

Do you mean a company not already listed in the listbox or just a new selection? Given that this question is about macros, please ask as a “New” question so others searching may find. There are different ways of doing this depending upon your requirements.

[EasyTrieve]

@Ratslinger, & @peterwt, This solution works for a single selector, but does not work for multiple selectors, for example if you want pull down customer name record locator’s called: “by Last, First”, and “by First Last”. I had hoped this would work. But when I tried it, the pull downs at first appear to work, but once you select an item, the selection vanishes in favor of the current record. An additional problem is the record motion buttons operate on the Filter table not the sub-form.

[EasyTrieve]

@Ratslinger, Thanks. I will write a new question.

[Ratslinger]

As far as your dropdown selections, it’s all on the setup. Each may be different depending upon what the end results are to be. You can have many dropdown boxes for a filter. For Form Navagation, a bar should be places on the Form or SubForm you want to navigate, and not to rely on the standard toolbar navigation.

[peterwt]

The solution and sample provided by @Ratslinger is a good way to satisfy your requirement. The use of a Table to store the List Box selection is easier than using a Basic macro.
There is however a potential problem. If by accident or design the Company Name is repeated in the Table it will not work correctly. There is no constraint on duplicates in the NAME field in the COMPANIES Table. You may need to have duplicates if the same company has facilities in more than one Location.

I have attached a modified version here. SelectCompanyByFilterMod.odb. In this version I store the COMPANY ID in the FILTER Table. As this is the Primary Key it will have no duplicates. I have modified the List Box to have 2 fields, the linked field which is stored in the FILTER Table and the display field which shows in the List Box. The display field if just using the Company NAME would not allow you to determine which of the duplicates to select. In the display field you can show the details from more than one field by concatenating a number of fields. In my example I concatenate the Company NAME and LOCATION using:-

SELECT “NAME” || ’ - ’ || “LOCATION”, “COMPANYID” FROM “COMPANIES2”

Which shows in the List Box display the company NAME - LOCATION.
There are some other modifications. All the modified versions have 2 appended to the name.
If you do not expect to have duplicate Company Names you can use the original method but you need to put a constraint on the NAME field in the COMPANIES Table to prevent duplicates.

I have used the concatenating method in the past when selecting a person from a Table containing details of persons. There would be duplicates of common surnames so I concatenated FirstName, Surname, and Location to provide an unique reference.

[EasyTrieve]

Concatenation with || does not work in MySQL so to make this work more generally use CONCAT(“NAME”, ’ - ‘, “LOCATION”). But if you have any nulls in NAME or LOCATION this will null out the entire concatinated value, so you will need something like CONCAT(if(isnull(NAME,’’,NAME), ’ - ‘, if(isnull(LOCATION,’’,LOCATION). Perhaps someone else can simplify this. In MS Access they have this neat little nz() function which nicely removes the nulls.