Saving calculated control source values to a table

Question

Hi there,

I'm building a database where the some of the values entered on a form are automatically calculated to save time and errors. I've discovered that these values aren't saved to the associated table field.

I know that convention dictates that the data shouldn't be saved and it should be calculated on the fly with a query, but the data I'm saving isn't going to change (it's the number of issues for each subscriber based on "No.issues" * "Start issue").

Aside from the fact that it's wrong, I have seen that it can be done, however it normally applies to older versions of Access and they don't work for 2007. Does anyone have a way that I can save the calculated value to a control source and then have it inserted into the table?

I'd ideally appreciate any suggestions or guidance on updating already entered records as it'll save me from going through 100s and updating them manually.

Many thanks,

Synapsis

Answer 1

Actually, in order to update tables or queries, you must use update queries. I don't use macros, but it's quite simple to execute an Update query using VBA.

I still maintain, though, that you should be doing the calculations in a query, and not storing them in the table.

Answer 2

I tried using the SetValue macro and got an error with it not recognizing the table, which I assume is because macros work with forms and queries only.

I tried using AfterUpdate() with the "Renewal issue" but couldn't work out the code to get it to update.

Currently I have the calculation [Start issue] + ([Number of issues] - 1) in the AfterUpdate() event of another textbox, with the control source of 'Renewal issue' being the corresponding field in the table.

Answer 3

My advice is even if you don't think it's going to change, don't save it. Instead, put the calculations into a query, and use the query wherever you would otherwise have used the table.

If you insist on saving them, whatever approach you've seen for older versions of Access should still work in Access 2007 (or 2010, for that matter). What have you tried, and what happened when you tried?