Stumped: summing amounts on a subform

[dave]

I have a main form that displays a subform in continuous form mode. The
amount textboxes (txtAmt) in the individual records on the subform should
sum up to the amount on the main form (txtGTotal). If they do not, I need
to alert the user before moving off the record.

I created a hidden text box (txtSumAmt) on the subform that sums the
individual txtAmt text boxes. I can compare this to txtGTotal on the main
form but I have two problems.

1. I don't know the event that triggers when the user moves off the current
record.

2. I tried coding the onCurrent event of the main form with this:

Private Sub Form_Current()
Dim SumAmt As Currency

SumAmt = Me.mySubForm.Form![txtSumAmt].Value

If txtGTotal.Value <> SumAmt Then
MsgBox ("Amounts do not agree." & _
vbCr & "Sum of individual amounts is: " & SumAmt)
txtGTotal.SetFocus
End If

But thus only works when the user moves ON to the record, not OFF of it.
Plus, if there are many subform records, the value of SumAmt is reported as
zero when the onCurrent event initially fires so the message box is
displayed even when in fact the two totals are the same.

I think my whole approach to this may be flawed. Hopefully someone out
there who has worked with subtotaling subforms can give me some direction on
how to proceed.

Dave

PS I could just set txtGTotal on the main form equal to txtSumAmt on the
subform. However, both the total and the sub amounts are available for input
and we want a one to be a check on the other for data validation purposes .

 

[Ken Snell]

Your form setup is the "source" of your dilemma. While coding might overcome
it, first reconsider how your form will operate -- anytime you must require
a user to enter sufficient data into another form in order that your form
can be allowed to move to a new record, it's possible (likely) that you need
to rethink how you store/validate/enter data. And you are "asking" for
difficult situations where a user must take additional actions just to
satisfy that summed value. This makes for possibly frustrated users who then
just cancel out of what they're doing and causing the very data
mismatch/omission that your form is trying to avoid. That red x at the top
right of ACCESS is a strong message to the programmer that the user is not
amused.

Can you allow the database to store the data while an incomplete amount of
data have been entered, and then just mark the entry as incomplete and thus
prevent its use in later actions? Only after the user has entered all
required data in this and/or subsequent sessions can the record be marked as
complete? Are there other ways to get the subform's data that don't have to
occur at exactly the moment of the main form's viewing?

With that suggestion and those questions......

The OnCurrent event is used to show when a user has changed to a different
record. It does indicate that the user has left the previous record, but,
unless you've stored the info from that previous record in variables, you
won't have access to the exact data from that record while in the "now
current" record.

OnCurrent is available in the subform's form as well. You can code that
event separately than the main form's OnCurrent event.

If the user is editing data on the subform's record, you can use the
subform's form's BeforeUpdate event to test/validate data that are in the
record. However, note that that the summing textbox may not contain the sum
that includes the current record's data until the record has been fully
saved (updated) to the recordsource.

When your user moves from the main form to the subform, the main form's
record will be saved to the underlying recordsource. When your user returns
to the main form from the subform, unless the user then edits the main
form's record, you won't be able to use the BeforeUpdate event of the main
form to test/validate that all records have been entered in the subform.

Thus, I can think of a few ways you might do what you seek. Each has its
advantages and disadvantages, some of which could lead to user frustration
and/or likelihood of "killing" the form and then you'll get all kinds of
interesting results.

The easier one may be to use the subform control's OnExit event to
test/validate if enough records have been entered into the subform to give
the total that is needed. This event occurs when focus is moving from the
subform to the main form. Note that this event is not available on the
subform's form; it's on the control (on the main form) that holds the actual
subform object. Use the OnExit event of the subform control to run code that
compares the "current value in the summing textbox on the subform" to the
"value on the main form". If yes, then allow the exit to continue. If not,
cancel the exit, tell the user that more data need to be entered, and thus
require the user to stay in the subform until enough records/data have been
entered to provide the required sum. Watch out for a frustrated user who
"cancels" the program!

A more difficult one would be to set a flag in a hidden textbox when the
user first goes to the subform, and when the focus returns to the main form
(use the OnExit event of the subform control perhaps to set the focus to the
correct control that has this code running on the control's OnGotFocus --
could be a nearly invisible textbox), test if the sums match. If not, tell
the user and send the user back to the subform for entering more data.Watch
out for a frustrated user who "cancels" the program!


--

Ken Snell
<MS ACCESS MVP>

I have a main form that displays a subform in continuous form mode. The
amount textboxes (txtAmt) in the individual records on the subform should
sum up to the amount on the main form (txtGTotal). If they do not, I need
to alert the user before moving off the record.

I created a hidden text box (txtSumAmt) on the subform that sums the
individual txtAmt text boxes. I can compare this to txtGTotal on the main
form but I have two problems.

1. I don't know the event that triggers when the user moves off the current
record.

2. I tried coding the onCurrent event of the main form with this:

Private Sub Form_Current()
Dim SumAmt As Currency

SumAmt = Me.mySubForm.Form![txtSumAmt].Value

If txtGTotal.Value <> SumAmt Then
MsgBox ("Amounts do not agree." & _
vbCr & "Sum of individual amounts is: " & SumAmt)
txtGTotal.SetFocus
End If

But thus only works when the user moves ON to the record, not OFF of it.
Plus, if there are many subform records, the value of SumAmt is reported as
zero when the onCurrent event initially fires so the message box is
displayed even when in fact the two totals are the same.

I think my whole approach to this may be flawed. Hopefully someone out
there who has worked with subtotaling subforms can give me some direction on
how to proceed.

Dave

PS I could just set txtGTotal on the main form equal to txtSumAmt on the
subform. However, both the total and the sub amounts are available for input
and we want a one to be a check on the other for data validation purposes ..

 

[Dave]

Thank you Ken for the extremely helpful info.

I have a lot to learn about events in Access.

Your form setup is the "source" of your dilemma. While coding might overcome
it, first reconsider how your form will operate -- anytime you must require
a user to enter sufficient data into another form in order that your form
can be allowed to move to a new record, it's possible (likely) that you need
to rethink how you store/validate/enter data. And you are "asking" for
difficult situations where a user must take additional actions just to
satisfy that summed value. This makes for possibly frustrated users who then
just cancel out of what they're doing and causing the very data
mismatch/omission that your form is trying to avoid. That red x at the top
right of ACCESS is a strong message to the programmer that the user is not
amused.

Can you allow the database to store the data while an incomplete amount of
data have been entered, and then just mark the entry as incomplete and thus
prevent its use in later actions? Only after the user has entered all
required data in this and/or subsequent sessions can the record be marked as
complete? Are there other ways to get the subform's data that don't have to
occur at exactly the moment of the main form's viewing?

With that suggestion and those questions......

The OnCurrent event is used to show when a user has changed to a different
record. It does indicate that the user has left the previous record, but,
unless you've stored the info from that previous record in variables, you
won't have access to the exact data from that record while in the "now
current" record.

OnCurrent is available in the subform's form as well. You can code that
event separately than the main form's OnCurrent event.

If the user is editing data on the subform's record, you can use the
subform's form's BeforeUpdate event to test/validate data that are in the
record. However, note that that the summing textbox may not contain the sum
that includes the current record's data until the record has been fully
saved (updated) to the recordsource.

When your user moves from the main form to the subform, the main form's
record will be saved to the underlying recordsource. When your user returns
to the main form from the subform, unless the user then edits the main
form's record, you won't be able to use the BeforeUpdate event of the main
form to test/validate that all records have been entered in the subform.

Thus, I can think of a few ways you might do what you seek. Each has its
advantages and disadvantages, some of which could lead to user frustration
and/or likelihood of "killing" the form and then you'll get all kinds of
interesting results.

The easier one may be to use the subform control's OnExit event to
test/validate if enough records have been entered into the subform to give
the total that is needed. This event occurs when focus is moving from the
subform to the main form. Note that this event is not available on the
subform's form; it's on the control (on the main form) that holds the actual
subform object. Use the OnExit event of the subform control to run code that
compares the "current value in the summing textbox on the subform" to the
"value on the main form". If yes, then allow the exit to continue. If not,
cancel the exit, tell the user that more data need to be entered, and thus
require the user to stay in the subform until enough records/data have been
entered to provide the required sum. Watch out for a frustrated user who
"cancels" the program!

A more difficult one would be to set a flag in a hidden textbox when the
user first goes to the subform, and when the focus returns to the main form
(use the OnExit event of the subform control perhaps to set the focus to the
correct control that has this code running on the control's OnGotFocus --
could be a nearly invisible textbox), test if the sums match. If not, tell
the user and send the user back to the subform for entering more data.Watch
out for a frustrated user who "cancels" the program!


--

Ken Snell
<MS ACCESS MVP>

I have a main form that displays a subform in continuous form mode. The
amount textboxes (txtAmt) in the individual records on the subform should
sum up to the amount on the main form (txtGTotal). If they do not, I need
to alert the user before moving off the record.

I created a hidden text box (txtSumAmt) on the subform that sums the
individual txtAmt text boxes. I can compare this to txtGTotal on the main
form but I have two problems.

1. I don't know the event that triggers when the user moves off the current
record.

2. I tried coding the onCurrent event of the main form with this:

Private Sub Form_Current()
Dim SumAmt As Currency

SumAmt = Me.mySubForm.Form![txtSumAmt].Value

If txtGTotal.Value <> SumAmt Then
MsgBox ("Amounts do not agree." & _
vbCr & "Sum of individual amounts is: " & SumAmt)
txtGTotal.SetFocus
End If

But thus only works when the user moves ON to the record, not OFF of it.
Plus, if there are many subform records, the value of SumAmt is reported as
zero when the onCurrent event initially fires so the message box is
displayed even when in fact the two totals are the same.

I think my whole approach to this may be flawed. Hopefully someone out
there who has worked with subtotaling subforms can give me some

direction

on
how to proceed.

Dave

PS I could just set txtGTotal on the main form equal to txtSumAmt on the
subform. However, both the total and the sub amounts are available for input
and we want a one to be a check on the other for data validation

purposes

.

 

[Ken Snell]

Post back with questions and solutions!

--

Ken Snell
<MS ACCESS MVP>

Thank you Ken for the extremely helpful info.

I have a lot to learn about events in Access.

Your form setup is the "source" of your dilemma. While coding might overcome
it, first reconsider how your form will operate -- anytime you must require
a user to enter sufficient data into another form in order that your form
can be allowed to move to a new record, it's possible (likely) that you need
to rethink how you store/validate/enter data. And you are "asking" for
difficult situations where a user must take additional actions just to
satisfy that summed value. This makes for possibly frustrated users who then
just cancel out of what they're doing and causing the very data
mismatch/omission that your form is trying to avoid. That red x at the top
right of ACCESS is a strong message to the programmer that the user is not
amused.

Can you allow the database to store the data while an incomplete amount of
data have been entered, and then just mark the entry as incomplete and thus
prevent its use in later actions? Only after the user has entered all
required data in this and/or subsequent sessions can the record be

marked

as
complete? Are there other ways to get the subform's data that don't have to
occur at exactly the moment of the main form's viewing?

With that suggestion and those questions......

The OnCurrent event is used to show when a user has changed to a different
record. It does indicate that the user has left the previous record, but,
unless you've stored the info from that previous record in variables, you
won't have access to the exact data from that record while in the "now
current" record.

OnCurrent is available in the subform's form as well. You can code that
event separately than the main form's OnCurrent event.

If the user is editing data on the subform's record, you can use the
subform's form's BeforeUpdate event to test/validate data that are in the
record. However, note that that the summing textbox may not contain the sum
that includes the current record's data until the record has been fully
saved (updated) to the recordsource.

When your user moves from the main form to the subform, the main form's
record will be saved to the underlying recordsource. When your user returns
to the main form from the subform, unless the user then edits the main
form's record, you won't be able to use the BeforeUpdate event of the main
form to test/validate that all records have been entered in the subform.

Thus, I can think of a few ways you might do what you seek. Each has its
advantages and disadvantages, some of which could lead to user frustration
and/or likelihood of "killing" the form and then you'll get all kinds of
interesting results.

The easier one may be to use the subform control's OnExit event to
test/validate if enough records have been entered into the subform to give
the total that is needed. This event occurs when focus is moving from the
subform to the main form. Note that this event is not available on the
subform's form; it's on the control (on the main form) that holds the actual
subform object. Use the OnExit event of the subform control to run code that
compares the "current value in the summing textbox on the subform" to the
"value on the main form". If yes, then allow the exit to continue. If not,
cancel the exit, tell the user that more data need to be entered, and thus
require the user to stay in the subform until enough records/data have been
entered to provide the required sum. Watch out for a frustrated user who
"cancels" the program!

A more difficult one would be to set a flag in a hidden textbox when the
user first goes to the subform, and when the focus returns to the main form
(use the OnExit event of the subform control perhaps to set the focus to the
correct control that has this code running on the control's OnGotFocus --
could be a nearly invisible textbox), test if the sums match. If not, tell
the user and send the user back to the subform for entering more data.Watch
out for a frustrated user who "cancels" the program!


--

Ken Snell
<MS ACCESS MVP>

I have a main form that displays a subform in continuous form mode. The
amount textboxes (txtAmt) in the individual records on the subform should
sum up to the amount on the main form (txtGTotal). If they do not, I need
to alert the user before moving off the record.

I created a hidden text box (txtSumAmt) on the subform that sums the
individual txtAmt text boxes. I can compare this to txtGTotal on the main
form but I have two problems.

1. I don't know the event that triggers when the user moves off the current
record.

2. I tried coding the onCurrent event of the main form with this:

Private Sub Form_Current()
Dim SumAmt As Currency

SumAmt = Me.mySubForm.Form![txtSumAmt].Value

If txtGTotal.Value <> SumAmt Then
MsgBox ("Amounts do not agree." & _
vbCr & "Sum of individual amounts is: " & SumAmt)
txtGTotal.SetFocus
End If

But thus only works when the user moves ON to the record, not OFF of it.
Plus, if there are many subform records, the value of SumAmt is

reported
as

zero when the onCurrent event initially fires so the message box is
displayed even when in fact the two totals are the same.

I think my whole approach to this may be flawed. Hopefully someone out
there who has worked with subtotaling subforms can give me some

direction

on
how to proceed.

Dave

PS I could just set txtGTotal on the main form equal to txtSumAmt on the
subform. However, both the total and the sub amounts are available for input
and we want a one to be a check on the other for data validation

purposes

.