Solved

Status Bar not always being updated

Posted on 2006-06-11
7
351 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

785 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