Updating total field on master form with sum of values on sub form (1 Viewer)

[dsajones]

I have a simple database consisting of a table of driving school pupil details and a linked table of the lessons they take including how many hours for each lesson.

I've created a form with a subform. Parent record details on the main form and a table of the lessons on the subform. When I add, change or delete records on the lessons subform, I need to recalculate the total hours from the records on the subform and update the totalhours field on the main form.

I've searched for and found a solution for getting the sum of the hours from the a text box in the footer of the subform to a text field on the main form. It updates itself correctly as I change records in the subform. But I can't get the actual database field on the main form to update when the text field updates. I've tried adding code to the afterupdate, change and dirty events of the text field but the main field still doesn't update itself.

Any help much appreciated.

Cheers
David

 

[PNGBill]

Some issues to consider...

You could force a Requery - may be needed with some of the following.

You can conditional sum ie just sum fields that meet certain criteria.
This may allow the main form sum to appear to reflect the subfrom data but in fact it just follows the criteria.
I can elaborate on this if you need.

Use a User Defined Function in your main form control that used the data/criteria in the subform.

Just some suggestions as to what may or may not work in our situation.
Solutions may depend on what fields are in your mainform record source sql.

 

[PNGBill]

Here is a conditional sum example

=Sum(IIf([APLBank]="ANZ",[Principal],0))

Only records where field APLBank = ANZ will Principal field be considered in the Sum result.

 

[dsajones]

Hi and thanks for the quick reply. Not quite sure I've followed what you suggest. I always thought a requery was to refresh data from the database itself.

Here's more info on the data and forms.

Master table name - Pupil_Details
Field name to hold total hours - PupilTotalHours

Child table name - Pupil_LessonLog
Field name to hold hours for each individual lesson - Lesson_Length

Obviously a 1 to many repaltionship exists between Pupil_Details and Pupil_LessonLog

Main form name - Pupils
Sub Form name - Pupil_LessonLog

On sub form I have a text field in the footer called SumOfHours and it's Control Source is =Sum([lesson_length])
That works perfectly and each time I change the Lesson_length or add or delete a record, this field updates correctly.

On main form I have a text field called TempSumOfHours and its Control Source is =[Pupil_LessonLog subform].[Form]![SumOfHours]
Again, that appears to be working so that it keeps in step with the SumOfHours field on the sub form.

So all I now want is that the actual bound field of PupilTotalHours also gets updated whenever TempSumOfHours on main form and SumOfHours on subform pick up the new total.

I did get it working sort of but it seemed to be one step behind the actual changes. So if I changed a child record, nothing would happen until I changed another child record. Then PupilTotalHours would change to the total value of the change before.

 

[PNGBill]

If you have Main Form and Sub Forms.
You change some data on a subform and want the data displayed on teh main form to reflect same - provided the changes are made to the data the forms use, then Requiry the main form and your changes should be evident.

Try adding a Requiry of the main form to the AfterUpdate event on the subform control where you change a record.
This would Lesson_Length

You can also add the requiry code to the subform AfterUpdate event.

Code would be

Me.Parent.Requery

 

[PNGBill]

Sorry, the code should be

DoCmd.Requery "mainformname"

 

[dsajones]

Hi. At the moment there is nothing that is actually assigning the value in the TempSumOfHours field to the actual bound database field. So are you suggesting that I need something like the following in the AfterUpdate event of the Lesson_Length field and/or AfterUpdate event of the whole subform:

Bound Field value = Value in TempSumOfHours
Requery main form

If so, then I can't find the correct code syntax to do that.

 

[PNGBill]

By Requerying the Form you are simulating Opening the form.

The form control TempSumOfHours is not bound to a Table or Query but it should still recalculate.

I have attached a handy syntax for subs document from P Baldy.

You can Requery the form control which should be

Me.Parent!TempSumOfHours.Requery

 

[dsajones]

Hi and thanks for that document. It's really useful (why don't you find this stuff in the actual software documentation !?!).

I'm still not having any luck. At the moment I have the following:


Private Sub Lesson_Length_AfterUpdate()

Me.Parent!PupilTotalHours = Me.SumOfHours
Me.Parent.Requery

End Sub

So I'm not using the temp field on the main form. This is working except that the bound field (PupilTotalHours) is always 1 update behind. So when I change the value of a Lesson_Length on the subform the following happens:

subform.SumOfHours - has correct value
parent.TempSumOfHours - has correct value
parent.PupilTotalHours - changes at the same time as the other fields change but is one step behind.

This seems such a basic requirement that I can't believe how hard this is to get working

 

[PNGBill]

I still can't help thinking a Full Requery will resolve the issue.

Is it possible to attach a database with non sensitive data ?
I can work on a solution in 10 hours.
Maybe other advice may assist in the meantime.

I wouldn't have thought you would need to use this line of code

Me.Parent!PupilTotalHours = Me.SumOfHours

as the control source should already be set, just requires a requery of control and or the whole form.

If you close the form open it, is all the data displayed correctly ? until you make any more changes.

 

[spikepl]

I had a similar issue one time, that the display was one step behidn actual data. I guess it has to do with som repainting of screen etc... and long time ago I decided I cannot be bothered, so I never use the default control names (created by access from the recordset a form is bound to at creation).

Try renaming the textboxes involved into other than the default names (as given by the name of the field they are bound to. Prefix them with txt. See if that helps. Start with the PupilTotalHours -> txtPupilTotalHours

 

[dsajones]

Thanks both for further help. I've tried renaming the bound field and prefixing it with txt but that still didn't work.

Copy of database attached. I've removed the various code attempts.

 

[spikepl]

WHat is the problem again? The moment I update Lesson_length AND move to another record, then the sum is updated in both subform and form. The move casues a save, but before hat you will not get any change in the sums. If you force a save in AfterUpdate of your time, then you'll get an immedaite reaction.

 

[dsajones]

Hi Spikepl. Yes, the two text fields are updating correctly. But I'm trying to get that value in to the bound field on the main form - PupilTotalHours and save it to the main Pupil record. Haven't found any way of getting that to work properly yet.

Thanks for your help.

 

[spikepl]

Whadyamean?

When I update LEsson and move to another record in the subform then the sum on the main form updates. How does that deviate from what you have?

 

[dsajones]

The text field on the main form is updating. But that is only there as a method that I found to transfer the total from the sub form. But what I'm trying to do is to update the actual database field called PupilTotalHours which is in the table Pupil_Details.

All I'm actually trying to do from a logic perspective is:

When a lesson record is added or deleted or the lesson length in an existing record is updated, update the total hours in the main pupil record to equal the sum of lesson length in lesson records.

I had assumed that I would add code to the afterupdate of the lesson length field with a simple update along the lines of pupil_details.pupiltotalhours = sum(pupillessonlog.lesson_length). But things never seem to be quite that simple in the Access world.

On another forum I found plenty of posts about how to produce a sum of records in a child form and transfer that value to the parent form. That's why I have the two text fields. One in the footer of the child form and one on the main form. And producing the sum and reflecting it in the text field on the main form is working fine. But I still can't get that value in to the actual database field pupil_details.pupiltotalhours.

Hope I've been able to explain it clearly now and thanks for giving your time to help me.

 

[spikepl]

I get it. I think.

You want to store a calculated value in your db, and that is what gives the problem, because you cannot at the same time bind a control to the db, and set its Control Source to some calculated or derived value.

1. Do not store that value in the db. SImply replace the control source to the reference to the sum in your subform.
2. If you do insist, then you have to have an event to update the value. It could be in the AfterUpdate of the Subform, where you could set the value of the control in the parent form to the sum on your subform.

 

[dsajones]

Hi Spikepl

1 - I know that from a DB practice it's best not to store calculated values in the DB but for various reasons this is what I need to do.
2 - Which brings me back to where I started. I can't find an event that fires correctly to update the pupiltotalhours with the current value of the sum of Lesson_Length. With the following code in the afterupdate event of the subform the pupiltotalhours value is always one step behind i.e. storing the value before the update was done.


Me.Parent.PupilTotalHours = Me.SumOfHours

 

[spikepl]

Try this in the subform:

Private Sub Form_AfterUpdate()
Me.Recalc
Me.Parent.txtPupilTotalHours = Me.SumOfHours
End Sub

 

[dsajones]

Try this in the subform:

Private Sub Form_AfterUpdate()
Me.Recalc
Me.Parent.txtPupilTotalHours = Me.SumOfHours
End Sub

That's it!! Yes, that's working great. Thank you for your help.