Filtering form data based on selection from Combo box

[barryg80]

Hi all,

Sorry if this is Access 101 stuff but I am an access amateur and trying to create a DB for use in our office as a once off.

I have a main Clients with a subform of ClientReturns (Tax). Each client is assigned one client partner in the company of which there are 6. Joe Bloggs is one of these managers and I want him to be able to filter the form so he only sees his clients. These client partners are stored in their own little table.

So I have been reading how to accomplish this but cannot get it to work.I have a query called qrySelectCROPartner and on the form I have the combo box on the form setup to get its values from the partner table mentioned above. The criteria in the query then points to the combo box on the form like so: [forms]![frmCROReturns]![Combo16]

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

Doesn't work when I run the form anyway, changing the partner select in the combo box achieves nothing.



Your direction is much appreciated!
Thanks

[Xipooo]

You have to requery your subform on the combo box update event.

[June7]

In VBA, like:

Sub comboboxname_AfterUpdate()
Me.Requery
End Sub

However, the Requery syntax depends on where the combobox is in relation to the parameterized query. Are they both part of the subform? If yes, then use what I show.

[barryg80]

Tried that still no joy. The combo box isn't part of the subform, its on the main form.

The main form,should it be based on the client table or the qry selecting from it?

[June7]

The query must be used as the main form RecordSource.

Also, is Partner a number field for a partner unique ID? Is the combobox bound to column Partner value?

[barryg80]

Ok, figured as much re the query.

Partner is text, a single field table with the partner as the PK. So JBloggs/ESmith/etc

Combo box is bound to the Partner value yes. Screenshot attached if that helps?

[Xipooo]

June7 was close. I don't think he realized your query was on a subform.

In your main form, behind the ComboBox update event use this..

Private Sub Combo16_AfterUpdate()
     Me.SubformName.Requery
End Sub

[barryg80]

Should the query select the partner from the clients table or from the partners table?

Theres a one-to-many relationship between partners and clients.

[barryg80]

Also in case its important, the partners field in the clients tale is a lookup field based on the partners single text field table

[Xipooo]

Should the query select the partner from the clients table or from the partners table?

Theres a one-to-many relationship between partners and clients.

It really wouldn't matter since presumably the Client.Partner field is the foreign key of your partners table... but here you aren't even doing a join between the two tables anyway. Filtering your clients table should be fine.

[barryg80]

It really wouldn't matter since presumably the Client.Partner field is the foreign key of your partners table... but here you aren't even doing a join between the two tables anyway. Filtering your clients table should be fine.

Thanks, relationship is this..

[Xipooo]

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.

[June7]

Filter the main form to the Client Partner and the linked subform should show only those clients that are associated with the Partner.

[Xipooo]

Filter the main form to the Client Partner and the linked subform should show only those clients that are associated with the Partner.

You know, you just made me realize he's probably using a Master/Child filter, not a query to do the filtering for him. My bad.

[barryg80]

Maybe I am totally astray here folks. I have the combo on the main form along with clinetid and name. I filter by partner and it shows only clients of that partner on the main form. As in you can navigate from one to the next. In the subform it shows tax details of the selected client..