By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,561 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

Progress Meter does not refresh during series of OpenQueries

P: n/a
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

Dec 24 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Dec 24 '05 #2

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

Dec 24 '05 #3

P: n/a

"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.
Dec 24 '05 #4

P: n/a
"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
Dec 26 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.