Using optional criteria for a subform query

[sponge]

Hi,

Is there a simple way to add optional criteria in a query for a subform? Basically, what I'm trying to do is have a number of comboboxes in the parent form that indicate what records are shown in the subform (these records could then be added and/or deleted). I would like to be able to have a combobox such as "Month" that would filter the data shown in the subform to that month. If no month is selected, then the subform would show all months.

Any help would be much appreciated.

 

[RuralGuy]

Are you aware that you can have more that one Link Master Field?
Link Master Field = Field1;Field2;Field3

Edit: I've never had to use it but I'm sure I saw that somewhere!

From VBA Help:
When you specify more than one field or control name for these property settings, you must enter the same number of fields or controls for each property setting and separate the names with a semicolon ";".

 

[iago18]

I'm not sure how RuralGuy's solution would work. Here's my suggestion:

In your query criteria use the LIKE operator. For example: SELECT Table.Field FROM Table WHERE Table.Field LIKE 'Frog'; The key is that if your query states LIKE '*' all records will be returned.

Now do this:
1. Add a value to each criteria combo box which has '*' as the bound value. (or just * without quotes if the other bound values of the combo are numeric) If you combo displays table values, you'll need to add an artificial value for this using a UNION query. Something like:
SELECT Table1.EmpID AS EmpID, Table1.EmpName FROM Table1 UNION SELECT '*' AS EmpID, '(All Employees)' AS EmpName FROM Table1 ORDER BY EmpID;
By putting "("s around the artificial employee name and sorting by this field you put this combo value first in the list.

2. Update the subform query to be bound to the combo box using the LIKE operator. Example: "SELECT Table.Field FROM Table WHERE Table.Field LIKE '" & [Forms]![Form].[OptionalCombo] &"';"

3. Set the Default Value of the combo to '*' so that "all values" are displayed when the form is open.

4. After update of the combo do two things:
A.) If the combo is null, reset it to the default value
B.) Requery the subform

I know this sounds complicated, but if you can master it it's quite effective. Give it a try and let me know if you have any questions.