Reports based off Filters on a query (1 Viewer)

[sladetroityer]

Currently, I have Macros that filter my employee query and I am looking to apply those same filters to the reports. How can I achieve this?

 

[Alansidman]

Why not make the query the record source for the report.

 

[sladetroityer]

The qselEmployeeInformation is the query for the reports but I want the reports filtered by department which I have macros for. The referenced query has ALL employees in it and I want the reports to be filter by department.

So the query is the record souce for the reports

 

[Alansidman]

Either create a parameter for the query or build a search form that will insert the parameter in the query. Then use that query as your record source for your report.

Here are two links that will help you.

http://www.techonthenet.com/access/queries/index.php

Scroll down to parameter queries.

http://www.datapigtechnologies.com/flashfiles/passparamtoreport.html

http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[sladetroityer]

Thanks for the info but I believe there has to be a way to use the same macros that are currently filtering my query. I am getting this error when I run the macro on the report button;

The 'Setfilter' action required a valid contol name that correspons to a subform or subreport.

Any ideas what the subreport name would be? Or how I can find it?

 

[Alansidman]

I don't use macros, only VBA (more flexibility) so I am unable to help you any further with this issue. Perhaps if you convert your macros to VBA it might make things easier and you will then be able to go forward.

 

[sladetroityer]

    ' FilterType - 1 = AVE, 2 = SCE-CHA, 3 = EPR, 4 = SCE-INT, 5 = SCI, 6 = VEE, 7 = UCN, 8 = SCI-NSA: Restraints, 9 = SCE-ENG, 10 = SCE-ELE, 11 = SCE-BOD, 12 = ALL, 13 = SCI:, 14 = SCI-ARD, 15 = SCI-BPS, 16 = SCI-CPG, 17 = SCI-DEB, 18 = SCI-MFS, 19 = SCI-NSA
    If (TempVars!FilterType = 1) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""AVE""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 2) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-CHA""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 3) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""EPR""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 4) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-INT""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 5) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""SCI""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 6) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""VEE""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 7) Then
        DoCmd.SetFilter "", "Left$([FIN],3)=""UCN""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 8) Then
        DoCmd.SetFilter "", "[strLowest_Level_Department]=""SCI-NSA: Restraints""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 9) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-ENG""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 10) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-ELE""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 11) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCE-BOD""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 12) Then
        DoCmd.SetFilter "", "", ""
    ElseIf (TempVars!FilterType = 13) Then
        DoCmd.SetFilter "", "Left$([FIN],4)=""SCI:""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 14) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-ARD""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 15) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-BPS""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 16) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-CPG""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 17) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-DEB""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 18) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-MFS""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 19) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""SCI-NSA""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 20) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-PCV""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 21) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-SRT""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 22) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-VIV""", "NavigationSubform"
    ElseIf (TempVars!FilterType = 23) Then
        DoCmd.SetFilter "", "Left$([FIN],7)=""VEE-VLE""", "NavigationSubform"
    End If

mcrFilter_Exit:
    Exit Function
mcrFilter_Err:
    MsgBox Error$
    Resume mcrFilter_Exit
End Function

 

[sladetroityer]

These filters work but I want to add the filter to the reports as well.....When I added the mcrFilter to the report button and ran it I received the error message mention above.

 

[sladetroityer]

Alan,
It appears that my filters are working for the reports but the path to the actual report is broken? How do I figure out the path code?

 

[sladetroityer]

, Left$([FIN],3)="AVE", [Forms]![ParentFormName]![SubFormName].[Form]![SubFormControl]

I believe this is the path to the report that I want filtered but I can't seem to get the correct form names in the proper order.

 

[sladetroityer]

Maybe this will help..... i have a button for each report and I am using a Browse To action that asks for;
Object Type: Report
Object Name: rptContactInformation
Path to Subform Control: frmNavigationMain.NavigationSubform>frmReportCenter.sfrReprtPage

This works but I want the report rptContactInformation to use the filters......

Any idea how to achieve this?

 

[sladetroityer]

What about the Where Condition on the Browse to action? Is that a possibility?

 

[sladetroityer]

OK, when I put the string in the where condition (Left$([FIN],3)="AVE") the report is filtered. Now how do I tell it to look at the macro that set's these filters?