update field calculations

Topic

New Reply

Ive inherited microsoft access databases to maintain and support

Ive limited microsoft access knowledge.

The databases were in MS-Access XP and are upgraded to Access 2007.

With the 2007 upgrade, functionality related to exporting data into an excel spreadsheet had to be amended due to office 2007 com add-ins code. The fix was to write new vba code within the forms and save the forms.
This now works successfully again.

On the database forms, data is entered into textbox fields, and related textbox fields automatically calcuate and display the updated amounts.
The issue reported is these textbox fields no longer calculate and display the updated amounts.

If I goto the back databases in XP these forms and fields work.
However in 2007 these fields no longer update.

Ive done a brief compare of the fields and their properties between XP and 2007. The result is the same properties. eg the field propertie Event on click – is the same on both

pls see screen capture.

Is there anything I may have done which accidentally “broke” these fields?
Any place I should start looking?

many thanks

diana

Reply | Quote

Replies

The first question when something does not work in Access 2007 is: Have you created a Trusted Location? Because you have VBA code running, I imagine you have.

There are three ways calculated controls on a form could work:

[*]Updated via code using an After Update Event.[*]Bound to a field calculated in a query[*]The control source is a formula that refers to other controls on the form.

It looks like you are using the third method. Is that right?

Reply | Quote

Unfortunately on a form this complex and not knowing that underlying data source makes it virtually impossible to make any educated guesses as to what might be wrong. A couple of observations – chances are the calculation of updated values probably occurs on the After Update event for the specific control, and the VBA that does that is likely where the issue is. Also, it is important to know which controls/fields are not updating, and which control should make that happen. Finally, make sure that SP2 of Office has been installed. Otherwise the export to Excel will still likely be problematic. Sorry I can’t be more specific about what might be the issue.

Reply | Quote

Thanks John and Wendall

John – yes its the 3rd option which is – The control source is a formula that refers to other controls on the form.

Wendall – yes your right the forms are very complex. We have Office2007 SP2. The excel intergration is now working fine.

in regards to the calculations – Ive worked it out & its now sorted.

The trigger is to add data into the field “Adjustment Date”.
Once a date is entered if you edit the field “Amount for period”
The Purchaser amount is updated and displayed automatically.

see screen capture

It was a case of user error, rather than being code related *phew*

many thanks for your help again

diana

Reply | Quote