Strange behaviour on form? (1 Viewer)

Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 10:52
Joined
Sep 21, 2011
Messages
14,254
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
Code: 
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
Code: 
=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

Minty

AWF VIP
Local time
Today, 10:52
Joined
Jul 26, 2013
Messages
10,370
On a new record the ID value won't be committed until the record is saved, so your DSum() will probably fail.

#FridayGuess#
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:52
Joined
May 7, 2009
Messages
19,230
I think you only need to change the balance if
the amount have been entered (changed/updated).
use AfterUpdate Event:
Code: 
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

Gasman

Enthusiastic Amateur
Local time
Today, 10:52
Joined
Sep 21, 2011
Messages
14,254
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:52
Joined
May 7, 2009
Messages
19,230
you can remove the other condition "id=....
since i included the oldvalue on the calculation.
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 10:52
Joined
Sep 21, 2011
Messages
14,254
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.?

Code: 
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?
 
Last edited:
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 10:52
Joined
Sep 21, 2011
Messages
14,254
Hi Minty,

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

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

#FridayGuess#
Click to expand...
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 10:52
Joined
Sep 21, 2011
Messages
14,254
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
Code: 
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
Code: 
=DSum("Amount","Emails","CMS = " & [CMS] & " AND ID <= " & [ID])
In the AfterUpdate event for the amount I have
Code: 
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

Gasman

Enthusiastic Amateur
Local time
Today, 10:52
Joined
Sep 21, 2011
Messages
14,254
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
Code: 
=DSum("Amount","Emails","CMS = " & [CMS] & " AND ID < " & [ID]) + [Amount]

and instead of refreshing the form, I have used
Code: 
Me.txtCalcBalance.Requery
 
You must log in or register to reply here.

Similar threads

C
Does Dsum drag form down?
Replies
17
Views
315
ebs17
E
J
Solved subform remains blank after new record saved
Replies
3
Views
167
Jaks
J
Gasman
Solved Exporting a form as text and then importing.?
Replies
15
Views
146
cheekybuddha
cheekybuddha
A
Solved Problem scrolling on form with a tab control
Replies
10
Views
489
AlliCarr
A
G
Solved Error #size! on calculated control based on subform value
Replies
2
Views
177
GaP42
G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom