Calc in VBA per group to show in Report

[JuniorWoodchuck24]

I have a report that pulls data in from a table and query. The query runs a product calc and what I'm trying to do is to put a function in VBA to show the product sum per group. The issue I'm having is that it finds the first groups answer and repeats it through the list of groups. For example:
Group A total = 1000
Group B total = 200
Group C total = 100

So the report shows:
Group A total = 1000
Group B total = 1000
Group C total = 1000

Me.GroupTotal = IIf(Group="A", ProductSumA, IIf(Group="B", ProductSumB, IIf(Group="C", ProductSumC,"")))

Is what I typed in VBA, and if I type the same thing in the text box's ControlSource it works. Only issue is that my IIf statement is much bigger than the example and if I type it in the ControlSource is gives me a error saying text is too long to edit.

Is there a certain function that I need to use to have it continually check the Group header?

 

[JuniorWoodchuck24]

Is there a way to code in the report's module to allow for the same summing/count/etc in the group footer? It seems that when you go to the report's module it takes the first group's calculation and just places it into all of the next groups. I'm going over the limit when I try to use the Control Source of the text box in the report's design view. This is because I have to meet several requirements. In VBA I can break the code up and I'm not limited to using one function.

 

[Rich]

Why can't you just put a Sum in the group footer?

 

[JuniorWoodchuck24]

ProductSum isn't a function that you can select so can't place that in there. I found a way around the problem, which probably isn't the best way but it works.