Printing Reports After Filter Is Applied

[ktrasler]

Hello all

I have this code below in access, which in itself works fine.

The only thing I want to change is I want the report to print automatically. If I change the acViewPreview to acViewNormal it errors?

How do I change this so that I can send the report directly to the printer and apply the filter and the label captions?

Private Sub cmdPrint_Click() 
     
    Dim myFilter As String 
    Dim X As Integer 
    Dim myFromDate As Date, myToDate As Date 
     
    If fraStd.Value = 2 Then 
        myOp = "<>" 
    Else 
        myOp = "=" 
    End If 
     
     'On Error GoTo Err_cmdPrint:
     
    Select Case WEEKSTATS 
    Case 2 
        X = 14 
    Case 6 
        X = 42 
    Case 13 
        X = 91 
    Case Else 
        Exit Sub 
    End Select 
     
    myToDate = Format(Date + 1 - Weekday(Date, 1), "mm/dd/yyyy", vbSunday) 
    myFromDate = Format(Date + 1 - Weekday(Date, 1) - X, "mm/dd/yyyy", vbSunday) 
     
    myFilter = cboColleague.Value 
     
    DoCmd.OpenReport "rptIndPickStats", acViewPreview, "" 
     
    With Reports![rptIndPickStats] 
        .Caption = WEEKSTATS & " Week Pick Review Stats" 
        .lblTitle.Caption = WEEKSTATS & " Week Pick Review Stats" 
        .lblWeekSummary.Caption = WEEKSTATS & " Week Summary" 
        .lblBreakdown.Caption = WEEKSTATS & " Week Breakdown" 
        .lblFrom.Caption = "From " & myFromDate 
        .lblTo.Caption = "To " & myToDate 
        .Filter = "[WCN]='" & myFilter & "' And [Date] >= #" & myFromDate & "# And [Date] <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'" 
        .FilterOn = True 
    End With 
     
    Exit Sub 
     'Err_cmdPrint:
     'MsgBox ("There seems to be an error, please contact support")
     
End Sub

Thanks

Kev

 

[boblarson]

When you change
acViewPreview to acViewNormal
the code acViewNormal tells it to print it, not view it. So, what you want is to open it with acViewPreview and then use

DoCmd.PrintOut acPrintAll

AFTER you set the filters.

Also, you can choose other options within the PrintOut but I just did the basic.

 

[ktrasler]

Hi Bob

I added the line like you said, it modified the headings as I wanted but didn't apply the filter.

I have now changed the code so that the filter is applied in the DoCmd. This seems to work fine now.

DoCmd.OpenReport "rptIndPickStats", acViewPreview, "", _
"[WCN]='" & myFilter & "' And [Date] >= #" & myFromDate & "# And [Date] <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'"
  
  With Reports![rptIndPickStats]
    .Caption = WEEKSTATS & " Week Pick Review Stats"
    .lblTitle.Caption = WEEKSTATS & " Week Pick Review Stats"
    .lblWeekSummary.Caption = WEEKSTATS & " Week Summary"
    .lblBreakdown.Caption = WEEKSTATS & " Week Breakdown"
    .lblFrom.Caption = "From " & myFromDate
    .lblTo.Caption = "To " & myToDate
    .FilterOn = True
  End With

DoCmd.PrintOut acPrintAll

Thanks for your assistance.

Cheers

Kev.