Change Report Footer control's controlsource in VBA

[cyberwolf0000 via AccessMonster.com]

I have been trying to figure out how to change the controlsource of a control
on a report footer. I know how to do it when it is a straight up field but
what if you have text that needs to be displayed. Basically I created a
report using the wizrd and it put the usual sum field in the group footer.
It also put a control that has this line in it

="Summary for " & "'RNOperatorID' = " & [RNOperatorID] & " (" & Count(*) &
IIf(Count(*)=1,"detail record","detail records") & ")"

I have been trying to figure out how this would get formatted in VBA so that
when I change that groups control source, I can change the sum field (which I
can do not problem) and this other field to match that groups control source?

TIA

--
James B Gaylord
For the Wolf comes the strength of the Pack,
For the Pack comes the strength of the Wolf,
-R. Kipling
Office 2003 on Win XP SP2

 

[Marshall Barton]

I have been trying to figure out how to change the controlsource of a control
on a report footer. I know how to do it when it is a straight up field but
what if you have text that needs to be displayed. Basically I created a
report using the wizrd and it put the usual sum field in the group footer.
It also put a control that has this line in it

="Summary for " & "'RNOperatorID' = " & [RNOperatorID] & " (" & Count(*) &
IIf(Count(*)=1,"detail record","detail records") & ")"

I have been trying to figure out how this would get formatted in VBA so that
when I change that groups control source, I can change the sum field (which I
can do not problem) and this other field to match that groups control source?

You can only change a control source in the report's Open
event procedure. The code would look like:

strfieldname = "Somefield" 'however you set the field name
Me.GroupLevel(x).ControlSource = strfieldname
Me.groupfootertextbox.ControlSource = _
"=""Summary for "" & "'" '" & strfieldname & "' = " _
& strfieldname & """ ("" & Count(*) & IIf(Count(*)=1, _
"""detail record"",""detail records"") & "")"

But keeping track of the embedded quotes and ampersands can
make anyone crosseyed.