MS Access - Calculated Field will not update properly.
Posted on 2006-06-06
I have an update problem on a field in a main form because that field needs 2 other fields to update before it can be refreshed and display the correct answer. Here is the problem
1. I have a main form with 2 sub forms on the main form.
2. The main form is called Company
3. Subform1 lists bonds and their amounts. On the form header I have a field with the following formula in it.
CompanyBondTotal =DSum("[BONDAMOUNT]","bbonds","[Company]=[Forms]![bcompany]![COMPANY] and [Status]=1 ")
This field just sumarizes the bond amounts into a field. This works well.
4. SubForm2 lists in use bonds (encumbered). It also has a field in the header that sums up the encumbered bonds. Its formula is:
All of this works fine to this point. Calculations work well.
5. On the Company Form (Main Form), I have a summary field titled "AmtRemaining" which takes the other 2 fields and subtracts CompanyBondTotal minus EncumberedTotal and places the calculation answer in the field "AmountRemaining". The formula in this field is
Amount Remianing =([bbonds3].[Form]![CompanyBondTotal])-([bbonddeduct].[Form]![EncomberedTotal])
Problem: The sub forms update properly but the "Amt Remaining " field on the main form usually indicates #Error because it updates quicker then the other 2 fields
Question: How can I delay the time for this to update so that the other 2 fields update first?
I have tried me.requery, Me.recalc, Me.refresh and some times these work but ofteen I have to move to the next record and then return to this record to get a correct value.
Any ideas hor to do this another way?
Sorry this is soooo long. I appreciate any advice you can give me.