asked on 3/17/2018

Using [Screen].[ActiveControl].[Parent] to filter a combobox

I am working in Access VBA. I have two fields on a form. One is for Architect Firm and the other for Architect. The architect combobox needs to be filtered on Architect.FirmID so that only Architects from the selected firm are in the list. I am using [Screen].[ActiveControl].[Parent]![cmbArchitectFirm] as a criteria in the RowSource of cmbArchitect. It works great to filter it, but only after a record has been selected in the Architect Firm combobox, because the AfterUpdate event on cmbArchitectFirm has Me!cmbArchitect.Requery. Before I select the architect firm, the architect field is blank even if the firm and architect fields have values. I have tried putting Me!cmbArchitect.Requery in different opening events on the subform with no luck.
There are two forms where I am seeing this behavior. Both are datasheet subforms. I use the same controls in two form views where the form is primary. There I use [Screen].[ActiveForm]![cmbArchitectFirm] and it works great.
This is something I have been fighting off and on for several days, trying to figure out the best way to create this filter. If I have to use the actual form names to make this happen I will have to make several copies of my subforms so that every form will have it's own dedicated subform, which I would prefer not to do.
Any help would be greatly appreciated!!!
Kim

Microsoft Access VBA

Last Comment

Kim Howard

3/19/2018

Dale Fye

3/17/2018

First, start by replacing:

[Screen].ActiveControl.Parent!cmbArchitectFirm

with

[Forms]![YourFormName].cmbArchitectFirm

in your query. I would also make sure that you declare that as a parameter and define the type of data field that will be returned by the combo.

If these combos are bound controls, then I would also use the Current event to requry the architect combo box, something like:

Private Sub form_Current

    Call cmbArchitectFirm_AfterUpdate

End Sub

Open in new window

This will cause the Architect combo box to be requeried so that only the architects associated with the selected firm are visible in the dropdown.

HTH
Dale

Kim Howard

3/17/2018

ASKER

If I replace [Screen].ActiveControl.Parent!cmbArchitectFirm with [Forms]![YourFormName].cmbArchitectFirm then my subform can only be used as a sub on one master form. I would really like to be able to reuse it.
I tried cmbArchitect.requery on form.current and it did not make any difference. I did it as a straight requery rather than Call cmbArchitectFirm_AfterUpdate. I would guess that should not make a difference since the only code in the cmbArchitectFirm_AfterUpdate event is cmbArchitect.requery. Correct me if I am wrong.

Kim

ASKER CERTIFIED SOLUTION

Dale Fye

3/17/2018

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Kim Howard

3/18/2018

ASKER

Dale,
That is a very creative solution! Thanks for the database. It made it so much clearer after reading your description. My initial thought was the disadvantage with a continuous form is that the fields are not inherently sortable. I right clicked in one of the fields in your database and, lo and behold, you can sort on the values. Actually, I could sort on the firm field, but not the architect field. Well, it said I was sorting on it, but i have no idea what it thought it was sorting on because the results were nonsensical. It doesn't really matter, though, because the architect name is not really one of the fields users will want to sort on.
Thanks so much for this idea, this just might work. I will play with it tomorrow in the context of my database.
Kim

PatHartman

3/18/2018

Well, it said I was sorting on it, but i have no idea what it thought it was sorting on because the results were nonsensical.

I didn't look at the example because it looks like you have your answer so I can't reference it but this happens with combos, listboxes, and LookupFields. The issue is that YOU are looking at a text string but Access is sorting on the bound field which is normally numeric.

Kim Howard

3/19/2018

ASKER

Pat,
Of course! I knew that, and thanks for reminding me!

Kim

Kim Howard

3/19/2018

ASKER

So, I ended up just hiding the architect field for now on the lists. I am on a deadline and it is not critical to the application for those fields to be on the list view. They work on the form view and that is all I need for now. I love Dale's solution and look forward to implementing it when, and if, it rises up the priority list.
Thanks all!
Kim

Dale Fye

3/19/2018

Kim,

Don't forget to close out the quesion.

Kim Howard

3/19/2018

ASKER

Thanks for the reminder. I thought I had.
Thanks again for your help!