• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Status Bar not always being updated

The following code snippet is supposed execute 4 queries repeatedly and indicate the progress on the status bar. Usually it works, but often, the status bar stops being updated mid-way through the routine, even though the program continues to run and completes successfully:

ws.BeginTrans
         Set Qd1 = CurrentDb.QueryDefs("q475d0")
         Set Qd2 = CurrentDb.QueryDefs("q475d1b")
         Set Qd3 = CurrentDb.QueryDefs("q475_e")
         Set Qd4 = CurrentDb.QueryDefs("q475_f")
         For i = 1 To NumNodes
            Qd1.Execute
            Qd2.Execute
            Qd3.Execute
            Qd4.Execute
            RelaxProgress = i / NumNodes
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))
         Next
     ws.CommitTrans dbForceOSFlush

Why is this happening? Is it something to do with the Transaction? Is there a screen refresh command that I should be using?
0
Milewskp
Asked:
Milewskp
  • 3
  • 2
  • 2
1 Solution
 
prosh0tCommented:
assuming a single query isn't taking too long because of lack of indexe's or something, try throwing in the 'Doevents' command into your loop:

ws.BeginTrans
         Set Qd1 = CurrentDb.QueryDefs("q475d0")
         Set Qd2 = CurrentDb.QueryDefs("q475d1b")
         Set Qd3 = CurrentDb.QueryDefs("q475_e")
         Set Qd4 = CurrentDb.QueryDefs("q475_f")
         For i = 1 To NumNodes
            DoEvents
            Qd1.Execute
            Qd2.Execute
            Qd3.Execute
            Qd4.Execute
            RelaxProgress = i / NumNodes
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))
         Next
     ws.CommitTrans dbForceOSFlush

0
 
rockiroadsCommented:
Like prosh0t says, it could be down to the timing of your queries

to check the time it takes to run the queries, u can add some diagnostics. Use debug.print so it dumps to the immediate window
e.g.
            Qd1.Execute
            Debug.print "q1",i,Now(),RelaxProgress
            Qd2.Execute
            Debug.print "q2",i,Now(),RelaxProgress
            Qd3.Execute
            Debug.print "q3",i,Now(),RelaxProgress
            Qd4.Execute
            Debug.print "q4",i,Now(),RelaxProgress



Have u thought perhaps of placing your update of the status bar after the query execute?
e.g.

            Qd1.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))
            Qd2.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))
            Qd3.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))
            Qd4.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))


or if thats too much

            Qd1.Execute
            Qd2.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))
            Qd3.Execute
            Qd4.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Progress: " & Format(RelaxProgress, "0.0%"))
0
 
rockiroadsCommented:
regarding this, if u do go for this approach, because u can't improve the speed (u should take the advice from prosh0t and check indexes)

            Qd1.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Running " & i & " of " & NumNodes & " for Q1: Progress: " & Format(RelaxProgress, "0.0%"))
            Qd2.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Running " & i & " of " & NumNodes & " for Q2: Progress: " & Format(RelaxProgress, "0.0%"))            Qd3.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Running " & i & " of " & NumNodes & " for Q3: Progress: " & Format(RelaxProgress, "0.0%"))            Qd4.Execute
            Answer = Application.SysCmd(acSysCmdSetStatus, "Running " & i & " of " & NumNodes & " for Q4: Progress: " & Format(RelaxProgress, "0.0%"))

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MilewskpAuthor Commented:
Adding DoEvents solved the problem. Thanks prosh0t! But I'm curious as to what is going on...

Does this mean that the Application.SysCmd statement triggers an event (I thought it was just a statement that was executed like any other and then control passed to the next statement in the code)?

I've used this statement many times before and never had a problem. What is it about this code snippet that requires DoEvents?

0
 
rockiroadsCommented:
well done prosh0t

perhaps the system command is a seperate process, so doing DoEvents is giving the CPU time to work on that process?

0
 
prosh0tCommented:
thanks :)  yeah.. it's just giving cpu time to process the form events - ie the status bar.  if you're in a loop that has a lot of iterations, none of the form events will get processed until it's out.  All it's doing is processing events; like if you move the form around, or minimize, maximize (if there's no dovents in tehre the form goes all white).    So a DoEvents is necessary.  I use it all the time in VB.  In .NET it's system.windows.forms.Application.Doevents i believe.

0
 
MilewskpAuthor Commented:
Thanks prosh0t, full points and an A++ go to you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now