exporting a filtered report after users verifies content

[Foe]

I have a report that is opened via a Form that lets the user choose a date to filter the report. The report opens in Print Preview mode.

What I'm attempting is to give the user an easy way to export the report once they've verified the report is accurate.

One way I've tried to do this was to use the OnClose event to execute a vbYesNo MsgBox giving them the option to export. The problem here is that I can't do the export while the report is closing.

Run-time Error '2585': This action can't be carried out while processing a form or report event.

I tried to move my MsgBox to the OnUnload so that could cancel the Unload, but was met with the same results. Code for that attempt follows:

Private Sub Report_Unload(Cancel As Integer)
    Dim Response
    Response = msgbox("Do you want to save a copy of this log?", vbYesNo, "Export to PDF")
    If Response = vbYes Then
        Cancel = True
        DoCmd.OutputTo acOutputReport, "rptWatchLog", acFormatPDF
    End If
End Sub

Some research indicates perhaps the DoCmd.OutputTo is happening to quickly. Would including some type of pause in the code execution between the Cancel = True and the DoCmd solve my problem? Though frankly, even if it did it doesn't feel very elegant. I also recognize that I'd need to reinitiate the Unload>Deactivate>Close process after the export completed.

I've spent about the last 2 hours trying to figure this one out and I'm stumped.

I'm open to other suggestions to accomplish my goal. I initially began by having an Export Command Button on the form they use the choose a date, but was unable to have the exported report honor the user supplied filter from that form.

Here is the code from that form that is applying the filter:

'Open Watch Log Report with chosen date as filter
Private Sub cmdOpen_Click()
     'use date even though it's not saved anywhere
     If Me.Dirty Then
        Me.Dirty = False
     End If
     DoCmd.OpenReport "rptWatchLog", acViewPreview, , "[EntryDate] = #" & Me.txtDate & "#"
     DoCmd.Close acForm, "frmwatchlogdatepicker"
End Sub
'close the form w/o entering report
Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmwatchlogdatepicker"
End Sub

 

[Foe]

I've spent a good bit of time trying to solve this one.

My previous chain of events to get a filtered report, view it and then export it:

Command button on frmMain opens frmDatePicker > User enters date in frmDatePicker.txtDate > Command button on frmDatePicker opens rptWatchLog in acViewPreview using Where condition derived from frmDatePicker.txtDate and then closes the frmDatePicker > When user closes rptWatchLog a MsgBox offers the option to export rptWatchLog
This method didn't work for reasons detailed above.

I've tried attacking it in a new way. My new method is as follows:

Command button on frmMain opens frmDatePicker > User enters date in frmDatePicker.txtDate > Command button on frmDatePicker opens rptWatchLog in acViewPreview with Where condition derived from frmDatePicker.txtDate > rptWatchLog creates copy of itself as rptDailyLog > When user closes rptWatchLog a MsgBox offer the option to export rptDailyLog
This solved the Run-time Error '2585' issue.

One issue this presented was that when I try to create rptDailyLog, Access doesn't like it if it already exists. Solved this with a DoCmd.DeleteObject that fires from the frmDatePicker (as seen below).

The remaining issue I'm having is that I can't get the Where condition to save and be included in the copied report. My rptWatchLog will open correctly using the intended filter, but when it creates a copy of itself that filter isn't included.

One thing I've tried is to keep the frmDatePicker open so that I can use it to set the filter on rptWatchLog after it's open. This doesn't appear to be working.

I've moved the DoCmd.SetFilter to several different events, but it never sticks. No matter how many different ways I've tried to manipulate the filter the rptDailyReport always displays either today's date or no filter at all.

I've also moved the DoCmd.CopyObject to several different events. The code below is just the last thing I tried.


The code for frmDatePicker

Private Sub Form_Load()
    DoCmd.DeleteObject acReport, "rptDailyLog"
End Sub

'Open Watch Log Report with chosen date as filter
Private Sub cmdOpen_Click()
    'use date even though it's not saved anywhere
    If Me.Dirty Then
        Me.Dirty = False
    End If
    DoCmd.OpenReport "rptWatchLog", acViewPreview, , "[EntryDate] = #" & Me.txtDate & "#"
End Sub

The code for rptWatchLog

Private Sub Report_Load()
    DoCmd.SetFilter "[EntryDate] = #" & Forms!frmWatchLogDatePicker.txtDate & "#"
    DoCmd.Save acReport, "rptWatchLog"
    DoCmd.Close acForm, "frmwatchlogdatepicker"
End Sub

Private Sub Report_Unload(Cancel As Integer)
    DoCmd.CopyObject , "rptDailyLog", acReport, "rptWatchLog"
End Sub

Private Sub Report_Close()
    Dim Response
    Response = msgbox("Do you want to save a copy of this log?", vbYesNo, "Export to PDF")
    If Response = vbYes Then
        DoCmd.OutputTo acOutputReport, "rptDailyLog", acFormatPDF
    End If
End Sub

Where am I going wrong? With either this method or the one detailed in the original post?

 

[Foe]

After posting about my new attempt I noticed a commonality between my latest efforts and my original export button. The exported report isn't using the filter.

How do I get the filter to stick?

 

[spikepl]

In the header of your report, create a button. In the click event of the button:
DoCmd.OutputTo acOutputReport, "rptDailyLog", acFormatPDF

In the format properties of the button, make it visible on screen only.

 

[Foe]

I'd prefer to have the report open in Print Preview. The button does not display in Print Preview.

 

[spikepl]

Then why do you need anything? The ribbon shows export to PDF

 

[Foe]

Simply put? Automation.

I know there are ribbon options and right click options*, but I want to automate the process.

I may also end up hiding the ribbon bar to minimize the chances of my users doing things outside the sandbox I'm creating for them.

*I'm developing in 2010 - My users have 2007 and I'm geographically remote. I don't know if those options are present in 2007.

 

[spikepl]

Hmm.. When you ask them, save the answer as a flag/global variable ..When the report is closed, nothing prevents you from afterwards outputting it to pdf, as per your command. AS to your filter, implement the filter in the report's record source, so that the query gets its parameter from the form, instead of as now, that the report is opened and then filtered.

 

[Foe]

I did briefly look at moving the filter into the record source (a query), but wasn't immediately sure how to do it and didn't' spend much time on it.

I did however, solve the problem. Your button idea led me down the path of having the (filtered) report open when I attempt to export it. That, along with some other searches which mentioned hiding the report led me to my solution. Might not be the most elegant, but it works.

here's the functioning logic:
Command button on frmMain opens frmDatePicker > User enters date in frmDatePicker.txtDate > Command button on frmDatePicker opens rptWatchLog in acViewPreview using Where condition derived from frmDatePicker.txtDate and then hides the frmDatePicker > When user closes rptWatchLog a MsgBox offers the option to export rptDailyLog > If user chooses yes, rptDailyLog opens (hidden) using frmDatePicker.txtDate to build the Where condition, then exports rptDailyLog, then closes rptDailyLog, then closes frmDatePicker(after the End If, in case they choose No)

rptWatchLog and rptDailyLog are identical with the exception that rptDailyLog doesn't contains any code.
I've gotten rid of the deleting and recreating of rptDailyLog since it was redundant with this method.

Code for the DatePicker:

'Open Watch Log Report with chosen date as filter
Private Sub cmdOpen_Click()
    'use date even though it's not saved anywhere
    If Me.Dirty Then
        Me.Dirty = False
    End If
    DoCmd.OpenReport "rptWatchLog", acViewPreview, , "[EntryDate] = #" & Me.txtDate & "#"
    Me.Visible = False
End Sub

Code for the rptWatchLog:

Private Sub Report_Close()
    Dim Response
    Response = msgbox("Do you want to save a copy of this log?", vbYesNo, "Export to PDF")
    If Response = vbYes Then
        DoCmd.OpenReport "rptDailyLog", acViewPreview, , "[EntryDate] = #" & Forms!frmWatchLogDatePicker.txtDate & "#", acHidden
        DoCmd.OutputTo acOutputReport, "rptDailyLog", acFormatPDF
        DoCmd.Close acReport, "rptDailyLog"
    End If
    DoCmd.Close acForm, "frmWatchLogDatePicker"
End Sub