Kim Howard
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]![c mbArchitec tFirm] 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]![cmb ArchitectF irm] 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
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]![cmb
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
ASKER
If I replace [Screen].ActiveControl.Par ent!cmbArc hitectFirm with [Forms]![YourFormName].cmb ArchitectF irm 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_AfterUpda te. I would guess that should not make a difference since the only code in the cmbArchitectFirm_AfterUpda te event is cmbArchitect.requery. Correct me if I am wrong.
Kim
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_AfterUpda
Kim
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
Pat,
Of course! I knew that, and thanks for reminding me!
Kim
Of course! I knew that, and thanks for reminding me!
Kim
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
Thanks all!
Kim
Kim,
Don't forget to close out the quesion.
Don't forget to close out the quesion.
ASKER
Thanks for the reminder. I thought I had.
Thanks again for your help!
Thanks again for your help!
[Screen].ActiveControl.Par
with
[Forms]![YourFormName].cmb
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:
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