Sum of Values

  • Thread starter Thread starter Jim Franklin
  • Start date Start date
J

Jim Franklin

Hi,

Can anyone tell me if there is an easy way of doing this:

I have a continuous form, containing a number field [fieldx]. Each time the
form is opened I would like to calculate sum([fieldx]) and hold this as a
static value. When I close the form again, I want to calculate the
difference between sum([fieldx]) at the close with the value calculated when
the form opened. I cannot use fieldx.OldValue as I am adding and deleting as
well as editing records.

Thanks for any help,

Jim F.
 
The following example assumes that you want to take into account any
filtering applied to the form. Otherwise it would be faster to query the
table directly. The key is the declaration of the variable that stores the
initial value ("mcurInitVal" in this example) at the top of the module,
outside of any procedure, so that it is accessible to any code in the
module, and retains its value as long as the form remains open. If you want
to search the documentation for further details, keywords to look for are
'scope' and 'lifetime'.

Option Compare Database
Option Explicit

Private mcurInitVal As Currency

Private Sub Form_Load()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
mcurInitVal = mcurInitVal + Nz(rst.Fields("UnitPrice"), 0)
rst.MoveNext
Loop

End Sub

Private Sub Form_Unload(Cancel As Integer)

Dim curCurVal As Currency
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
curCurVal = curCurVal + Nz(rst.Fields("UnitPrice"), 0)
rst.MoveNext
Loop
MsgBox curCurVal - mcurInitVal

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Brendan, thanks.

One problem. My form actually opens as a subform on another form, which only
ever shows one particular record each time it is opened. The form and
subform are linked by master and child fields. The subform recordsource is a
table. The problem is that the subform's recordsetclone seems to include a
different set of records when the subform loads as it does when it unloads.

The mcurCurrVal is therefore correct, but the mcurInitVal comes out wrong.
Sorry, I did not mention this to begin with as I didnt think it would make
any difference.

Jim



Brendan Reynolds said:
The following example assumes that you want to take into account any
filtering applied to the form. Otherwise it would be faster to query the
table directly. The key is the declaration of the variable that stores the
initial value ("mcurInitVal" in this example) at the top of the module,
outside of any procedure, so that it is accessible to any code in the
module, and retains its value as long as the form remains open. If you want
to search the documentation for further details, keywords to look for are
'scope' and 'lifetime'.

Option Compare Database
Option Explicit

Private mcurInitVal As Currency

Private Sub Form_Load()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
mcurInitVal = mcurInitVal + Nz(rst.Fields("UnitPrice"), 0)
rst.MoveNext
Loop

End Sub

Private Sub Form_Unload(Cancel As Integer)

Dim curCurVal As Currency
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
curCurVal = curCurVal + Nz(rst.Fields("UnitPrice"), 0)
rst.MoveNext
Loop
MsgBox curCurVal - mcurInitVal

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Jim Franklin said:
Hi,

Can anyone tell me if there is an easy way of doing this:

I have a continuous form, containing a number field [fieldx]. Each time
the
form is opened I would like to calculate sum([fieldx]) and hold this as a
static value. When I close the form again, I want to calculate the
difference between sum([fieldx]) at the close with the value calculated
when
the form opened. I cannot use fieldx.OldValue as I am adding and deleting
as
well as editing records.

Thanks for any help,

Jim F.
Click to expand...
Click to expand...
 
A subform actually loads before its main form, so I expect the recordset
clone will include all records at that point. Try moving the code to the
Load event of the main form, and modifying it to get the recordset clone of
the subform: "Set rst = Me!NameOfSubformControl.Form.RecordsetClone".

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Jim Franklin said:
Brendan, thanks.

One problem. My form actually opens as a subform on another form, which
only
ever shows one particular record each time it is opened. The form and
subform are linked by master and child fields. The subform recordsource is
a
table. The problem is that the subform's recordsetclone seems to include a
different set of records when the subform loads as it does when it
unloads.

The mcurCurrVal is therefore correct, but the mcurInitVal comes out wrong.
Sorry, I did not mention this to begin with as I didnt think it would make
any difference.

Jim



Brendan Reynolds said:
The following example assumes that you want to take into account any
filtering applied to the form. Otherwise it would be faster to query the
table directly. The key is the declaration of the variable that stores
the
initial value ("mcurInitVal" in this example) at the top of the module,
outside of any procedure, so that it is accessible to any code in the
module, and retains its value as long as the form remains open. If you want
to search the documentation for further details, keywords to look for are
'scope' and 'lifetime'.

Option Compare Database
Option Explicit

Private mcurInitVal As Currency

Private Sub Form_Load()

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
mcurInitVal = mcurInitVal + Nz(rst.Fields("UnitPrice"), 0)
rst.MoveNext
Loop

End Sub

Private Sub Form_Unload(Cancel As Integer)

Dim curCurVal As Currency
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
curCurVal = curCurVal + Nz(rst.Fields("UnitPrice"), 0)
rst.MoveNext
Loop
MsgBox curCurVal - mcurInitVal

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Jim Franklin said:
Hi,

Can anyone tell me if there is an easy way of doing this:

I have a continuous form, containing a number field [fieldx]. Each time
the
form is opened I would like to calculate sum([fieldx]) and hold this as a
static value. When I close the form again, I want to calculate the
difference between sum([fieldx]) at the close with the value calculated
when
the form opened. I cannot use fieldx.OldValue as I am adding and deleting
as
well as editing records.

Thanks for any help,

Jim F.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top