Summing a subform to display a total on parent form

[David F]

I have a subform with several records displayed in datasheet view. As the
user inserts a value in the "amount" field in the subform, I need the
cummulative total (sum) for all the related "amount" fields to display in a
textbox on the parent form.

Can anyone give me some guidance on how to code this?

I can write to the parent form using the AfterUpdate event of the "amount"
field in the subform like:

Form_parentformname.amount.Value = 10

But I am not sure how to _sum_ the "amount" field for the all the records
showing the in subform.

The SQL would look something like this: SELECT sum(amount) FROM orders WHERE
id=linkedfieldid.

But how can I get this value from within a VB module?

 

[Marshall Barton]

I have a subform with several records displayed in datasheet view. As the
user inserts a value in the "amount" field in the subform, I need the
cummulative total (sum) for all the related "amount" fields to display in a
textbox on the parent form.

Can anyone give me some guidance on how to code this?

You don't need any code for this. Add a text box named
txtTotalAmount to the subform's footer section and use the
expression:

=Sum(Amount)

Then the text box in the main form can display the sum by
using the expression:

=subformcontrolname.Form.txtTotalAmount

 

[David F]

Thanks Marshall, that works well.

One other question, what is the event that fires when the textbox on the
parent changes based on input in the sub? I tried coding the AfterUpdate
and OnChange events but these do not seem to be firing. Do I have to code
this in the sub?

Also, what event fires when I move off the record? IOW, when I click on the
standard navigation buttons at the bottom of the form to move next or
previous.

 

[Marshall Barton]

Thanks Marshall, that works well.

One other question, what is the event that fires when the textbox on the
parent changes based on input in the sub? I tried coding the AfterUpdate
and OnChange events but these do not seem to be firing. Do I have to code
this in the sub?

There is no event that fires when a calculated control is
(re)evaluated. Why do you think you need this?

Also, what event fires when I move off the record? IOW, when I click on the
standard navigation buttons at the bottom of the form to move next or
previous.

I think A2003 has (or almost had) an event when a record was
about to lose its status as the current record? But,
lacking that, you can try to use the form's AfterUpdate
event that fires after the changes to a record are saved.

The form's Current event fires just as a record becomes the
current record. Again, I don't have any idea what you're
trying to accomplish so I can't off any specific suggestions
--
Marsh
MVP [MS Access]

 

[David F]

The subform has field called "percentage." The sum of the percentage fields
for all the records displayed in the subform should be 100%.

The sum is displayed in a text box on the main form. As records are added or
updated in the subform, the sum text box in the main form changes.

I wanted to implement two features to help ensure a user's percentages
always summed to 100%:

1. Display the value in the sum text box on the main form in red if it was
not equal to 100%

2. Fire a message box when the user tried to move off the current record and
the sum text box on the main form was not equal to 100%

In order to do this I need to know the appropriate events to code.

 

[Marshall Barton]

The subform has field called "percentage." The sum of the percentage fields
for all the records displayed in the subform should be 100%.

The sum is displayed in a text box on the main form. As records are added or
updated in the subform, the sum text box in the main form changes.

I wanted to implement two features to help ensure a user's percentages
always summed to 100%:

1. Display the value in the sum text box on the main form in red if it was
not equal to 100%

If you're using A2K or later, I think(?) you can use
Conditional Formatting (Format menu) for this. You can't
really use VBA code in an event procedure, because the
controls are calculated asynchronously from the execution of
of code.

2. Fire a message box when the user tried to move off the current record and
the sum text box on the main form was not equal to 100%

With the above caveat, you might be able to use the form's
BeforeUpdate event. This fires just before the record is
saved and it has the Cancel argument in case you want to
prevent the record from being saved until the total is
acceptable. Granted this may fire even if they don't move
to another record, but, depending on what else you might
have going on, this may be acceptable.