filter form listbox query by form primary key

[josephervin]

Hello,

I have two tables connected by primary key via a relationship. Top level table is Employee which has coumns for name, address, and multiple phone numbers, called Phone1, Phone2, Phone3. The other table contains details about a specific employee Activity group, and which references the Employee table primary key. In the input form for the Activities table, I’m trying to create a combo box populated from the multiple phone numbers given for this employee in the Employee table, i.e. so I can pick a preferred phone number in the Activities table chosen from the data for that employee record in the Employee table.

The trick seems to be that I want to create a query for the combo-box that will pull Phone1, Phone2, Phone3, only for the one employee, not the entire Employee Table. So I figure I want to add a WHERE on the combo-box query to look only at those Fields for this employee, but I don’t know how to do that. Does the Form itself have a primary key that I can reference in the combo-box query?

Thanks for any help!

[doug]

I can get you closer, but the end result is elusive. Ordinarily, you would want a UNION query for this, combining the outputs of SELECT "phone1" FROM "empl" with phone2, etc. This will be executed in a query by enabling Edit → Run SQL Command Directly. This then can be the list source. However, not possible to filter the combo box list to limit it to data for a particular employee row. I am not sure it is possible to re-apply such a filter to a list source.

[doug]

Thus, the alternative option is to create a subform with the phone numbers displayed in whatever way you want-- there are many options-- linked to the main form by the employee identifier. Doing this will allow you to select a filtered list of phone numbers, but updates of data would have to be done in LO Basic by grabbing the phone number you select and updating the relevant value in the main form.

[josephervin]

Thanks Doug. I played around with Subforms, but the killer is that the query that builds the list has no knowledge fo the form state. So for example, I’ve selected the employ in an earier listbox on the same form. But the later listbox where I’m trying to select the phone number has no knowlege of which form I’ve selected. There’s a cool trick in the Base user’s manual where they create a dummy 1-row table and push the employee selection into there.

[josephervin]

(continuing) then the subform can use the info passed through the dummy table as a “where” clause. Problem is, I need the first listbox to do two things; save the employee primary key in the dummy table and in the Activity table, i.e. the one that is the primary focus of the input form. I think I’d need to get into Macro programming to do that, and likely won’t go there.