By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,044 Members | 1,216 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,044 IT Pros & Developers. It's quick & easy.

Calculate fields on form open

P: 2
I have created a MS Access form that is based on a table. There are some fields that are calculated in the form and populated on the table. Is there a way to recalcualte every record on the table when the form is opened?
Aug 28 '08 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 15k+
P: 31,761
It's a very bad idea to design the table so that it needs to be updated continually.

What you should be looking at is storing the base data in the table, then creating a query based on the table but which also includes the calculations you need.

The form should be driven by the query and not the table.

Does that help?

Welcome to Bytes!
Aug 28 '08 #2

Expert 100+
P: 1,134
What, recalculate every record every time the form is opened?

To answer your question, yes, one way would be to write an update query to perform the calulated updates as you require. Then you run that query from vba in the Form_Load event for your form.

But I agree with Neopa, there is no need to store calculated fields.
Aug 28 '08 #3

Expert 2.5K+
P: 3,532
With the exception of a few situations, calculated values should never be stored in a table, but rather re-calculated as needed. The fact that you want these values to be re-calculated every time the form opens indicated that this is npt one of those rare situations!

It would help us to help you if we had a more detailed explanation of what you're trying to do.

As a simple example, let's day TextboxC = TextboxA + TextboxB

One approach would be to have the Control Source set to the formula. In Design View for your form, select TextboxC then goto Properties - Date and in the Control Source Property enter

= [TextboxA] + [TextboxB]

You would have to do the same thing in order to include TextC in a report, using the same thing as the Control Source of an unbound textbox on the report.

Another approach, probably the better choice, would be to create a query on your table. In truth, because of issues just like this, most forms should be based on queries, even when only a single table is involved. Among other things, you can create a calculated field once, and it will be available to you, freom that pont forward, without having to re-calculate it again. Instead, the query automatically re-calculates it, every time the query runs.

In the Design Grid for the query, in a blank filed, you would enter something like:

CalculatedFieldC: [FieldA] + {FieldB]

With your form based on the query, simply assign the Control Source of TextboxC to the query's CalculatedFieldC. In a report, using this same query, you'd do the same thing.

Linq ;0)>

Sorry for the late post! Having connection problems today!
Aug 28 '08 #4

P: 2
Thanks all for your help. I was able to create an update query that is kicked off by a macro when the form opens. It may not be the best way to do it, but it works.
Aug 29 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.