Wait status

[John liem]

I have an application where I have to run different queries and output to Excel, the problem is (I think ...) that before the first query is completed, the second query is started. How can I make sure that the second query wait to execute before the first one is ended.
This is an example of my application:
Docmd.OutputTo acOutputQuery, "Qy_1", acFormatXLS, "C:\A.xls"
Docmd.OutputTo acOutputQuery, "Qy_2", acFormatXLS, "C:\B.xls"
Is there a way to put a "delay" or "wait" status in between?
Thanks, John.

 

[IMO]

Like this maybe?

Docmd.OutputTo acOutputQuery, "Qy_1", acFormatXLS, "C:\A.xls"

If MsgBox("First Phase Complete." & vbCrLf & vbCrLf & "Would you like to run the second Phase now?", vbQuestion + vbYesNo, "Phase 1") = vbYes Then
Docmd.OutputTo acOutputQuery, "Qy_2", acFormatXLS, "C:\B.xls"
Else
DoCmd.Close
End If


IMO

 

[John liem]

Thanks IMO for your quick reply, but I am looking for a solution without manual input, a kind of solution where you can put a "delay" time of approx. 30 seconds in between before the second query starts to run.

 

[IMO]

Create a form that opens for 30 seconds. On the close event of that form run the second query.

Docmd.OutputTo acOutputQuery, "Qy_1", acFormatXLS, "C:\A.xls"
'Open second form here

I'll send an example if you like.

IMO

 

[IMO]

The second form could even open as hidden if you want nothing visual

IMO

 

[John liem]

The script I have written is supposed to run from a Form with a button (to run the queries). If the end user clicks on this button, the 2 queries will run one after the other in the back ground.
Yes, I would appreciate if you could send me an example. I can imagine that the form will close and open again, I hope this is not going to enoy too much the end user of seeing it. :0)

 

[Rich]

Have you tried DoEvents ?

 

[bradcccs]

If you are not using the timer event on your current form, you could apply IMO's idea (or a version of) on the current form:

Private Sub YourButton_Click()
Docmd.OutputTo acOutputQuery, "Qy_1", acFormatXLS, "C:\A.xls"
Me.TimerInterval = 30000
End Sub

Private Sub Form_Timer()
Me.TimerInterval = 0
Docmd.OutputTo acOutputQuery, "Qy_2", acFormatXLS, "C:\B.xls" 
End Sub

This method uses the timer event to fire the second Output, and resets timer interval to 0 to avoid re-firing.

Just a thought.

Brad.

 

[IMO]

Here you are. It's in A2K, let me know if you need A97.

IMO

 

[John liem]

Hi all, thanks for all the nice ideas and will give them a try and come back to you.
Again, thanks a million!

 

[John liem]

I got an error message:

Run-time error '2046':
The command or option 'OutputTo' isn't available now

Can someone tell me what it is and how I can solve this? Thanks

 

[IMO]

Send the code that you have in the click event of the button

IMO

 

[John liem]

Private Sub RunReport_Click()
Dim strsql As String
Dim qry1 As QueryDef
Dim fSQL As Boolean
Set db = CurrentDb
Dim strSQLAccess As String
Dim retval As Integer
Dim App As Object

strSQLAccess = GetSetting("RT DB", "Replication", "SQLAccess", False)
If (strSQLAccess = "false") Then fSQL = False Else fSQL = True
DoCmd.Hourglass True
Label147.Caption = "Getting data ..."
Me.Repaint

DoCmd.OutputTo acOutputQuery, "Qy_1", acFormatXLS, "C:\A.xls"

"GOT RUN-TIME ERROR 2046 IN THIS STAGE"

DoCmd.OutputTo acOutputQuery, "Qy_2", acFormatXLS, "C:\B.xls"
DoCmd.OutputTo acOutputQuery, "Qy_3", acFormatXLS, "C:\C.xls"

Label147.Caption = "Creating Report and Graphs..."
Me.Repaint
GetExcel ("C:\A.xls")
GetExcel ("C:\B.xls")
GetExcel ("C:\C.xls")

Set App = GetObject(, "excel.application")

App.Workbooks.Open FileName:="C:\D.xls"
App.Application.run macro:="D.xls!Macro1"

'This macro1 will consolidate the 3 temp Excel files (A, B, C.xls) together

App.UserControl = True
App.Visible = True
Set App = Nothing
Label147.Caption = "Root Caused Report"
Me.Repaint
Beep
DoCmd.Hourglass False
End Sub

 

[IMO]

I'm not too sure but could the error occur because the first 'Output To' command had not completed? If so,

Private Sub RunReport_Click()
Dim strsql As String
Dim qry1 As QueryDef
Dim fSQL As Boolean
Set db = CurrentDb
Dim strSQLAccess As String
Dim retval As Integer
Dim App As Object

strSQLAccess = GetSetting("RT DB", "Replication", "SQLAccess", False)
If (strSQLAccess = "false") Then fSQL = False Else fSQL = True
DoCmd.Hourglass True
Label147.Caption = "Getting data ..."
Me.Repaint

DoCmd.OutputTo acOutputQuery, "Qy_1", acFormatXLS, "C:\A.xls"

"HERE IS WHERE YOU OPEN THE HIDDEN FORM THAT HAS DoCmd.OutputTo acOutputQuery, "Qy_2", acFormatXLS, "C:\B.xls" IN THE ONCLOSE EVENT"


"AND A SECOND FORM HERE WITH THE TIMER SET TO 60 SECONDS THAT HAS DoCmd.OutputTo acOutputQuery, "Qy_3", acFormatXLS, "C:\C.xls" IN THE ONCLOSE EVENT"


Label147.Caption = "Creating Report and Graphs..."
Me.Repaint
GetExcel ("C:\A.xls")
GetExcel ("C:\B.xls")
GetExcel ("C:\C.xls")

Set App = GetObject(, "excel.application")

App.Workbooks.Open FileName:="C:\D.xls"
App.Application.run macro:="D.xls!Macro1"

'This macro1 will consolidate the 3 temp Excel files (A, B, C.xls) together

App.UserControl = True
App.Visible = True
Set App = Nothing
Label147.Caption = "Root Caused Report"
Me.Repaint
Beep
DoCmd.Hourglass False
End Sub


I'm not sure it's the best way to go about this (Probably not) but give it a try. If it works GREAT!!

IMO

 

[John liem]

IMO, I do appreciate your help and I will certainly give a try. I'll let you know about the result.
Thanks a lot.