Progress Meter does not refresh during series of OpenQueries

[bfulford]

I have a macro that needs to have a progress meter displayed since it
is long running. I moved the Macro's instructions to a table and pulled
those records into a recordset that is looped through.
PROBLEM -- The form, with a progress meter and labels to inform the
user as to which step of a series of instructions is being done and the
overall progress, seems to lock from repainting after the first
docmd.openquery because the form is never updated until ALL the
openqueries are done.

Any advice out there?

DoCmd.OpenForm "PROGRESS"
Dim frm As Form
Set frm = Forms("PROGRESS")
Dim ctrl As Control
Set ctrl = frm.Controls("lblMessage")
ctrl.Caption = "PLEASE WAIT...." & vbCrLf & "Starting Audit"

Dim lblHdr As Control
Set lblHdr = frm.Controls("lblHeading")

Dim lblTime As Control
Set lblTime = frm.Controls("lblTime")

DoEvents
Set conn = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.Open "select * from AuditOrders", conn, adOpenDynamic,
adLockReadOnly

Dim count As Integer
Dim currCount As Integer
count = 0
currCount = 1

If Not rs.EOF Then
Do Until rs.EOF
count = count + 1
rs.MoveNext
Loop
End If

If count <= 0 Then Exit Function
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.SetWarnings False

updatemtr 0, count
frm.Repaint

rs.MoveFirst

Do Until rs.EOF
DoEvents
recordval = rs!Value
recordType = rs!TYPE
recordHeading = rs!heading
active = rs!active

lblHdr.Caption = recordHeading
ctrl.Caption = "Executing " & recordval
updatemtr currCount, count
DBEngine.Idle dbRefreshCache
frm.Repaint

Select Case UCase(recordType)
Case "QUERY"
DoCmd.OpenQuery recordval
DoCmd.Close acQuery, recordval
frm.Repaint
Case "REPORT"
'DoCmd.OpenReport recordVal, acViewPreview, , ,
acWindowNormal
Case "CODE"
'Call Val(recordVal)
End Select

Sleep (100)
currCount = currCount + 1
rs.MoveNext
Loop

Exit_Function:
DoCmd.Hourglass False
DoCmd.Echo True
DoCmd.SetWarnings True
MsgBox "AUDIT DONE.", vbOKOnly, "AUDIT COMPLETE"
DoCmd.Close acForm, "PROGRESS"
Exit Function

errorhandler:
If ERR.Description <> "" Then MsgBox "AccuReg Error" & vbCrLf &
ERR.Description, vbOKOnly, "ERROR"
Resume Exit_Function

[RoyVidar]

bfulford wrote in message
<11*********************@o13g2000cwo.googlegroups. com> :

I have a macro that needs to have a progress meter displayed since it
is long running. I moved the Macro's instructions to a table and pulled
those records into a recordset that is looped through.
PROBLEM -- The form, with a progress meter and labels to inform the
user as to which step of a series of instructions is being done and the
overall progress, seems to lock from repainting after the first
docmd.openquery because the form is never updated until ALL the
openqueries are done.

Any advice out there?

DoCmd.OpenForm "PROGRESS"
Dim frm As Form
Set frm = Forms("PROGRESS")
Dim ctrl As Control
Set ctrl = frm.Controls("lblMessage")
ctrl.Caption = "PLEASE WAIT...." & vbCrLf & "Starting Audit"

Dim lblHdr As Control
Set lblHdr = frm.Controls("lblHeading")

Dim lblTime As Control
Set lblTime = frm.Controls("lblTime")

DoEvents
Set conn = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.Open "select * from AuditOrders", conn, adOpenDynamic,
adLockReadOnly

Dim count As Integer
Dim currCount As Integer
count = 0
currCount = 1

If Not rs.EOF Then
Do Until rs.EOF
count = count + 1
rs.MoveNext
Loop
End If

If count <= 0 Then Exit Function
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.SetWarnings False

updatemtr 0, count
frm.Repaint

rs.MoveFirst

Do Until rs.EOF
DoEvents
recordval = rs!Value
recordType = rs!TYPE
recordHeading = rs!heading
active = rs!active

lblHdr.Caption = recordHeading
ctrl.Caption = "Executing " & recordval
updatemtr currCount, count
DBEngine.Idle dbRefreshCache
frm.Repaint

Select Case UCase(recordType)
Case "QUERY"
DoCmd.OpenQuery recordval
DoCmd.Close acQuery, recordval
frm.Repaint
Case "REPORT"
'DoCmd.OpenReport recordVal, acViewPreview, , ,
acWindowNormal
Case "CODE"
'Call Val(recordVal)
End Select

Sleep (100)
currCount = currCount + 1
rs.MoveNext
Loop

Exit_Function:
DoCmd.Hourglass False
DoCmd.Echo True
DoCmd.SetWarnings True
MsgBox "AUDIT DONE.", vbOKOnly, "AUDIT COMPLETE"
DoCmd.Close acForm, "PROGRESS"
Exit Function

errorhandler:
If ERR.Description <> "" Then MsgBox "AccuReg Error" & vbCrLf &
ERR.Description, vbOKOnly, "ERROR"
Resume Exit_Function

Try the following

In the loop, replace frm.Repaint with DoEvents. It should only be
necessary after altering the captions.

--
Roy-Vidar

[bfulford]

I have a doevents right after the do until... does it really matter
where in the loop I place it?

[Randy Harris]

"bfulford" <br***********@gmail.com> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

I have a macro that needs to have a progress meter displayed since it
is long running. I moved the Macro's instructions to a table and pulled
those records into a recordset that is looped through.
PROBLEM -- The form, with a progress meter and labels to inform the
user as to which step of a series of instructions is being done and the
overall progress, seems to lock from repainting after the first
docmd.openquery because the form is never updated until ALL the
openqueries are done.

Any advice out there?

DoCmd.OpenForm "PROGRESS"
Dim frm As Form
Set frm = Forms("PROGRESS")
Dim ctrl As Control
Set ctrl = frm.Controls("lblMessage")
ctrl.Caption = "PLEASE WAIT...." & vbCrLf & "Starting Audit"

Dim lblHdr As Control
Set lblHdr = frm.Controls("lblHeading")

Dim lblTime As Control
Set lblTime = frm.Controls("lblTime")

DoEvents
Set conn = CurrentProject.Connection
Dim rs As New ADODB.Recordset
rs.Open "select * from AuditOrders", conn, adOpenDynamic,
adLockReadOnly

Dim count As Integer
Dim currCount As Integer
count = 0
currCount = 1

If Not rs.EOF Then
Do Until rs.EOF
count = count + 1
rs.MoveNext
Loop
End If

If count <= 0 Then Exit Function
DoCmd.Hourglass True
DoCmd.Echo False
DoCmd.SetWarnings False

updatemtr 0, count
frm.Repaint

rs.MoveFirst

Do Until rs.EOF
DoEvents
recordval = rs!Value
recordType = rs!TYPE
recordHeading = rs!heading
active = rs!active

lblHdr.Caption = recordHeading
ctrl.Caption = "Executing " & recordval
updatemtr currCount, count
DBEngine.Idle dbRefreshCache
frm.Repaint

Select Case UCase(recordType)
Case "QUERY"
DoCmd.OpenQuery recordval
DoCmd.Close acQuery, recordval
frm.Repaint
Case "REPORT"
'DoCmd.OpenReport recordVal, acViewPreview, , ,
acWindowNormal
Case "CODE"
'Call Val(recordVal)
End Select

Sleep (100)
currCount = currCount + 1
rs.MoveNext
Loop

Exit_Function:
DoCmd.Hourglass False
DoCmd.Echo True
DoCmd.SetWarnings True
MsgBox "AUDIT DONE.", vbOKOnly, "AUDIT COMPLETE"
DoCmd.Close acForm, "PROGRESS"
Exit Function

errorhandler:
If ERR.Description <> "" Then MsgBox "AccuReg Error" & vbCrLf &
ERR.Description, vbOKOnly, "ERROR"
Resume Exit_Function

What is updatemtr?

--
Randy Harris
tech at promail dot com
I'm pretty sure I know everything that I can remember.

[RoyVidar]

"bfulford" <br***********@gmail.com> wrote in
news:11**********************@g47g2000cwa.googlegr oups.com:

I have a doevents right after the do until... does it really matter
where in the loop I place it?

I really don't know, I've just always done the assigning to the form
controls, then used DoEvents immediately after, and it has worked. But
since no one else has responded, a couple of more thoughts, though I
haven't the foggiest whether they address the immediate issue ...

I would "second" Randy Harris question though, what is updatemtr, and
does it contain anything that could prevent the form from updating?

Also, there are some other things in your code that could provide
anomalities - your usage of reserved words, for instance Count, Value and
Type, which are properties of lots of objects, see (watch for linebreaks
in the link)
http://support.microsoft.com/default...b;en-us;286335

But I don't know whether this has anything to do with the form not
updating.

You have also declared a variable named lblTime as Control, and then
assigned to it the form control "lblTime" - at least when working in the
form class module, this might provide anomalities.

Also, why do you make it sleep? If this is needed, I'd try to stuff a
DoEvents between updating the form controls and the Sleep.

If no one else responds, I'd suggest to simplify the routine (comment out
larger parts of it) and see at which time/alteration you are able to get
the updating of the form to work, then see what is the cause, and see if
you can work around it.

One suggestion that will probably save some time for you at a later time
(which I'm "rude" enough to suggest;-), is to start using a naming
convention that will prevent you from using reserved words when designing
your dbs.

--
Roy-Vidar