Access form refresh / re-calculation delay

Posted on 2013-05-20
Medium Priority
Last Modified: 2013-05-27
I have an Access database front-end with an SQL Server back-end. The main form consists of several sub-forms (containing simple totals) and various text-boxes, some of which are calculated based on sub-form totals.

It's actually a quoting application, whereby the sub-forms for materials / labour etc. are totalled up to give an overall cost, with additional input fields for discount / markup etc. The main 'quote' record is linked to several other tables for materials used, labour required etc. Normal sort of stuff I guess.

An initial problem meant that all the calculations were not correct if a user modified a field and then pressed print without the record being updated. To resolve this,  I now refresh/requery the form to ensure any field the user is currently in is updated and all calculations are correct (especially the overall quotation value of course). I found I had to add a fixed delay before printing to enable everything to refresh otherwise the printed quote did not correspond to the correct values on-screen. However, on some slower machines this is not long enough  - the delay is already up to several seconds which is getting a bit ridiculous as far as users are concerned.. normally pressing print should at least show a preview in a reasonable time.

Does anyone have any ideas how to resolve this, whether it be a better way of ensuring everything is up-to-date on the form before printing or telling me there's something obvious I've missed in all of this?

Question by:nigelr99
LVL 25

Assisted Solution

chaau earned 450 total points
ID: 39182988
Didn't you need to do Update method, instead of refresh/requery?

Use Update with Force parameter set to TRUE. This will ensure the data is saved to the database
LVL 86

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1050 total points
ID: 39187168
How are you doing the "refresh/requery"? In most cases, if you want to force Access to save the data you just do this:

If Me.Dirty Then Me.Dirty = False

This forces Access to save any changes. Refreshing/requerying can take quite a bit of time, depending on many different factors (network speed, data size, UI complexisty. Saving data is very fast in most cases

Author Closing Comment

ID: 39199385
Sorry for slow reply, didn't have the chance to test for a few days. Taking the 2 replies I had, I realised that I didn't really need to requery as it meant the subforms were also requerying as well which seemed to take the most time.
In the end, I replaced the requery with a simple docmd.Save which seems to have done the trick for the time being.
I've awarded some points to each reply, hopefully the split is agreeable.
Many thanks

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

600 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