Filtering a Report using a form

[jonman1124]

Hi, I am fairly new to Access as well as VBA.

My current setup has a report called "qall summary" which is generated by a bunch of queries all going back to a single query which collects the raw data together, called qall. I want to be able to:
1) generate this report based on ALL the data queried by qall
2) generate this report based after filtering the data generated by qall on a field called sellers, pulling only the information where sellers = 'CIBC'
3) same as #2 where sellers <> 'CIBC'

what i have right now is a form called report_switchboard where can click on an option group to filter the data generated by qall, but when i click on the button to preview the report, i see a report based on all the data, regardless of which option button i chose. I can see that the data has been filtered when i click on the option buttons, but the report does not reflect that. can someone help out?

this is what my code looks like:
Private Sub Preview_Click() 'preview one of two reports
Dim strreporttype As String

If reporttype = 1 Then
strreporttype = &quot;all summary&quot; 'percentage based report
Else
strreporttype = &quot;all summary delinq loan count&quot; 'count based
End If

DoCmd.OpenReport strreporttype, acViewPreview
DoCmd.Close acForm, &quot;report_switchboard&quot;

End Sub
-----------------------------------------------
Private Sub selleroption_AfterUpdate()
'apply or remove the filter for the option the user chose.

Select Case selleroption

Case 1
Me.Filter = &quot;seller = 'CIBC'&quot;
Me.FilterOn = True

Case 2
Me.Filter = &quot;seller <> 'CIBC'&quot;
Me.FilterOn = True

Case 3
Me.FilterOn = False

End Select

End Sub

 

[jjob]

Your problem is you are filtering the Form record source, not the report's.

I have not got Access on the PC I am using at the moment, and I can't remember the syntax for the


DoCmd.OpenReport strreporttype, acViewPreview

BUT there is an optional 'filter' that can be added - I have given you an idea of how it should work in your print code below, BUT you need to

a) Find out where the Filter parameter fits into the DoCmd string

b) work out what should be in your filter string, as I can't see how your form filter (the after_update code) which has 3 options, matches your report which appears to a have only 2 options ie Report type 1 or anything else.

So although you can see how you need different 'open report' lines, I haven't got the correct filters (or lack of them) showing.

Private Sub Preview_Click()  'preview one of two reports
Dim strreporttype As String

If reporttype = 1 Then
    strreporttype = "all summary"   'percentage based report
    '** This report would have no filter
    DoCmd.OpenReport strreporttype, acViewPreview
Else
    strreporttype = "all summary delinq loan count"    'count based
   'open report here with appropriate filter?
    DoCmd.OpenReport strreporttype, acViewPreview, "seller = 'CIBC'"
End If


DoCmd.Close acForm, "report_switchboard"

End Sub
-----------------------------------------------

 

[jjob]

Found the syntax, also what to do to use the form's filter

DoCmd.OpenReport strreporttype, acViewPreview, WhereCondition := Me.Filter

 

[jonman1124]

jjob, will this work, even tho the report being generated isn't directly based on the query qall? qall contains the field being filtered on, &quot;seller&quot; the report is based on a query called qreport which is a result of a series of queries on qall and does not include the field &quot;seller&quot;.

 

[GoDawgs]

You can only filter a report on fields that are actually in the report. Just bring that field (&quot;seller&quot along into the query that the report is based on. Hope that helps.

Kevin