Calculated sum problem

[aquinachuva]

Hello. I have a parent form with bound fields for totals from a child form. The child form is continuous and I am using text boxes in this sub form’s footer to calculate the totals. The control source for one of these fields looks like this: =Sum(IIf([homen]='1',1,0)). I then use a button on the main form to pass the values to bound controls on the main form. When I use the mouse to move out of the subform and click on the mainform button, the last subform record is not included in the sums. I have tried requery and recalc. These do update the subform totals in the subform footer, but only after my code to copy the values to the main form has run. I have lots of controls on the subform (>30). If I reduce this to <10, it seems to work. So,the problem is probably the delay in calculating. Any ideas on how to deal with this?
Thanks,
Rob

 

[jjturner]

Hello and Welcome,

May I ask why you're storing calculated values (via the main form bound controls)?

And it almost looks as if your Sum should be a Count instead...

Apologies for not addressing your problem directly, but perhaps if these aspects of your circumstances are considered, it may obviate your update issue (which I suspect would be cured with an explicit "Save Record" command prior to executing the remainder of your code).

HTH,
John

 

[aquinachuva]

Hi John and thanks for the welcome. I have a continuous subform with lots of boxes - some are checkbox and some are option boxes. I want to be able to place the totals, based on the values of these boxes, into bound text boxes on the main form, but only if a button is clicked. I was doing this by placing text boxes in the footer of the subform, setting the control source to something like: =Sum(IIf([mulher]='1',1,0)) and then copying each value to the main form. Is there a better way to do this from the main form itself?
Thanks,
Rob

 

[jjturner]

Hi Rob,

Yes, I already understand your original explanation - but I was hoping you could respond to my questions. I suspect there may be some design issues which need to be taken care of.

Your approach with the subform Footer totals and the main form text boxes is standard except for the fact that you state they are "bound". And my question is "Why are they bound?"

HTH,
John

 

[aquinachuva]

Hi John. The 'total' fields in the main form match variables in the main table and are based on summary data. In the past, the data were summarized on paper and then entered into a database (number of men, number of women, positive test results, negative test results). The continuous subform is meant to be a tool that can be used to enter individual values (sex=male, result=positive, etc.; sex=female, result=negative, etc.) for summarization... or not. Note, there are >40 different elements... Not sure if this makes sense.

Thanks,

Rob

 

[jjturner]

ok - just to be clear, the point I've been trying to get at is that you don't want to store calculated values in your table.

Calculations on your core dataset are what Queries are for. And your core dataset in this case is the data input on the subform.

Therefore, what I'm ultimately suggesting is that you should be using references to the subform footer totals as Control Sources for your main form controls (text boxes). In other words - get rid of the table storing calculated values - it's redundant and bad practice.

And this will save you from having to use code to populate the main form controls. They'll update automatically.

HTH,
John