Pause code until form is opened & value set

[rglman]

Please help; I have a module that imports data from an Excel speadsheet into a staging table. Then it runs various queries to append the data to the appropriate tables within the db. Previously it was an annual process, and it was easiest to simply modify the query defs once a year to identify the reporting period. Now, it is being done quarterly, so I have created a modal form to allow the user to select the quarter from the drop-down list. The code in my module is as follows:

' Begin import process

DoCmd.OpenForm "frm_quarter_for_import", acNormal
DoCmd.GoToControl "Quarter"

' START IMPORTING ANALYZER TEMPLATE

DoCmd.SetWarnings False
' Import the current template into the "tblDetailImportStage" table. (Range: B10:K20)
DoCmd.TransferSpreadsheet acImport, 8, "tblDetailImportStage", "Quarterly Update Facilities Consolidated.xls", False, "Facility001!B10:K20"

' UPDATE ALL ASSOCIATED DATA TABLES WITH THE CURRENT DATA
' Update the "tblDetail" Phase 1
DoCmd.SetWarnings False

DoCmd.OpenQuery "qryLoadDetail", acViewNormal, acEdit
DoCmd.Close acQuery, "qryLoadDetail"

My problem is how do I stop the code until the form has opened and the user has selected the appropriate value and clicked the "OK" button on the form to set the control value? The query def "qryLoadDetail" uses the value from the form's "Quarter" control to populate the records with the proper Quarter ID.

Please help.

 

[RichO]

Does your form close once the appropriate value is entered? You could open the form in "acDialog" mode which will suspend running of the code until the form is closed.

 

[rglman]

The rest of the code runs, BEFORE the form even opens! Then after everything has finished the form pops open. I have tried "acDialog" and it doesn't appear to make any difference. I think the solution is going to be some kind of "If - Then - Else" or maybe a "Do - Loop" statement to keep the code flow progressing until the condition that the form control value is not null. My problem is I'm not sure how to do that.

 

[RichO]

Are you doing:

DoCmd.OpenForm "frm_quarter_for_import", acDialog

or

DoCmd.OpenForm "frm_quarter_for_import", , , , ,acDialog

?


I've used acDialog (the second way) on a number of forms and it always successfully suspends the running of code until the form is closed.

 

[rglman]

Sorry.... I've been asleep at the switch!!

This was all I needed:

DoCmd.OpenForm "frm_quarter_for_import", acNormal, "", "", acEdit, acDialog

Thanks for your help!