Looping fields within a report

[chok120]

Hi

How can I loop rows within a report to catch all the rows through VBA code. I want to assign the rows to a single string variable.

I've tried this but it doesn't work.

For Each varItem In Me.Report
strBodyText = strBodyText & Me.Report.ItemData(varItem)
Next varItem

Is it possible to loop within a report to catch all the data and assign to a string variable?

Thanks

 

[lark_tilly]

You can place code in the reports Detail_Format event to capture the data and assign it to a variable. You can then display the variable in the Page Footer or Report Footer by placing code in those events to assign the variable to a field in that section of the report. Is that what you have in mind?

 

[chok120]

You can place code in the reports Detail_Format event to capture the data and assign it to a variable. You can then display the variable in the Page Footer or Report Footer by placing code in those events to assign the variable to a field in that section of the report. Is that what you have in mind?

Yes that is what I have in mind, what is the relevant vba code to capture the data and assign it to a variable?

Thanks

 

[lark_tilly]

The attachment has one table with sample data.
The report displays the detail and then the concatenated string in both the PageFooter and the ReportFooter. I've commented the code so you can follow along with what I'm doing.

Hope this helps.

 

[chok120]

The attachment has one table with sample data.
The report displays the detail and then the concatenated string in both the PageFooter and the ReportFooter. I've commented the code so you can follow along with what I'm doing.

Hope this helps.

Thanks for this Tilly. However I've tried doing exactly the same in access 2007 and it does not work.

If you've got 2007, please take a look at the attached file

The code behind is:

-------------------------------------------------
Option Compare Database
Dim strLongString As String

Private Sub Button_Click()
MsgBox strLongString
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
strLongString = strLongString + [namer]
MsgBox "Routine has run"
End Sub
----------------------------------------

When the button is clicked the msgbox is empty although the report has 3 rows with a namer data item

Is there a command to run sub detail_format routine? Something along the lines of:

Private Sub Button_Click()
[ run sub-routine detail format ]
MsgBox strLongString
End Sub

This would ensure the Detail_Format routine is run before the display box as I don't think it is running on report load as I never see the "Routine has run" test debug

Regards

 

[lark_tilly]

Take a look at this example.
It does what you want.