# MS Access - Calculated Field will not update properly.

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:
EncumberedTotal =IIf(nz(Sum([amount]))="",0,Sum([amount]))

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.

Lou

LVL 1
###### Who is Participating?

Commented:
Hello Ldufresne19

Your second expression makes sense and will update correctly. It can be simplified to this:

=Nz(Sum([amount]),0)

But that is irrelevant. Whenver anything affecting that calculation occurs, the field will be recalculated, perhaps after a short (but visible) delay.

However, the first boils down to this:

=DSum("constant","constant","constant")

Basically, Access has no clue as to when it needs to recalculate this control. It's totally unrelated to the  rest of the form. If at all possible – i.e. if the subform does in fact display the table "bbunds" – try to replace it with:

=Sum(IIf(Status=1, BONDAMOUNT, 0))

If that is not possible, try to move the expression to the main form, and use perhaps:

=DSum("BONDAMOUNT","bbonds","Company='" & [COMPANY] & "' and Status=1")

This would at least link the expression to the field COMPANY and thus enter the control in the chain of dependencies.

Finally, a slight simplification of the total can help (you do not need the Form object in this case)

=[bbonds3]![CompanyBondTotal]-[bbonddeduct]![EncomberedTotal]

Good luck!
(°v°)
0

Commented:
When are you populating this field? What event?
J
0

IT Business Analyst CSM / Project ManagerAuthor Commented:
The "AmtRemaining" field is populated when a save button is clicked on the main form. It includes me.requery as the last statement after all queries run to update any tables that are used.

Lou
0

Commented:
Amount Remianing   vs AmtRemaining???? a typo?

Try this :
Me!YourSubform1Control.Requery
Me!YourSubform2Control.Requery
Amount Remianing   =([bbonds3].[Form]![CompanyBondTotal])-([bbonddeduct].[Form]![EncomberedTotal])

If above doesn't show amount remaiing, then add the folowing line at the end:
Me.Requery
0

Commented:
I read the comments just above and would like to add that a form with subforms should not use requery anywhere, if it can be avoided. Me.requery or Me.subform.requery throws the entire update sequence out of  whack and very often forces a full refresh of all controls.

Try to set up your expressions in such a way that Access knows what to update and when.

Cheers!
(°v°)
0

Commented:
Hi Harfang,
Good point.
How about if I just requeried the control that is being recalc:

Me!YourSubform1Control.Form!CompanyBondTotal.Requery
0

Commented:
oops, should have used:
Me!YourSubform1Control.Form!YourSubForm1!CompanyBondTotal.Requery
0

Commented:
If needed, yes, of course. However, information on a form are normally linked in some ways, and it's best to let Access know about these links. For example, if you want a DLookup to recalculate whenever the control txtTrigger changes, you are better off adding a dummy link:

= DLookup("aField", "aTable", "aCondition" & [txtTrigger]+Null)

The information will never be used, but Access won't know that and thus automatically recalculate just that single expression.

Cheers!
(°v°)
0

Commented:
Gotcha.  Thanks for the info.
0

IT Business Analyst CSM / Project ManagerAuthor Commented:
Hi harfang,
= DLookup("aField", "aTable", "aCondition" & [txtTrigger]+Null)

Lou
0

Commented:
Lou,

This was meant as a pure example, not really related to your question. I was trying to show that, although requerying  a single contol usually works as expected, you very often have an alternative. That alternative is to make the chain of dependencies explcit for Access.

Full example: an unbound form with two controls, txtPrice and txtCount. You can type a price in the first, the second will display the number of products that are more expensive. This expression could be:

=DCount('*', 'Products', 'UnitPrice>Forms!frmTestForm!txtPrice')

This function uses three string constants. Access won't know that it depends on the first field, and will not recalculate automatically. You would need to press F9 in txtCount to get a new count, or add an After Update event hander containing txtCount.Requery.

A better expression would be the following, with the reference to the field outside of the quotes:

=DCount('*', 'Products', 'UnitPrice>' & Nz([txtPrice],'Null'))

Now, Access will detect the dependency and recalculate txtCount each time the value in txtPrice changes, for any cause.

This is better than using txtCount.Requery in the AfterUpdate event because: 1) it works even if the event is not fired (e.g. value changed through code) and 2) it is never forced and occurs only when the system becomes idle (there is a slight delay). If the value changes rapidly like when surfing through records, the expression is not calculated each time.

Since it is desirable to use Access' recalculate dependencies, what to do if the link has become indirect? For example, let's imagine this query, called qtotCountEm, with:

SELECT Count(*) AS Nb FROM Products
WHERE UnitPrice>Forms!frmTestForm!txtPrice;

This is rather artificial, of course. The form could use:

=DLookup('Nb','qtotCountEm')

But again, the field no longer recalculates. We know that there is an indirect link, but Access doesn't. How can we fake this dependeny? By using a dummy argument, one that will never be used.

For example, DLookup accepts a third argument, that can be left blank or set to an empty string. Thus:

=DLookup('Nb','qtotCountEm',[txtPrice]+Null)

Again, txtCount will be made dependant of txtPrice, and will recalculate automatically, although txtPrice is in fact never used.

That was the point, in more word.

End of the lecture, thank you for your patience (^v^)

Going back to your question, any progress? Have you tried some of the suggestions?

(°v°)
0

IT Business Analyst CSM / Project ManagerAuthor Commented:
harfang,

Thank you for the information it is what I needed at this point in my MS Access development.  It will be very helpful down the line.  I did try the suggestions that you made and those that puppydogbuddy made and now my problem is solved. I have made those changes in my code and removed all me.requery statements that were not needed in my form code.

Thanks again everyone for the assistance.

Lou
0

Commented:
Lou,

I'm very glad to read that. I felt I had overdone it somewhat, but if it was useful, then all is well.
Thanks, and success in your "Access development"

(°v°)
0
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.