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
Kim HowardAsked:
Who is Participating?
 
Dale FyeConnect With a Mentor Commented:
I missed the piece about a datasheet subform.

datasheets and continuous subforms have a problem.  The rowsource of combo boxes in those subfoms apply to all copies of that control.  So when you requery the architect combo (or change the rowsource).  Only those records where the value that is in the bound field will display the text associated with the selected value.

You can overcome this issue with a continuous form, by setting the recordsource of the form to include a value for the Architect Name (In the sample database I use FullName).
Instead of :
SELECT * FROM tbl_myTable

Open in new window

you would use something like:
SELECT tbl_myTable.*, tbl_Architects.FullName
FROM tbl_myTable LEFT JOIN tbl_Architects ON tbl_myTable.ArchitectID = tbl_Architects.ArchitectID

Open in new window

and then display that value in a textbox displayed overtop (positioned to the front) of the combo, where only the combo boxes dropdown arrow is displayed to the right of the textbox.  You would set the TabStop property of the textbox to "No", and I add text to the GotFocus event of the textbox to force the focus to the Architect combo, just in case the user clicks into the textbox.  In the attached database, myContinuous form, I've dropped the FullName textbox down below the ArchitectID combo box, but if you drag that item above the combo, you will see how this works.

Unfortunately, you cannot use this technique with datasheets, because they just don't allow you to overlay controls over one another.

HTH
Dale
20180317-Continuous_and_Datasheet-.accdb
0
 
Dale FyeCommented:
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
0
 
Kim HowardAuthor 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
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kim HowardAuthor 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
0
 
PatHartmanCommented:
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.
0
 
Kim HowardAuthor Commented:
Pat,
Of course! I knew that, and thanks for reminding me!

Kim
0
 
Kim HowardAuthor 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
0
 
Dale FyeCommented:
Kim,

Don't forget to close out the quesion.
0
 
Kim HowardAuthor Commented:
Thanks for the reminder. I thought I had.
Thanks again for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.