Trouble with subtotal in form footer

[mthornblad]

Hi

I have a form/subform for entering invoice data. I have a subtotal
text box in the form footer which keeps a running total of the line
items I enter in the subform. Sometimes the subtotal doesn't update
as I enter the line items.

If I click in the subtotal text box in the form footer, the subtotal
displays correctly. I don't know what condition is causing this.
Sometimes it totals correctly and somtimes it remains blank until I
click in the subtotal text box.

I would appreciate any help or suggestions on how I could solve this
problem.

Thanks in advance
Mark

 

[Allen Browne]

There can be a delay before the total gets updated.

If you show the Record Selector at the left of your subform, you will see a
pencil-icon there while the record is dirty (edits that are not yet saved.)
Once the record gets saved, you will probably see that Access updates the
total shortly after that.

Once the record is saved, if the total is not yet updated, you can force it
by programmatically issuing a Recalc, but you should not have to do that.

 

[mthornblad]

There can be a delay before the total gets updated.

If you show the Record Selector at the left of your subform, you will see a
pencil-icon there while the record is dirty (edits that are not yet saved.)
Once the record gets saved, you will probably see that Access updates the
total shortly after that.

Once the record is saved, if the total is not yet updated, you can force it
by programmatically issuing a Recalc, but you should not have to do that.

Thanks Allen

The record selector is showing when the record is "dirty". When the
record is
saved, the subtotal still doesn't update. Most the time that is.
Sometimes
the subtotal works properly. If I am in the middle of entering the
line items
and I click on a tab to go to another program, the subtotal is
calculated.
I don't know what is causing it to work sometimes and not others.

How and where would I force the re-calculation of the subtotal ?

Thanks
Mark

 

[Allen Browne]

One (rather crass) way to force the total to update would be to use the
AfterUpdate event procedure of the form to Recalc:
Private Sub Form_AfterUpdate()
Me.Recalc
End Sub

If you need to do that after deletions as well, use the form's
AfterDelConfirm event.

An alternative would be to leave the text box unbound, and assign a value in
Form_AfterUpdate. This kind of thing:
Me.[Text99] = DSum("Amount", "Table2", "MyID = " & Nz([MyID]))
Be sure to use Form_AfterDelConfirm as well if you do that.

 

[mthornblad]

One (rather crass) way to force the total to update would be to use the
AfterUpdate event procedure of the form to Recalc:
Private Sub Form_AfterUpdate()
Me.Recalc
End Sub

If you need to do that after deletions as well, use the form's
AfterDelConfirm event.

An alternative would be to leave the text box unbound, and assign a value in
Form_AfterUpdate. This kind of thing:
Me.[Text99] = DSum("Amount", "Table2", "MyID = " & Nz([MyID]))
Be sure to use Form_AfterDelConfirm as well if you do that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen

The record selector is showing when the record is "dirty". When the
record is
saved, the subtotal still doesn't update. Most the time that is.
Sometimes
the subtotal works properly. If I am in the middle of entering the
line items
and I click on a tab to go to another program, the subtotal is
calculated.
I don't know what is causing it to work sometimes and not others.

How and where would I force the re-calculation of the subtotal ?

Thanks
Mark

Allen

I added the Me.Recalc to the form update event and that caused another
problem. I have a field (Unit Price) which I Dlookup the price based
on
whether the customer is a wholesale or retail account. I can override
the price if I wish. I had to tab twice to get past this field.

I took out the Recalc code and saved the form and it works perfectly.
I don't know how that solved the problem but it did. I am using
Access 2007 with Windows Vista if that means anything.

Anyway, you solved my problem.

Thank you so much for your help. I greatly appreciate it !

Mark