Strange behaviour on form?

[Gasman]

Hi,
Having some strange behaviour (for me at least) in a form.
I am aware that one should not store balance in a table, but was doing so so as to be able to report on it easily.

For this I used

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

    If Me.NewRecord Or Me.Dirty Then
        Me.Balance = Me.Balance + Me.Amount
    End If
    Me.Refresh

End Sub

to show the Balance (this is from the table).
There was no Me.Refresh at the time.

So I added a control txtBalance whose control source is

=DSum("Amount","Emails","CMS = " & [CMS] & " AND ID <= " & [ID])

and then added the Me.Refresh

All works if I amend a record, however when adding a new record, it appears that the record pointer moves to the first record with that ID.?

I did have a Required = Yes for a field Reference, which is the next control after the Amount control, and that would complain that a Reference was required, so it appears the record movement was triggering this, and so I removed it. Now I am getting this anomaly.

I did not expect a DSUM to alter the record pointer, so I must be doing something a little stupid?

The form (just in case) is a basic emulated split form, with a Continuous Form as a subform on a main form with the same recordsource.

Any ideas as to what I have done wrong please.?

I realise not a lot to go on, but the DB holds confidential data, which I would have to clean if requested to upload.

TIA

 

[Minty]

On a new record the ID value won't be committed until the record is saved, so your DSum() will probably fail.

#FridayGuess#

 

[arnelgp]

I think you only need to change the balance if
the amount have been entered (changed/updated).
use AfterUpdate Event:

Private Sub Amount_AfterUpdate()
    Dim dblOldAmount As Double
    Dim dblBalance As Double
    dblOldAmount = Nz(Me.Amount.OldValue, 0)
    If Me.TransType = "Payment" Then
        Me.Amount = Me.Amount * -1
    End If
    dblBalance = Nz(DSum("[Amount]", "Emails", "[CMS]=" & Me.CMS & " And [ID] < " & Me.ID), 0)
    Me.Balance = dblBalance + Me.Amount - dblOldAmount
End Sub

 

[Gasman]

Hi arnelgp,
Thanks for that.
The reason I went with the calculated balance option, was that there is on occasion amendments to the amounts when I get the invoices. Generally by a penny or two.
So that meant I had to find that entry and amend, then subsequently amend all transactions for that client to get the latest balance.

I'll try your code and report back. Thank you.

 

[arnelgp]

you can remove the other condition "id=....
since i included the oldvalue on the calculation.

 

[Gasman]

Yes, the oldvalue calculation was giving an incorrect value, so I commented that part out and it seemed to work fine.

Have now put it back in and removed the ID part.

Thanks again.

Edit: I believe I do need the ID part as I need to know the balance at any particular transaction.?

Date            CMS     Amount   Balance
01/09/2018 254165 £200       £200
02/09/2018 254165 £100       £300
03/09/2018 245165 £-65       £235
04/09/2018 245165 £95        £330

Removing that will give me the current balance, not the balance at that time?

 

[Gasman]

Hi Minty,

Fair point, but I was hoping to see the balance calculated as I added a new amount.?

On a new record the ID value won't be committed until the record is saved, so your DSum() will probably fail.

#FridayGuess#

 

[Gasman]

OK, I have found my problem, but would appreciate some assistance in overcoming it.

The scenario, and it only happens when adding a new record. Amending the data does not cause this to happen.

I have a main form that has a continuous subform. However this is an emulated split form in it's basic state, just using the continuous form to easily select records.
The main form open event has

Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = "qryEmails"
    Me.sfrmEmails.SourceObject = "cfrmEmails"
    Set Me.sfrmEmails.Form.Recordset = Me.Recordset
    DoCmd.RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord acDataForm, Me.Name, acPrevious, 5 ' Needed for a continuous form as only last record shows.
    DoCmd.RunCommand acCmdRecordsGoToLast

End Sub

When I enter an amount I recalc the balance, however this balance is currently stored to the table.
I have added a control txtCalcBalance to show the balance on the fly, and it is this control that I want to refresh to show the value any time the amount changes. I have tried Me.txtCalcBalance.Requery in the AfterUpdate even of Amount, but that does not refresh the control.

Source for txtCalcbalance is

=DSum("Amount","Emails","CMS = " & [CMS] & " AND ID <= " & [ID])

In the AfterUpdate event for the amount I have

Private Sub Amount_AfterUpdate()
    Dim dblOldAmount As Double
    Dim dblBalance As Double
    
    'dblOldAmount = Nz(Me.Amount.OldValue, 0)
    If Me.TranType = "Payment" Then
        Me.Amount = Me.Amount * -1
    End If
    dblBalance = Nz(DSum("[Amount]", "Emails", "[CMS]=" & Me.CMS & " And [ID] < " & Me.ID), 0)
    Me.Balance = dblBalance + Me.Amount '- dblOldAmount
    Me.Refresh
    
End Sub

and I believe that it is the Me.Refresh that is causing the problem.?
Whilst the record number and record count of the main form shows me to be on the last record, the actual record showing in the main form is the first record.?
I've tried storing the bookmark and then reassigning it, but it complains of an invalid bookmark, presumably as the record has not actually been created yet.?

So all I am trying to do is show the balance in txtCalcbalance as I move out of the Amount control, even on a new record.
If I have any check on data in the form's BeforeUpdate event, that stops this happening.? I did have Required set for a field in the table and that had the same effect.

TIA

 

[Gasman]

Ok,
I think I have found a workaround, but I'd like to do it the 'correct' way if there is one.?
I've amended the source for the txtCalcBalance to

=DSum("Amount","Emails","CMS = " & [CMS] & " AND ID < " & [ID]) + [Amount]

and instead of refreshing the form, I have used

Me.txtCalcBalance.Requery