Solved

# MS Access - Calculated Field will not update properly.

Posted on 2006-06-06
865 Views
Last Modified: 2012-05-05
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

0
Question by:Lou Dufresne
13 Comments

LVL 34

Expert Comment

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

LVL 1

Author Comment

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

LVL 38

Assisted Solution

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

LVL 58

Accepted Solution

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

LVL 58

Expert Comment

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

LVL 38

Expert Comment

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

Me!YourSubform1Control.Form!CompanyBondTotal.Requery
0

LVL 38

Expert Comment

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

LVL 58

Expert Comment

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

LVL 38

Expert Comment

Gotcha.  Thanks for the info.
0

LVL 1

Author Comment

Hi harfang,
I am not sure about this txtTrigger is this just a field that you create?
= DLookup("aField", "aTable", "aCondition" & [txtTrigger]+Null)

Lou
0

LVL 58

Expert Comment

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

LVL 1

Author Comment

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

LVL 58

Expert Comment

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

### Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

#### 761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!