Subform filter

[shinobi]

Hey there,



I have a form and a few subforms (they are not linked and relate to different tables - reason being the user being able to access different data sets from one form instead of having several different forms and switching between them).

One of the subforms is a datasheet. It links to Visits table and shows the following records:
VisitID, Date, Client, Employee

Clients and Employees have their own tables that are linked to Visits table


I was wondering if it is possible to put a combobox on the main form that would filter the datasheet subform by client.

This can be achieved by rightclicking one of the clients in the subform and applying filter (Equals ClientA) but if there are too many clients the user would have to find the record with the required client first and then apply filter. A client combobox on the main form listing all the clients and then based on the selection filtering the subform would speed up the process. The form and subform are not linked so this probably requires some kind of JOIN operation?

[ranman256]

In a table, tQrys, put 2 fields,
Caption, Qry

fill the table with the queries you want in the subForm, and the caption to show in the combo box.
set cbo col widths: 1;0
to show the caption, hide the query name.
when user picks thE item in the cbo, set the form source.

sub combo_afterupdate()
  Me.subform.sourceobject = combo
end sub

[Micron]

I was wondering if it is possible to put a combobox on the main form that would filter the datasheet subform by client.
If your single subform could be a continuous form with a header, you could dispense with the nav buttons and dividing lines and put the combo in the header. The AfterUpdate event of the combo could cause the requery of the subform using the combo as a filter.

Otherwise, this would mean you'd have to hide/unhide the combo based on the subform in use (I presume it doesn't apply to all subforms) and it would have to be linked to the main form by vba since you couldn't design it that way.