Hi all, I am using Access 2002-2003 file format for this database
Components:
1 Form with [name], [start date], [end date]
1 Main query that uses the form as criteria
22 nested queries that are inside the main query
1 Loading Form that appears when the query is running
1 Report that has tons of calculations of its own
Problems:
Show on the loading form the queries that are running using VBA
Add a cancel button to dump anything it has processed and return to the main form.
Resolution so far:
I have read multiple posts regarding solutions to this and I have found a module that pauses the queries and displays the name of the query.
Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
Dim PauseTime As Variant, Start As Variant
PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop
Exit_Pause:
Exit Function
Err_Pause:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Pause
End Function
Private Sub Form_Open(Cancel As Integer)
'Loading Parameters
'Me.Label1.Caption = "Step 1"
'Pause (1)
'RunQuery1
'Me.Label1.Caption = "Step 2"
'Pause (1)
'RunQuery2
'Me.Label1.Caption = "Step 3"
'Pause (1)
'RunQuery3
End Sub
So far with all of this I can do everything except the Pause function errors out and says Compile Error: Sub or Function not defined. Also, I can figure out how to run the queries I know the above code isn't using the correct syntax to run queries but how can I pass the results of all the subqueries to a report after I run them on the loading form? Finally, Performance wise, this query takes about a minute or two to process so I am hoping to maybe increase the performance with what ever solution I am provided.
Thank you all!
Components:
1 Form with [name], [start date], [end date]
1 Main query that uses the form as criteria
22 nested queries that are inside the main query
1 Loading Form that appears when the query is running
1 Report that has tons of calculations of its own
Problems:
Show on the loading form the queries that are running using VBA
Add a cancel button to dump anything it has processed and return to the main form.
Resolution so far:
I have read multiple posts regarding solutions to this and I have found a module that pauses the queries and displays the name of the query.
Public Function Pause(NumberOfSeconds As Variant)
On Error GoTo Err_Pause
Dim PauseTime As Variant, Start As Variant
PauseTime = NumberOfSeconds
Start = Timer
Do While Timer < Start + PauseTime
DoEvents
Loop
Exit_Pause:
Exit Function
Err_Pause:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Pause
End Function
Private Sub Form_Open(Cancel As Integer)
'Loading Parameters
'Me.Label1.Caption = "Step 1"
'Pause (1)
'RunQuery1
'Me.Label1.Caption = "Step 2"
'Pause (1)
'RunQuery2
'Me.Label1.Caption = "Step 3"
'Pause (1)
'RunQuery3
End Sub
So far with all of this I can do everything except the Pause function errors out and says Compile Error: Sub or Function not defined. Also, I can figure out how to run the queries I know the above code isn't using the correct syntax to run queries but how can I pass the results of all the subqueries to a report after I run them on the loading form? Finally, Performance wise, this query takes about a minute or two to process so I am hoping to maybe increase the performance with what ever solution I am provided.
Thank you all!