Problem displaying a form that runs queries

[h2fcell]

Hello,
In Access 2007, I’m having a little issue displaying a form that runs
queries on load. Below is my code.
When I double click the form, the queries begin to run but the form only
appears after the queries are done.
If I put the code behind a command button it works fine. I can’t use a
command button because I need the queries to run unattended when the DB
opens. Where am I going wrong?

Private Sub Form_Open(Cancel As Integer)
DoCmd.MoveSize 2880, 4320
End Sub


Private Sub Form_Load()
DoCmd.SetWarnings False
'**********************************
'QUERIES MUST BE RUN IN THIS ORDER
'**********************************
Me.lboActiveQuery.Caption = "Running 02_qtbl106SupplierProducts"
DoEvents
DoCmd.OpenQuery "02_qtbl106SupplierProducts_Delete"
DoCmd.OpenQuery "03_qtbl106SupplierProducts"

Me.lboActiveQuery.Caption = "Running 05_qtbl106SupplierInfo"
DoEvents
DoCmd.OpenQuery "04_qtbl106SupplierInfo_Delete"
DoCmd.OpenQuery "05_qtbl106SupplierInfo"

Me.lboActiveQuery.Caption = "Running 07_qSupplierProducts_InfoLink"
DoEvents
DoCmd.OpenQuery "06_qSupplierProducts_InfoLink_Delete"
DoCmd.OpenQuery "07_qSupplierProducts_InfoLink"

DoCmd.SetWarnings True
End Sub

 

[Marshall Barton]

In Access 2007, I’m having a little issue displaying a form that runs
queries on load. Below is my code.
When I double click the form, the queries begin to run but the form only
appears after the queries are done.
If I put the code behind a command button it works fine. I can’t use a
command button because I need the queries to run unattended when the DB
opens. Where am I going wrong?

Private Sub Form_Open(Cancel As Integer)
DoCmd.MoveSize 2880, 4320
End Sub


Private Sub Form_Load()
DoCmd.SetWarnings False
'**********************************
'QUERIES MUST BE RUN IN THIS ORDER
'**********************************
Me.lboActiveQuery.Caption = "Running 02_qtbl106SupplierProducts"
DoEvents
DoCmd.OpenQuery "02_qtbl106SupplierProducts_Delete"
DoCmd.OpenQuery "03_qtbl106SupplierProducts"

Me.lboActiveQuery.Caption = "Running 05_qtbl106SupplierInfo"
DoEvents
DoCmd.OpenQuery "04_qtbl106SupplierInfo_Delete"
DoCmd.OpenQuery "05_qtbl106SupplierInfo"

Me.lboActiveQuery.Caption = "Running 07_qSupplierProducts_InfoLink"
DoEvents
DoCmd.OpenQuery "06_qSupplierProducts_InfoLink_Delete"
DoCmd.OpenQuery "07_qSupplierProducts_InfoLink"

DoCmd.SetWarnings True
End Sub

There is no "After Load" event, but I think maybe you can
use a flag to let the Current event know when to run. The
code would be along these lines:

module level variable section (at top of module, before any
procedures)
Private bolLoadDone As Boolean

in load event
bolLoadDone = True

in Current event
If bolLoadDone Then
'run the queries
bolLoadDone = False
End If

Note that, if those are all action queries, you should be
using the Execute method instead of OpenQuery. (FYI
OpenQuery is for opening a Select query's datasheet, RunSQL
is for running action queries and Execute is almost always
preferred over RunSQL because it runs synchronously).

 

[Jeanette Cunningham]

Suggest that you open a hidden form first.
Put the code that opens and runs the queries in a separate module and make
it a public function.
The code to call the function tnat runs the queries would be in the hidden
form.
When the queries have finished running, then open whatever form you want.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[h2fcell]

The reason I want the form to appear is because as the queries run I change
the Caption property of a Label on the form to display the name of the
running query. I’m not sure your suggestion will allow me to do that. I’ll
give it a try.
Thanks

 

[Jeanette Cunningham]

The reason that your form misbehaves is due to putting the code to run the
queries with the load event.

A form will not display properly until it has finished its load event.
Your form runs all those queries in its load event - so it will not finish
the load event until the queries have finished running.

Another suggestion (untested) is to use the current event instead of the
load event.
The current event will fire after the load event is finished (usually) - you
could test it.

Another alternative is to open the form normally - the one that shows the
query that is running in a label. In addition have a hidden form that opens
at the same time that calls the code that runs the queries.
You could use code on the hidden form to change the label on the visible
form as each query is running.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia