Printing report fails

[silverback]

I have a form which collects the start and end dates for a period. When the user has supplied these and clicks the 'Go' button, VB code executes a DoCmd.OpenReport, opening it in Preview mode. The report uses the contents of the two date fields to show the period for which data is displayed.
The VB code subsequent to this DoCmd line 'tidies up' the form by clearing the contents of the date controls ready for next use. All works well up to this point.
However, when I try to print the report, Access errors (with an obscure message about an expression being too complex to evaluate etc).

After a very frustrating afternoon's work, I've tracked this down to the fact that, when I implement the print request, the report's underlying query runs again, and the print request fails because the dates on the form have been cleared; this is why the "expression is too complex to evaluate". (I have since got round this by not clearing the dates on exit from that piece of code, instead clearing the dates when an initial selection is made on the form.) However, I have some questions.
1. Why does the query run again when the report is in preview mode and a File | Print request is made? Doesn't preview mode mean the report is ready for printing?
2. Is there a way to 'pause' the execution of the VB code so that the DoCmd line is executed, the report opens, the user does what they want and only when the report is closed does the VB code carry on? I've tried making the report Modal, but that isn't working; the code still continues to execute all the code subsequent to the DoCmd line, even though the modal report is open in preview mode.
Thanks
Silverback

[HansV]

1) Frankly, I don't know. Perhaps to ensure that the printed report is up-to-date - after all, you might have left the print preview open for 24 hours...

2) As yo have found, settin the Modal property of the report to Yes doesn't help, but specifying acDialog for the WindowMode argument of DoCmd.OpenReport will pause code execution from the form until the report has been closed. E.g.

DoCmd.OpenReport "rptMyReport", acViewPreview, , , acDialog

or, equivalently,

DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WindowMode:=acDialog

[Wendell]

Regarding your first question, I believe it has always been that way, at least since Access 2.0. And I think the reason is that the Print Preview mode is doing something entirely different from the Print mode. In the first case it is rendering the report as something that will display in a window on the monitor, and in the second case it has to actually create the data necessary to format the report on the designated printer. It does seem a bit unnecessary to actually rerun the query however, as you would expect the printed report to reflect what you previewed. So perhaps Hans has identified the real reason - the data may have changed between the time of the preview and the print.

[silverback]

Wendell, Hans
Re the first part of my posting, It's frustrating, but I can see (just about) the logic. I was going to write that I'd wasted an afternoon trying to track down the problem, but it hasn't really been wasted as a useful lesson has been learnt, albeit at the expense of a few hours head scratching for my wife and myself.

Regarding Hans' suggestion of using acDialog, I tried this and it did, indeed, do exactly as I asked. The VB code execution was suspended so all looked good. However, when the preview appeared, all the menus were 'locked' so I still couldn't print! (Access 2003 in use).
I discovered that right clicking on the preview brought up a context menu, which offered Print (and Export), so I tried Printing from this. That worked but then, bizarrely, on return from the print to the report preview, all the menus had become unlocked!
I decided that this was going to be too complex to describe to my users, so I've abandoned the use of acDialog and reordered the code to clear the date fields 'on the way in' instead of 'on the way out'. I have logged the use of acDialog for possible future use, though.
Thank you both for your replies.
Silverback

[HansV]

I'm glad you've found a solution.