timer event

[bobh]

Hi All,

This should be simple enough but I'm not getting it working for some
reason so, what's the code and in what events

In AccessXP on a form when a user clicks on a button(btnGetData) I
want a labelbox's text(lblWait) to bold/unbold every second until the
form closes.
The vba behind the btnGetData button gets data and updates a table and
then closes its self. The data it gets and updates could take several
mintues so I want the let the user know something is happening so they
don't think the process is hung and start clicking on stuff or start
pounding on the keyboard.
thanks
bobh.

[Rich P]

Hi Bob,

Your operation here requires multi threading which requires an object
oriented programmming environment which Access does not support. So it
is not possible to perform this operation within Access. The workaround
would be to create a user control in VB.Net (make it com based) and then
apply that custom user control to your Access application. I had to do
the very same thing for the very same purpose. The code for creating
the user control was a snap in VB.Net (either 2003 or 2005). It was a
label (actually a series of labels) that behaved like a progress meter
except that all it did was grow and shrink and change colors while the
process was underway. I would invoke the control at the beginning of
the procedure in Access (make it visible) and at the end of the
procedure I turn off the user control - make it not visible. The
control is actually constantly running (cpu based) so you just make it
visible = true and visible = false. So the control is independent of
the procedure - of Access - on a totally different thread - it runs
asynchronously to your procedure which is what you want.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On 26 Apr 2007 21:53:13 GMT, Rich P <rp*****@aol.comwrote:

Here is a poor-man's version:
In your code, try to structure things so they happen a chunk at a
time. Then use SysCmd acSysCmdInitMeter and its cousins to use the
built-in progress bar. Something like:
Function MyLongRunningFunction()
dim rs as Recordset
SysCmd acSysCmdInitMeter, "Processing Data", 100
set rs = GetData()
SysCmd acSysCmdUpdateMeter 10
ProcessChunk(1, rs)
SysCmd acSysCmdUpdateMeter 30
ProcessChunk(2, rs)
SysCmd acSysCmdUpdateMeter 50
ProcessChunk(3, rs)
SysCmd acSysCmdUpdateMeter 70
ProcessChunk(4, rs)
SysCmd acSysCmdUpdateMeter 90
FinalProcessing()
SysCmd acSysCmdUpdateMeter 100
rs.Close
SysCmd acSysCmdRemoveMeter
End Function

ProcessChunk would process a fourth of the data passed in.

-Tom.

>Hi Bob,

Your operation here requires multi threading which requires an object
oriented programmming environment which Access does not support. So it
is not possible to perform this operation within Access. The workaround
would be to create a user control in VB.Net (make it com based) and then
apply that custom user control to your Access application. I had to do
the very same thing for the very same purpose. The code for creating
the user control was a snap in VB.Net (either 2003 or 2005). It was a
label (actually a series of labels) that behaved like a progress meter
except that all it did was grow and shrink and change colors while the
process was underway. I would invoke the control at the beginning of
the procedure in Access (make it visible) and at the end of the
procedure I turn off the user control - make it not visible. The
control is actually constantly running (cpu based) so you just make it
visible = true and visible = false. So the control is independent of
the procedure - of Access - on a totally different thread - it runs
asynchronously to your procedure which is what you want.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

The only downside with this solution is if the procedure is processing
one long running query - which I believe is the case. Syscmd would only
get called once. I guess if the query could get broken up into chunks,
then yes, syscmd would be an easier workaround. But if the query must
remain intact, then the workaround would be to use the asynchronously
running user control. Believe me, I have been there.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Tom van Stiphout]

On 27 Apr 2007 01:44:03 GMT, Rich P <rp*****@aol.comwrote:

Agreed. I just thought your "write a dotnet control" was a bit
extreme for the average Access developer. Especially since the
progress bar is really just a convenience. A modal "Please Wait - This
can take a few minutes" dialog and an Hourglass should typically
suffice.

If there is a long-running query, it can sometimes be hacked up into
multiple chunks like this air code:
set rs=db.OpenRecordset("select Min(myPK), Max(myPK) from myTable")
lngMin = rs(0): lngMax = rs(1)
for i = 0 to 9
update sometable .....
where PK_Value between i*(lngMax-lngMin) and (i+1)*(lngMax-lngMin)
SysCmd ...
next i

-Tom.

>The only downside with this solution is if the procedure is processing
one long running query - which I believe is the case. Syscmd would only
get called once. I guess if the query could get broken up into chunks,
then yes, syscmd would be an easier workaround. But if the query must
remain intact, then the workaround would be to use the asynchronously
running user control. Believe me, I have been there.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Actually, the real fix would be -- if a query takes more than a few
seconds to run (or even a few minutes) - then it is time re-evaluate if
the correct tool is being used or if the query needs to be revamped.
More times than not, I have seen queries that could be dramatically
improved by removing unnecessary joins, or in some cases it is just time
to step up to sql server.

As for the .Net solution, yes, a tad overkill. It was one of my first
efforts with .Net back about 5 years ago. Users used to wonder if the
program crashed whenever it took more than a few minutes to run a query.
I just thought I would provide some visual effects. I did something
similar in VB6 before that where I created a simple form exe with a
timer that would do the same thing as the .Net app except that it was a
standalone program that I would call with Shell and then close with API
code. That was asynchronous, but it wasn't really part of the Access
app where the custom user control was part of the application - way more
control.

Going back to breaking up the query goes back to your solution of using
chunks "so to speak" and use syscmd as each portion of the procedure is
processed to display some info in the statusbar.

Rich

*** Sent via Developersdex http://www.developersdex.com ***