"Loading" indicator while running a query?

[glgcag]

I am loading a form and then calling the "click" event on my form that runs a large query. During the query I'd like to have a field on the form redisplay "Loading ." every second while adding another " ." as each second passes. I have experimented with DoEvents but I'm sure I'm not doing it correctly because it's not running. Here's is the code that is running:

Dim sSQL as string

sSQL = "Select . . . ."

docmd.setwarnings False
docmd.runsql(sSQL)
docmd.setwarnings True

docmd.close acform, me.name

Then, I assume I need to create code in the Timer event of the form that every 1 second updates the text box on the form, but how do I do this at the same time as the query is running?

Any help/suggestions are appreciated!

 

[PHV]

Me.TimerInterval = 1000
DoCmd.RunSQL sSQL
Me.TimerInterval = 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[glgcag]

But I already have the form closing after the docmd.runsql(sSQL) statement so it kills all timer functionality of the form when it closes.

My question is how do I get the Loading text box to display WHILE the sSQL query is running?

 

[PHV]

Doesn't Me.TimerInterval = 1000 trigger the Timer event procedure ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[glgcag]

PHV,

Here's all the code I am running.
Private Sub cmdRun_Click()
Dim sSQL As String


sSQL = "INSERT INTO tRMBAProjects . . . . . . "

DoCmd.SetWarnings False
DoCmd.RunSQL (sSQL)
DoCmd.SetWarnings True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()

Me.TimerInterval = 1000
Me.cmdRun.Tag = ""


End Sub

Private Sub Form_Timer()
Dim sLoading As String

If Me.cmdRun.Tag = "" Then
sLoading = "Loading ."
Me.txtLoadingMeter = sLoading
Call cmdRun_Click
Me.cmdRun.Tag = "1"
Else
Me.txtLoadingMeter = sLoading & " ."
End If

End Sub

The code is running fine up through doing the query and displaying "Loading ." but instead of continuing to add periods to the "Loading ." string, it just hangs while the query runs. Then it it errors out at the "Me.cmdRun.Tag = "1"" because the form has closed. How do I get the code execution to continue during the query running?

 

[PHV]

And what about this ?
Private Sub cmdRun_Click()
Dim sSQL As String
sSQL = "INSERT INTO tRMBAProjects . . . . . . "
DoCmd.SetWarnings False
Me!txtLoadingMeter = "Loading"
Me.TimerInterval = 1000
DoCmd.RunSQL (sSQL)
Me.TimerInterval = 0
DoCmd.SetWarnings True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
End Sub

Private Sub Form_Timer()
Me!txtLoadingMeter = Me!txtLoadingMeter & " ."
DoEvents
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244