Get current balance?

[Gasman]

Hi everyone,

I have a form that holds amount for a record and I used to enter the balance as calculated from an Excel sheet I also maintain.

This form was really just to create emails to advise caseworkers and my boss.

However I have now amended the amounts to be negative or positive depending on the transaction type and want the form to calculate the balance automatically. The Balance is stored in the record as well.

The following code works great when adding a record, but if I have a need to go back and edit the amount, the balance is not changed on the form after I leave the Amount control.?

Private Sub Amount_LostFocus()
If Me.TranType = "Payment" And Me.Amount > 0 Then
    Me.Amount = Me.Amount * -1
End If
Me.Balance = DSum("[Amount]", "Emails", "[CMS]=" & Me.CMS)
If Me.NewRecord Then
    Me.Balance = Me.Balance + Me.Amount
End If

End Sub

I've tried a Me.Refresh and Me.Balance.Requery which did not work.
I added Me.Dirty test to the Me.NewRecord test as I believe it is because the record has not been saved and Dsum does not pick the new amount up.?
What do I need to add to be able to just modify amount and immediately see the correct value in the Balance field please?

TIA

 

[Gasman]

I appear to have it working with this code

Private Sub Amount_LostFocus()
If Me.TranType = "Payment" And Me.Amount > 0 Then
    Me.Amount = Me.Amount * -1
End If
Me.Balance = DSum("[Amount]", "Emails", "[CMS]=" & Me.CMS & " AND ID < " & Me.ID)
If Me.NewRecord Or Me.Dirty Then
    Me.Balance = Me.Balance + Me.Amount
End If

End Sub

Is there a better way of doing it?

 

[smig]

Do you store the calculated field?

Sent from my m2 note using Tapatalk

 

[Gasman]

Yes,
It started life not being calculated. Only just got around to that.
The data goes in an email so the caseworker knows how much funds we have for a client.
I have the balance in an Excel sheet I maintain, but thought it would be nice to just be able to calculate it as I add new records. However there is a need to go back an perhaps amend an amount, so the balance needs to be recalculated again.

I realise that if I amend a record prior to the last record for a client, I will have to update later records (at least for now), but intend to address that problem later on.

 

[pbaldy]

You shouldn't address it later on because you shouldn't store the balance. Just calculate it on the fly. It's too much grief to try to keep it updated.

 

[Gasman]

You shouldn't address it later on because you shouldn't store the balance. Just calculate it on the fly. It's too much grief to try to keep it updated.

I understand what you are saying Paul and I've already managed to create a query to update the balance if need be.

I *thought* I needed it stored as I was sending it in an email, with the other data and it was one of those cases whereby it was prudent to store the value at the time of creation.

I now realise I could just as easy calculate it in the code for the email. Initially is was being keyed in by myself on record creation.

Will have a play over Xmas with amending the code for the email.

 

[pbaldy]

Surely there will be more fun things to play with over Xmas!

I think you'll be happier in the long run.