For each control on a report

[JEA]

I want to do a For each loop on a column of textboxes on a report.
The number of boxes (rows) is not constant so I was hoping to achieve something like this:

Public Function SumCalculatedColumn(columnName As String)
    Dim ctl As Control
    Dim rpt As String
    Dim runningTotal As Int
    rpt = "[Reports]![Service Schedule Report]"


    [COLOR="red"]For Each ctl In rpt.[StaffPIN] [/COLOR]   'Where [StaffPIN] is the name of a column in my report
        [COLOR="red"]runningTotal = runningTotal + workedHours(ctl, rpt.columnName)
    Next ctl[/COLOR]

SumCalculatedColumn = runningTotal

It's the red text I'm not sure about. Is it possible?

Hopefully it will be calculating workedHours() using the value in each row of the staffPIN column and the name of the column 'columnName'. (ie same 'columnName' each time but different values for StaffPIN corresponding to each row of my report)

Phew, hope that makes sense

It's trying to solve a problem I posted on a thread here.

 

[mikela]

Hey!

Not sure what you want to do, but if you want to sum all the values for a row, check this out, it may help.

http://support.microsoft.com/kb/328320


Hope this helps!

 

[JEA]

Thanks for the reply, but I want to sum all the values in a column not row. The values in the column are calculated using workedHours(), according to Access help, I can't just sum them. I have to calculate them again and then sum them in the same statement.

The workedHours() function takes it's perameters from a text box in it's row (StaffPIN), and a text box at the top of it's column (ColumnName).

So in the code above, I'm trying perform workedHours() for each [StaffPIN] (row) on the form.

In the link above there's a diagram of my report and probably a better example of what I'm trying to achieve.

 

[Rabbie]

Provided all the entries in the column come from the same source field Then you should be able to use something like

= Sum(datafield)

to populate the total field.

 

[boblarson]

As Rabbie has said you can use

=Sum(Nz([datafield],0))

to sum a total column and, if you put the same within any group footers it will sum the group. The thing to be aware of is that you have to put the field name within square brackets and you have to make sure that your text box that houses the original field is not named the same as the text box. So if your data field is named datafield then make sure the text box is named txtdatafield or something like that and then you use the FIELD name in the sum code.

I would use the Nz function too (as shown above) to make sure that it doesn't fail because of null values, which it will if there are any null values within the column.