Solved

Status Bar not always being updated

Posted on 2006-06-11
7
348 Views
Last Modified: 2008-02-01
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
Comment
Question by:Milewskp
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Accepted Solution

by:
prosh0t earned 500 total points
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:Milewskp
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
 
LVL 7

Expert Comment

by:prosh0t
Comment Utility
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
 
LVL 1

Author Comment

by:Milewskp
Comment Utility
Thanks prosh0t, full points and an A++ go to you.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now