DoCmd.OutputTo On Report Doesn't Unload

Question

In Access 2007, I have a form set up to allow previewing and exporting options for the project's reports.

DoCmd.OutputTo seems to behave strangely when dealing with reports that have the Modal property set to true. Modal is currently set to True in the Open event for all of the reports that I'm working with.

If I do

DoCmd.OpenReport szReportName, acViewPreview
DoCmd.Close acReport, szReportName

Then, focus and control is returned to the executing form normally.

If I export directly instead and use

DoCmd.OutputTo acOutputReport, szReportName

Then, the report is exported correctly, but control never returns to the executing form. It's completely disabled. The same code works just fine, if I use Modal = False when opening the report instead. I experimented a little with the report's event hooks to try and figure out what the difference is and OnUnload is never hit after OutputTo is called.

I know I could work around this by only making the report modal when I need it to be modal, but it's definitely easiest to do from inside the report's code instead of the module opening it and I really don't think I should be having this problem. I also have no problem exporting the report from preview mode instead of directly from VBA, but apparently the customer does...

So, actual questions:

1. Is there any good reason for OutputTo to not trigger the Unload event? If this is normal behavior, then fine, but I would at least like to understand the reason for it.
2. Is there any way to export a modal report and still regain control of the other windows? Or at least, a non-hacky way to re-enable and give focus to the calling form?

Answer 1

Your first code:

  DoCmd.OpenReport szReportName, acViewPreview
  DoCmd.Close acReport, szReportName

...is going to error out if the report is modal. The reason why is the second line can't be executed until the report opened on the first line is closed. So, as soon as you get to the second line, you get an error, because the report isn't open any longer by that point.

What you need to do is not set the modality in the report's properties sheet, but in the DoCmd.OpenReport command:

  DoCmd.OpenReport szReportName, acViewPreview, , , acDialog 

The acDialog switch opens it modally, and code will pause until the report is closed, and then continue.

But when you use DoCmd.OutputTo, it will behave normally, because the report is not modal.

In general in Access forms and reports, you don't really need to set the Modal property at all, since you can always open it modally at runtime.

I don't know why you believe you need to set the modal property in the report's OnOpen event at all. There is no code needed there, and I don't think it's a good place to be changing that (and would have expected it to not work in the first place, as you can't really change window mode after the window is already created; maybe the OnOpen executes before the visible window is created?).

But you're doing things backwards from the standpoint of standard Access practices. I don't know why you resist setting the window mode in the calling code, as it means you can use the report in any context without having to have code that worries about the issue inside the report itself. I always think that reports and forms should be as dumb as possible, knowing only about themselves, and let the calling contexts have all the intelligence in them (though with as little poking into the internal contents of the called form/report as necessary).