Search code examples
vbams-accessms-access-reports

MS Access: Print a Report 7 times, Increase Date by 1 on each copy?

I am developing a Report in MS Access that pulls a list of employees. It generates a daily sign-in sheet which then gets posted in each department.

I would like to open the report, ask for the "Start Date", and then print that report for the next 7 days, increasing the "Start Date" parameter by 1 on each copy. (the start date is not stored in the database, its just an input parameter that gets used to print on the report)

The report is grouped by department and each department prints on a new page (Group Header section has "Force New Page" set "Before Section").

I am assuming this all needs to be done in VBA in a while loop from the Click() event of the Command button that opens the report, but I am out of my league here. Any idea how to accomplish this?

Solution

  • The way that I would do this is:

    • Create a table (called tblReportDate) to store the date in a field called ReportDate;
    • Create a small form, with a text box called txtReportDate that has an input mask suitable for dates;
    • Add a textbox to the report, and set the controlsource to be =DLookUp("ReportDate","tblReportDate");
    • Add a command button to the form, and have the following code in its OnClick event:
    Private Sub cmdPrint_Click()
    On Error GoTo E_Handle
    Dim intLoop1 As Integer
    For intLoop1 = 0 To 6
        CurrentDb.Execute "DELETE * FROM tblReportDate;"
        CurrentDb.Execute "INSERT INTO tblReportDate (ReportDate) SELECT " & Format(DateAdd("d", intLoop1, Me!txtReportDate), "\#mm\/dd\/yyyy\#") & ";"
        DoCmd.OpenReport "rptReport", acViewPreview
        DoCmd.OutputTo acOutputReport, "rptReport", acFormatPDF, "C:\test\report-" & intLoop1 + 1 & ".pdf"
        DoCmd.Close acReport, "rptReport"
    Next intLoop1
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "frmReport!cmdPrint_Click", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

    I prefer to use a small form rather than the built-in InputBox as you can use the input mask to enter the date.

    In this example, I am just outputting the report as a PDF before closing it. You may wish to use DLookup as part of the name, rather than the loop counter that I am using.

    Regards,