VBA code to manipulate grouping on Reports

[jplink49]

I have a field [ACMT] on my report that holds analysis comments; it [ACMT] resides in the Footer section of my report. The problem is my analysis comments do not show on my report. I learned that the Footer section only displays values in the last record of the table (the record source of the report), as oppose to the Detail section, where a unique value would be displayed once per line from the table. (I can see this when I move the [ACMT] field into the Detail section;here I see the analysis comment no matter which record it's found). With my current report set-up, I'm only going to see analysis comments if ithey happen to be in the last record of the table.

My question is: How can I manipulate my report with VBA code or formula to get the [ACMT] in the Footer Section to display an analysis comment any record in the field?This is what I did:
1.Placed [ACMT] in the Detain section but made it invisible.
2.Then I defined a global variable for the report: DIM ACOMMENT AS STRING
3.Then in the detail code, put:
If Me.ACMT <> "" then
ACOMMENT = Me.ACMT
End If
4.Then in the Footer Section, added an unbound text field called, ACMT_Field
5. In Footer, add code thats says Me.ACMT_Field = ACOMMENT

It looks like this...

Option Compare Database

Dim ACOMMENT As String

Private Sub Detail1_Format (Cancel As Integer, FormatCount As Integer)
If Me.ACMT <> "" Then
ACOMMENT = Me.ACMT
End If
End Sub

Then in Footer..

Private Sub PageFooter2_Format (Cancel As Integer, FormatCount As Integer)
Me.ACMT = ACOMMENT
End Sub

Despite this I still don't see my comments. What is wrong?

 

[gemma-the-husky]

not quite

you can have several footers

page footer (one per page), report footer (one per report) group footer (one per group)

look at your report sorting/grouping (rightclick report header). If you are grouping (eg by section) then you can set each section to have a [group] section header/footer, and there you can put comments realtive to the current section.

i'm sure thats what you are looking for

 

[jplink49]

Gemma... so I should go to Reportheader-->Rightclick-->sorting/grouping--> then create a group footer?

 

[jplink49]

I figured it out. This is what I did to achieve what I wanted to do:

1. I went to the [ACMT_Field] field and entered this expression,
=Dlookup("[ACMT]", "Tablename", "Left([ACMT], 3)='The'")

2. I did this and my analysis comment "The actual concentration is..." pops up in my [ACMT_Field].