Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-26-2007, 08:54 AM   #1
chok120
Registered User
 
Join Date: Jun 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
chok120 is on a distinguished road
Looping fields within a report

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

chok120 is offline   Reply With Quote
Old 06-26-2007, 11:45 AM   #2
lark_tilly
Registered User
 
Join Date: Jun 2007
Posts: 67
Thanks: 0
Thanked 0 Times in 0 Posts
lark_tilly is on a distinguished road
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?
lark_tilly is offline   Reply With Quote
Old 06-27-2007, 05:42 AM   #3
chok120
Registered User
 
Join Date: Jun 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
chok120 is on a distinguished road
Quote:
Originally Posted by lark_tilly View Post
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

chok120 is offline   Reply With Quote
Old 06-27-2007, 07:56 AM   #4
lark_tilly
Registered User
 
Join Date: Jun 2007
Posts: 67
Thanks: 0
Thanked 0 Times in 0 Posts
lark_tilly is on a distinguished road
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.
Attached Files
File Type: zip chok120.zip (17.8 KB, 78 views)
lark_tilly is offline   Reply With Quote
Old 06-27-2007, 10:35 AM   #5
chok120
Registered User
 
Join Date: Jun 2007
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
chok120 is on a distinguished road
Quote:
Originally Posted by lark_tilly View Post
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
Attached Files
File Type: zip Database.zip (40.8 KB, 67 views)

Last edited by chok120; 06-27-2007 at 10:42 AM.
chok120 is offline   Reply With Quote
Old 06-28-2007, 08:31 AM   #6
lark_tilly
Registered User
 
Join Date: Jun 2007
Posts: 67
Thanks: 0
Thanked 0 Times in 0 Posts
lark_tilly is on a distinguished road
Take a look at this example.
It does what you want.
Attached Files
File Type: zip chok120-1.zip (12.6 KB, 76 views)

lark_tilly is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you refer to fields on a sub report Jon123 General 1 09-25-2005 05:57 AM
Report Printing Some Fields With Black Bkgrd Llobid Reports 3 09-21-2004 04:46 AM
How many fields can I put in a report StevenJ Reports 2 05-16-2002 11:41 AM
Creating report from a query in which number of fields vary from time to time Nancy Reports 2 11-01-2000 01:28 PM




All times are GMT -8. The time now is 10:46 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World