Avatar of Kim Howard
Kim HowardFlag for United States of America asked on

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 AccessVBA

Avatar of undefined
Last Comment
Kim Howard

8/22/2022 - Mon
Dale Fye

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
ASKER
Kim Howard

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Kim Howard

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PatHartman

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.
ASKER
Kim Howard

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

Kim
ASKER
Kim Howard

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

Kim,

Don't forget to close out the quesion.
ASKER
Kim Howard

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