We help IT Professionals succeed at work.

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

Kim Howard
Kim Howard asked
on
287 Views
Last Modified: 2018-03-19
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
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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

Author

Commented:
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
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

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

Kim

Author

Commented:
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 FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Kim,

Don't forget to close out the quesion.

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.