set subform filter before opening main form

[ednz]

I have used vba code to open a (unbound) form that has a subform bound to a query.
I want to apply a filter to the subform. I can do it after the main form is opened, but then get many unwanted records before the filter is applied.
How can I set the subform filter before the subform accesses any records?

 

[Pampers]

The subform is based on a query. What kind of filter you want to set? More detail please

Pampers
Just let it go...

 

[ednz]

Hi Pampers

Thanks for your response.

Filtering subform according to the value of a control on my menu form.

I'm now trying using an on-open event on the subform to check the menu form control and hence apply the filter.

It's messy because it needs code on each subform.

Any better ideas?

 

[Pampers]

I think a better idea would be to base your query that is populating your subform on the value in the mainform. You can do that by setting your form in designmode. Go to the query, and for the criteria use the wizard to build the criteria, referencing the mainform control.


Pampers
Just let it go...

 

[Pampers]

In your query, you get something like this:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeName, tblEmployee.SuperVisorID
FROM tblEmployee
WHERE (((tblEmployee.EmployeeID)=[Forms]![Form1]![EmployeeID]));

Pampers
Just let it go...

 

[ednz]

Hi Pampers

Thanks for your response.

Sometimes I do not want to restrict records at all, so referencing the control is not suitable, or at least more complex.

Have done now by having the main form get the filter details from the menu form, where I used a label to hold the wording of the filter. Where no filter was required the filter was set to "".

Thanks for your input

Ednz