Programatically setting the controlsource property of a summary field in a report

[Garudzo]

Hi all

I am dynamically creating a report in Access using a dynamic query. I use a record set to get the field names of a resulting query to set the controlsource property fields of the report fields and it works fine untill I wand to do the same for summary fields which are field4 and field5. These are displaying error in the report fields themeselves and not when I run the code any suggestions

If Not rs.EOF Then
           
           Field1.ControlSource = rs.Fields(2).Name
           Status.ControlSource = rs.Fields(1).Name
           Field2.ControlSource = rs.Fields(3).Name
           Field3.ControlSource = "=([" & rs.Fields(3).Name & "]-[" & rs.Fields(2).Name & "])/[" & rs.Fields(2).Name & "]"
           Field4.ControlSource = "=Sum([" & rs.Fields(2).Name & "])"
           Field5.ControlSource = "=Sum([" & rs.Fields(3).Name & "])"

[MMcCarthy]

you can't do this

Field4.ControlSource = "=Sum([" & rs.Fields(2).Name & "])"

because the recordset can only see one record at a time.

You need to create a second recordset which sums those two fields.

Mary