Base the subform on a query which references the controls on the main form as
parameters. For each control examine it for 'OR IS NULL' to make it
optional. For simplicity lets assume that there are only three controls,
one, a combo box, for CustomerID and the others, both text boxes, for
DateFrom and DateTo so that orders for a selected customer between two dates
can be returned. The query would be along these lines:
PARAMETERS
Forms!YourForm!txtDateFrom DATETIME,
Forms!YourForm!txtDateTo DATETIME;
SELECT *
FROM Orders
WHERE (CustomerID = Forms!YourForm!cboCustomerID
OE Forms!YourForm!cboCustomerID IS NULL)
AND
(OrderDate >= Forms!YourForm!txtDateFrom
OR Forms!YourForm!txtDateFrom IS NULL)
AND
(OrderDate < DATEADD("d", 1,Forms!YourForm!txtDateTo)
OR Forms!YourForm!txtDateTo IS NULL);
For more controls you'd extend the query in the same way for each. Note
that I've declared the date parameters. This can be important with date
parameters as otherwise they might be interpreted as arithmetic expressions
rather than dates if entered in short date format, and give the wrong results.
With the above example if the user selects a customer, but enters no dates
all orders for the selected customer will be returned; if they don't select a
customer but enter both dates, then all orders for all customers between the
dates will be returned; if they select a customer and enter both dates then
orders between those dates for the selected customer only will be returned.
If they only enter one date then orders any time after of before that date,
depending which one is entered, will be returned. And so on…
To filter the rows shown in the subform on the basis of the values in the
controls on the main form requery the subform control in the button's Click
event procedure:
Me.sfrOrders.Requery
where sfrOrders is the name of the subform control, i.e. the control in the
main form which houses the subform, not the name of its underlying form
object; unless both have the same name of course.
Ken Sheridan
Stafford, England