Printing a report based on the filtered results of a form

[ecco]

Hi All,

I am trying to print a report based on the filtered results of a form where the data record source is generated from a query. What I have is five unbound comboboxes on a form that filter the results of the query on a subform which works fine in whichever combination I set, I then want the report to print out the results of the filter and the filter combination that I used - basically exactly as it appears on the form (I have used the same query / subform in the report with text boxes to show the filters used on the form). My VBA skills are quite limited (but improving!) and I have trawled the web trying different code examples but can't seem to get it nailed. Current filter code follows;

Option Compare Database
Option Explicit
 
Private Sub PrntConfigReport_Click()
If Me.FilterOn And Len(Me.Filter & "") > 0 Then
DoCmd.OpenReport "rpt_SCR_Report", acViewPreview, WhereCondition:=Me.Filter & "'"
Else
DoCmd.OpenReport "rpt_SCR_Report", acViewPreview
End If
Exit_Preview_report_Click:
Exit Sub
End Sub
 
Private Sub fltrControlSystemAcr_AfterUpdate()
Call RunFilter
End Sub
 
Private Sub fltrPriority_AfterUpdate()
Call RunFilter
End Sub
 
Private Sub fltrReason_AfterUpdate()
Call RunFilter
End Sub
 
Private Sub fltrStatus_AfterUpdate()
Call RunFilter
End Sub
 
Private Sub fltrSystem_AfterUpdate()
Call RunFilter
End Sub
 
Private Sub Form_Load()
Call RunFilter
End Sub
 
Private Sub RunFilter()
    Dim strFilter       As String
    Dim bFilter         As Boolean
bFilter = False
strFilter = ""
 
If Nz(Me.fltrSystem, "<All>") > "<All>" Then   'SS_Num
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "SS_Num = '" & Me.fltrSystem & "'"
    bFilter = True
    End If
 
If Nz(Me.fltrControlSystemAcr, "<All>") > "<All>" Then    'ControlSystemAcr
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "ControlSystemAcr = '" & Me.fltrControlSystemAcr & "'"
    bFilter = True
    End If
 
If Nz(Me.fltrStatus, "<All>") > "<All>" Then  'Status
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "Status = '" & Me.fltrStatus & "'"
    bFilter = True
    End If
 
If Nz(Me.fltrPriority, "<All>") > "<All>" Then  'Priority
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "Priority = '" & Me.fltrPriority & "'"
    bFilter = True
    End If
 
If Nz(Me.fltrReason, "<All>") > "<All>" Then  'Reason
    If Len(Nz(strFilter)) > 0 Then strFilter = strFilter & " And "
    strFilter = strFilter & "Reason = '" & Me.fltrReason & "'"
    bFilter = True
    End If
 
If bFilter Then
    Me.subfrm_SCR_Report.Form.OrderBy = ""
    Me.subfrm_SCR_Report.Form.Filter = strFilter
    Me.subfrm_SCR_Report.Form.FilterOn = True
    Else
    Me.subfrm_SCR_Report.Form.FilterOn = False
    End If
End Sub

Really appreciate any advice,

Thanks,

Ecco

 

[Mihail]

Just an idea:
Use a function ( say FilterString) to return the filter string:

Dim Filter As String
  Filter = FilterString

Private Function FilterString() As String
  ' You already have the code in yours RunFilter sub.
  ' You must adapt it a litle bit in order to return the SQL string
End Function

Then use Filter to filter either the form either the raport.

DoCmd.OpenReport "ReportName", , , Filter

 

[ecco]

Hi Mihail,

Thanks, I will give it a try and see if I can get it to work.

 

[nanscombe]

Just a thought, but I wouldn't use the word "Filter" as a variable name, it could get confusing.

Try using something like myFilter as it would be easier to read and understand at a later date.