How to change subform query criteria /w command button

[Jarekb]

I have a main form called MAIN_FORM and a sub form called RESULTS_SUB. The main form will have some check boxes and combo boxes for the user to choose from. Based on the users choices the subform changes after pressing a button. The subform contains all records from two tables.

My question is, how do I modify the subform query to change the criteria to what the user selects.

An example would be that RESULTS_SUB shows 100 employee records. The user only wants to see employees who were hired within the past 30 days, selects that, and hits a command button. The button would change the subform query criteria and then requery the subform to reflect the changes.

Thanks for any help.

 

[PHV]

You may consider the Filter and FilterOn properties of the form object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Jarekb]

Thanks for the quick answer, I'll look into applying the filter to redisplay records in the RESULTS_SUB. I take it that I can create filters that would do something like (Date()-DT_HIRE) > 30?

Also, I wanted to have several buttons that would print reports based on the results in the subform. Is the best option to do this involve using DoCmd.OpenReport and changing the Where clause?

 

[PHV]

Have a look at the 4th parameter of the OpenReport method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Jarekb]

I can't get the filter to work, I keep getting a "Run-time error code '424'" Here's what I have so far.

Private Sub Notice_Letter_Click()

'Me.RESULTS_SUB.Form.Filter = "DT_HIRE =(DateDiff(d,Date$(),[DT_HIRE])>30"
MAIN_FORM.RESULTS_SUB.Form.Filter = "EMPLID = 102697504"
MAIN_FORM.RESULTS_SUB.Form.FilterOn = True

Me.RESULTS_SUB.Requery

End Sub

I wasn't sure if my date subtraction part was correct so I tried a simple filed = number filter (emplid randomly generated). Both didn't work. Right now I'm just testing how this will work, all I have in Main_Form is a button and a subform. The subform is just a datasheet view of several fields.

Any thoughts?

Thanks

 

[Jarekb]

Figured it out

Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = "(DateDiff('d',Date(),[DT_HIRE]))>'30'"
Forms!MAIN_FORM.RESULTS_SUB.Form.FilterOn = True

There was a problem with the syntax and the way I was calling the subform.

 

[Jarekb]

Well I spoke too soon. While the above code does change the results of the subform...I'm not sure what's filtered out.

Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = "(DateDiff('d',[DT_HIRE],Date$()))>'30'"
Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = "[DT_HIRE] > 5/31/2006"
Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = "(DateDiff('d',[DT_LAST_WORKED],Date()))<'35'"

I use a table of randomly generated information and when I apply any of those filters the results aren't correct. The easiest to tell is the one that states only records from after 5/31/2006 should show, but I've got older records in the results.

 

[PHV]

Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = "DateDiff('d',[DT_HIRE],Date())>30"
Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = "[DT_HIRE] > #2006-05-31#"
Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = "DateDiff('d',[DT_LAST_WORKED],Date())<35"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Jarekb]

Thanks for the help, the problem wasn't with the SQL statement, rather I didn't change the date fields to date format in the table. After making that change the filter worked. The only problem I had was that only parts of the code would work in the filter. As in only the [DT_HIRE] > #2006-05-31# filter worked, while the other filters didn't kick in. Not sure what the problem was, but I used this instead and it worked fine.

sqlNC = "[DT_HIRE] > #5/31/2006# and (DateDiff('d',[DT_LAST_WORKED],Date$()))<35"
sqlNC = sqlNC + "and (DateDiff('d',[DT_HIRE],Date$()))>30"
Forms!MAIN_FORM.RESULTS_SUB.Form.Filter = sqlNC