Filtering form data based on selection from Combo box

[barryg80]

The relationship isn't used in your query that populates the subform though.

If I understand correctly this is the query which is the source for your subquery right?

SELECT Clients.ClientID, Clients.ClientName, Clients.PartnerFROM Clients
WHERE (((Clients.Partner)=[forms]![frmCROReturns]![ComboPartner]));

Since there is no join statement and you aren't bringing in the Partners table in your FROM, the relationship is irrelevant to the query.

Yes this is the select I have

[June7]

Understand now.

Main form filters by the partner to restrict the clients and subform shows client tax info.

Did you use Requery code suggested in post 3?

Combobox must be UNBOUND.

[Xipooo]

Understand now.

Main form filters by the partner to restrict the clients and subform shows client tax info.

Did you use Requery code suggested in post 3?

Combobox must be UNBOUND.

June7, I think you and I were assuming this is a master-detail setup. But it sounds like he's filtering the main form by what he's selected in the combo box. The subform is tax info, NOT partner info.

I believe this is why he's gone down this particular rabbit hole.

Barry, you need to UNBIND the combobox as June7 suggests. Delete the "Control Source" in it's properties. Then on the combobox After Update event, requery your form using me.requery

[June7]

It's still a master-detail setup. The main form and subform linking will be the client ID.

barry, do you want to do data entry/edit of client info on the main form?

[barryg80]

Yep Xipoooo it certainly feels like a rabbit hole I am in with this one. Sorry for the delay replying by the by, trying to keep away from work for Sunday at least with the family!

I dont need to edit the client info on the main form no, that is done on a separate client details form. This form I am stuck on is solely for editing and adding the clients returns in the subform. The filter is just to show only clients linked to a certain partner. I can do this using a parameter in a query but the combo box on the form is a much nicer way in my opinion.

I'll try those steps you have just suggested and see how I go now...appreciate all the help folks by the by

[barryg80]

Ok, some progress. Its working as its meant to, kinda! When the form loads it prompts with a dialog box to enter a partner so when I type a valid one in it filters the records correctly. Progress! If I the want to change to another partner on the form and hit the combo box I get the dialog box prompt again. How do I set it up so that the user can just choose a value in the combo box without having to type one in? Thanks again

[June7]

The main form is bound to the parameterized query?

The combobox is on the main form?

If the query correctly references the combobox, there should not be a popup.

[barryg80]

The main form is bound to the parameterized query?

The combobox is on the main form?

If the query correctly references the combobox, there should not be a popup.

My bad June7. Had a mistake in my query criteria, referencing a different test form I was using to figure this out. Thank you very much. One more small question, when the form loads the combo box contains "#Name?" until a partner value is selected. Can I populate this with something more meaningful for the user such as 'Select partner' ?

[June7]

Don't think that error should show - but I don't use dynamic parameterized queries so I could be wrong. I use VBA to set form Filter and FilterOn properties.

What is the combobox RowSource sql?

[barryg80]

I worked out why this was happening June7. When I was experimenting with the value shown in the combo box I had entered a default value at one point and forgotten to remove it. I just spotted that now. Once removed it clears up the issue and when I load the form the combo is blank. This is fine as I'll just instruct user they have to select a value. Thanks very much for all your help really appreciate it.