Prevent interruption of status bar meter

  • Thread starter Thread starter Brian
  • Start date Start date
B

Brian

I initialize the status bar meter, then increment as I loop through data.

'set status bar
txtStatus = SysCmd(acSysCmdInitMeter, "Updating points...", NumberOfThings)
For Each Thing In MyListOfThings
ThisNumber = MyListOfThings.ListPosition
'increment status bar
txtStatus = SysCmd(acSysCmdUpdateMeter, ThisNumber)
'update record
strSQL = "UPDATE MyList SET Whatever = True"
DoCmd.RunSQL strSQL
Next

The status bar text flashes rather than incrementing smoothly. It appears
that the status bar is changing to something else VERY briefly (perhaps
"Running query....) during each RunSQL, then returning to the meter and
moving to the next increment.

Am I incorrect in what I think I am seeing? If so, is there a way to prevent
other status bar text updates when a meter is in progress.
 
Brian said:
The status bar text flashes rather than incrementing smoothly. It appears
that the status bar is changing to something else VERY briefly (perhaps
"Running query....) during each RunSQL, then returning to the meter and
moving to the next increment.
Click to expand...

Ahh, quite likely. Good diagnosis.
Am I incorrect in what I think I am seeing? If so, is there a way to prevent
other status bar text updates when a meter is in progress.
Click to expand...

One possibility is to use your own progress bar meter on a form. One
advantage is that this is much larger and much more noticeable by the
user. So use some rectangle controls on a form. See the Progress
Bars and Microsoft Access page at
http://www.granite.ab.ca/access/progressbar.htm for more info.

Another is to use currentdb.execute.

The problem with DoCmd.RunSQ is that it ignores any errors. Either
of the following will display any error messages received by the
query. If using DAO, use Currentdb.Execute strSQL,dbfailonerror..
For ADO use CurrentProject.Connection.Execute strCommand,
lngRecordsAffected, adCmdText You can then remove the
docmd.setwarnings lines.

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Thank you, Tony.

I will try the custom status bar code, although I use the status bar
extensively on many different forms, so it will take a bit of Find & Replace
to get it all.

One of these days, if & when things slow down enough, I will probably
convert all my DoCmd.RunSQL's to Executes. The MVP's keep sending me back to
that one.
 
Brian said:
One of these days, if & when things slow down enough, I will probably
convert all my DoCmd.RunSQL's to Executes. The MVP's keep sending me back to
that one.
Click to expand...

FWIW one nice thing about Find & Replace is the little button which
allows you to view a continuous form containing all the found objects.
You can then click on the record on the form to go to the line of code
make your change and come back to the list.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Tony Toews said:
FWIW one nice thing about Find & Replace is the little button which
allows you to view a continuous form containing all the found objects.
You can then click on the record on the form to go to the line of code
make your change and come back to the list.
Click to expand...

I decided to blog this.

http://msmvps.com/blogs/access/arch...eature-in-rick-fisher-s-find-amp-replace.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Brian said:
One of these days, if & when things slow down enough, I will probably
convert all my DoCmd.RunSQL's to Executes. The MVP's keep sending me back to
that one.
Click to expand...

BTW just to save some time on this.

Do a find and replace on DoCmd.RunSQL to currentdb.execute. Then
using Ricks tool go to each line and, using paste, add the
,dbfailonerror. Won't take long.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Great idea!

Yes, Find & Replace was the best $35 I ever spent. It has saved me hundreds
of hours of manual searching.

I just used it heavily in converting an Access/MapPoint interface from early
binding (in which Intellisense helped immensely during development) to late
binding (so that users not having MapPoint installed would not receive all
those very-hard-to-correlate early-binding-related anomalies.
 
Actually, I can change it in one place. I actually used a public Sub, like
this:

In various form's modules:

strSQL = "UPDATE abc set def = 123"
RunSQLStatement(strSQL)


In a standalone module:

Public Sub RunSQLStatement(strSQLRun As String)
On Error GoTo ErrorHandler
DoCmd.SetWarnings False
DoCmd.RunSQL strSQLRun
DoCmd.SetWarnings True
End Sub

So, I just get to just replace those three lines in my public sub with one
Execute line. I learn something new every day.
 
Back
Top