Populate $ Amount Field in Main Form With Total From SubForm

[Bill6868]

I have a fundraising event where a donor pledges a sponsorship $ amount but doesn’t pay all at once. I set up a Continuous subForm to track different amounts paid towards that pledge along with the date the payment was received.

In the subForm footer I have an unbound text box that totals all the amounts paid. I need to get this figure into a field on the Main Form.

Something like this:

Main Form [TotalAmountsReceived] = [TotalAmountsPaid] ‘this is the unbound text box in the subform footer.

If I could get the syntax right I would write this into an event procedure, like when the subForm record is updated and/or when the Main Form is Closed.

I am not sure if this can even be done, that is - populate a field on the Main Form with a Text Box sum in the footer of the subForm. If anyone has any suggestions it would be greatly appreciated.

 

[randy700]

How do you get the total in the SubForm?
I'd get the total from the data in the table.


Randy

 

[dhookom]

I expect you could use the after update event of subform with code like:

Me.Parent.txtControlOnMainForm = Me.txtControlOnSubForm

This assumes you have a good reason to store a calculated value.


Duane
Hook'D on Access
MS Access MVP

 

[Bill6868]

Randy - from the underlying query I was able to get the sum from the subform table, however now I can't edit the records. I get a "Cannot update recordset".

dhookom - Youe suggestion is also what I am looking for. How do I make this = that? I'm having trouble with writing the syntax of your suggestion. The field in the Parent form is named [Text200] and the name of the sum in footer of the sub-form is named [Text97].

How would I get [Text200] to equal [Text97] when I either close the form or update the subform record (or add a record)? What would the syntax look like when written?

 

[dhookom]

Bill6868,
Change the names of your text boxes to names that make sense. Then substitute the better names into the code that I suggested.

If you have a bunch of existing records, I would probably use an update query to update the field in the parent record. After this, the code suggested in the After Update of the subform should handle any updates.

Again, I am not in favor of storing the value in the parent record.

Duane
Hook'D on Access
MS Access MVP

 

[Bill6868]

Thanks Randy and dhookom. As you suggested, not storing this value in the Parent table was the clue I needed for a resolution. Made it a calculated value at the query level in the Parent table. Had to edit the join properties between the two tables to get the result I was looking for.

Much appreciated!

bill6868