Filtering a subform based on user input

[dawnd3]

Hi there, I have a form based on a table of contractors and a subform based on the child table that lists the shifts for each contractor. I have a field called "week number" in the shift table and I want to be able to ask the user the week number when they open the form and have it filter out the shifts for just that week number. I have tried to run the apply filter on the OnOpen of the subform using a query that was identical to the datasource except it used a parameter in the week number field criteria. That didn't work. Can anyone help? Dawn

 

[HIM]

You can always modify the datasource of the child form to be based on a query.... adding a condition like this [Enter Week #] this will ask the user to enter a week number when opening.....

 

[dawnd3]

Hi, thanks. I tried that also. When I opened the form it indeed asked me for the week number, however it continued to ask me every time I moved from record to record.

 

[tempclerk]

Try using [Week number?] as the control source for a textbox on your main form, and add that field to Link Master Fields and the week number field from your subform to Link Child fields.

 

[dawnd3]

The problem I think with that is that I already have the form and subform linked by contractor id. Won't that mess up that link? Isn't there a way to use a query filter each time the form is opened?

Dawn

 

[tempclerk]

You can link on BOTH fields. I believe you just need a semicolon between them.

 

[dawnd3]

I will try it and let you know what happens. I am still curious though why I am having problems using a query as a filter. Is this not the right situation for that? When would be a better time to use a query as a filter? This may be simplistic questions but I swear I am feeling lost in this area.