Filter Subform with mulitple search criteria

[TKing]

Hi all,




I would like controls on the form that will filter the subform. Ideally, these would filter "after update" of the control, and would add new criteria to the filter as the next control box is chosen. It would also be fine just to filter by all controls with a command button.

The form: fRequests, with a subform: sbf_supportrequest.
This is a pretty simple subform with 6 controls. Right now the recordset is a single table. The parent/child values are not set.

The search controls on the main form are unbound. They are cboEmployee, cboRequesting, cboStatus, txtstartdate and txtenddate (short date). For the dates, I would like the user to be able to enter either both a start/end or only 1 of the values (i.e. if start date, it would search all records from that date to the present).

Also, instead of leaving the search criteria blank for no filters, it'd be nice to have an "all" choice (which means the same thing, but isnt' it my table).

This is asking a lot, I'm okay at adapting existing code for my own uses, so even an example of a similar database would be good. I'm using ACC2007.

thanks for your time.

[pbaldy]

The sample db at the end of this FAQ demonstrates one method:

http://www.baldyweb.com/BuildSQL.htm