Solved

Status Bar not always being updated

Posted on 2006-06-11
7
355 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
ID: 16882325
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
ID: 16883264
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
ID: 16883274
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!

 
LVL 1

Author Comment

by:Milewskp
ID: 16889013
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
ID: 16889638
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
ID: 16890428
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
ID: 16892923
Thanks prosh0t, full points and an A++ go to you.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

679 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