Testing performance on a form

[Ray C]

I have a form with a lot of calculated fields. The problem is that when I put
the data file on the server and the front end on my machine, these calculated
fields take about a second or two to calculate and the value appear. Is there
any way I can have the form calculate the amount of time it takes for all the
values to appear.

I tried putting code in some of the event handlers (open, load), but the
time appears too fast...even before the calculated fields complete. My client
wants this information to appear on the form. Something like an
OnLastCalculatedFieldFinishes event.

Thanks !

 

[Jeanette Cunningham]

Hi Ray,
Access gives a low priority to updating calculated fields.
You can try to do the calculation using vba code and assigning the value to
the particular control (call this code from the form's load event) to see
how much faster this is over waiting for Access to get round to updating the
calculated fields.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ray C]

Is using VBA code usually faster? Is it known to always be faster? I ask
because I don't want to code all my calculated fields only to find out at the
end that it isn't faster. I have close to 80 calculated fields on the form.

Thanks !

 

[Jeanette Cunningham]

80 calculated fields sounds a bit excessive.
Without knowing anything about your database, I suggest that you have a
closer look at this form. See if you can you build a query that will do a
lot of these calculations instead of doing them on the form after it has
loaded. Depending on the circumstances, you could put the query into a
separate subform on the main form.



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Ray C]

Thanks Jeanette, but I already tried the query way and it also took the query
too long to display because the query caused too much network traffic.

My client wanted me to convert an Excel spreadsheet into an Access form.
Each line has only two data fields, the rest of the fields in the same line
are all calculated based on these two fields. The formulas are very complex.
When I placed the formulas in the form, the form opened much faster because
the calculations are done on the local machine. But the problem is that it
takes about 3 or 4 seconds for these values to appear. This is much faster
than the query way because we only need to read the two values from the
database. It's very strange. But the solution works fine for my client. It's
just that now he's asking me to put a timer on the form that indicates the
amount of time the form takes to caluclate. There is no event for this to
handle when it finishes.

 

[Jeanette Cunningham]

That is correct. You need some way for the client to indicate the time when
the last calculation is finished - perhaps a button to click when he sees
that all calculations have finished.

One thing comes to mind. There is a well known psychological effect of
putting a progress bar on a form - the progress bar takes the client's mind
away from how long the process is taking and makes the process seen
quicker - I don't know if this your client's problem, but thought I'd
mention it.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia