Solved

Status Bar not always being updated

Posted on 2006-06-11
7
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

729 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