Code not pausing to wait for dialog response

Question

Hello,

I posted a question last week or so about a problem I was having getting the position of my pop-up forms to behave when the form is opened with the form's PopUp property = True.  My fix was finally to not open the form in acDialog mode (which sets both PopUp and Modal to True), and instead to use acWindowNormal and then manually set the form properties are (PopUp = False, Modal = True).  This actually seems to accomplish all of the behavior I need, except maybe for one critical thing...

While all of the Microsoft help that I can find on acDialog mode seems to indicate that it only affects the level of the window, it seems that it also pauses any running code to wait for the dialog form.  So, while I have fixed my window positioning problem by not using the PopUp = True setting, I now have a code problem.

In my database, a user chooses a report type from a menu.  That menu button then opens the dialog form to get some parameters for the report.  As it is now (without acDialog mode) the code just keeps going without waiting for the user to fill in any parameters.  So the report opens by itself a fraction of a second after the dialog opens, using whatever default values are specified.  Not good.

Is there any other way I can get the code to pause and wait before it continues on to open the report?

Thanks!

Emily

Answer 1

Question:  Do you have to have the data assembled before you initiate the opening of the report?

No, a report's recordset is not loaded at this stage, so you can manipulate the data at will.  In my demo, in Method 2, the unbound form is opened in dialogue mode, so this pauses the opening of the report, which is resumed once the form is closed.  This may give you the positioning problems which you encountered, however.  Method 1 uses a different approach; in this the code checks to see if the form is open, and if not, transparently cancels the opening of the report if not, and opens the form, passing the report name to it via the OpenArgs mechanism.  The report is then reopened from the form after entering the data.  To the user it appears as if the report opens only once after entering the parameters via the form.

The report can be opened by any means, including from the navigation pane.  With method 1, however, as cancelling the opening of the report raises an error if the report is opened by means of the OpenReport method or macro action this would raise an error, so this needs to be handled.  As no developed application would expose the navigation pane to a user of course, the error handling is essential.  This is illustrated in my demo.

The other option of course, and the most commonly used, is simply to open the form first and then open the report from the form after entering the parameters.  The MultiSelect demo in my same OneDrive folder illustrates various ways of filtering or restricting a report's output in this way.

Answer 2

Question:  Do you have to have the data assembled before you initiate the opening of the report?  Or can you run a bunch of code to crunch a bunch of numbers within the On Open event to create the data that the report is based on?

If it's too late by the On Open event to start creating the data for that report, then that would prevent me from using that approach (although it sounds like a simpler approach otherwise).

Thanks!

Emily

Answer 3

Another approach is to open the form from the report's Open event procedure, not from the calling procedure.  You'll find an example as ReportDialogue.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file I think method 1 would probably suit you best as this does not open the form in dialogue mode.  Note the different positioning of the form when using method 2, which does open the form in dialogue mode.

Answer 4

OK, solved my own problem.  How do I give myself a point?

I'm using a do loop and a WaitSeconds sub that I stole from here:

http://www.fmsinc.com/microsoftaccess/modules/examples/AvoidDoEvents.asp

First, go to the fmsinc.com link above.  Cut/paste the declaration and the sub into a module.

Then you need a place to store a global variable.  I use a hidden form called Globals, and create a field called mypause.  So you can reference the field by Form_Globals.mypause to set or get values for mypause.

Then, in the Click event code for your menu button, add the following code when you open the dialog form:

'prompt user to select date for report

DoCmd.OpenForm "WeekSelectMsgbox", , , , , acWindowNormal

''DoCmd.OpenForm "WeekSelectMsgbox", , , , , acDialog

'' I am not using acDialog mode because it screws up the positioning of the form.  But without

'' using acDialog, the code does not wait for me.  So add a loop below to wait.

'' Loop to make code wait for response from WeekSelectMsgbox

Form_Globals.mypause = 0  '' reset mypause to 0

Do While Form_Globals.mypause = 0

    WaitSeconds (2)

Loop

Then, in the btnOK_Click event (or equivalent event) in your dialog form, add the following code to set mypause = 1.

'' Set mypause <> 0 to exit loop in Form_ReportsMenu

Form_Globals.mypause = 1

Voila!  Tested, works great.  Thought someone else might be able to use.  And still would appreciate any other easier or better suggestions.

Time for a margarita.

Thanks!

Emily