Test for EOF in Report Group Footer

[mredmond13]

I have a very vanilla application. I run a query against a table to select
the records I want. I use the query to feed a report. There is absolutely no
code in report. Pure and simple Access.

The data groups on a single field. On all group breaks except the last one, I
print some labels in the group footer area. I do not want to print those
labels in the footer after the last group prints. I should be able to test
for EOF or last group and then set the labels VISIBLE atrribute to FALSE, but
I cannot seem to find the right syntax.

I appreciate any help I can get.

 

[Allen Browne]

Reports have a forward scrolling records (snapshot), so there's no built-in
"check the next record" in reports.

One way to approach it would be a put a text box in the Report Header
section, with an expression that gets the ID for the last record. You can
hide the text box, but you can now compare the ID of your records with this
text box to see if you've reached the last record (that is, it's the same
primary key value.)

There are several pitfalls with that, e.g. the report may be opened with a
filter so that the last record is not the one you expect. Unfortunately,
Access does not maintain the FilterOn property of the report correctly, so
although it would be possible to write code that selects the last record
from the RecordSource with a filter applied, you have no reliable way to
know whether the expression in the report's Filter property is applied or is
an artifact from a previous filtering.

 

[mredmond13 via AccessMonster.com]

Allen,

Thanks for the suggestion.

When you group on a field, isn't there an event that occurs that cause the
footers of the previous group to be formatted and printed and then the
headers of the next group to be formatted and printed? Access has to have
tried to read the next record and determined that there were no more, and the
group break event was triggered. Is there no way to code into that event and
determine that there are no more records so this must be the last grouping?

For such a simple application, this is getting rather technical, isn't it.

 

[John Spencer]

I think this may work for you.

Add a textbox to the detail section of your report.
Name: txtCounter
Control Source: =1
Running Sum: Over All

Add a textbox to the group footer
Name: txtTotalRecords
Control Source: =Count(*)

Now you can test if the last record has printed with
If Me.TxtCounter = me.txtTotalRecords Then
'Not the last record so, show stuff
ELSE
'Hide Stuff
END IF

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[mredmond13 via AccessMonster.com]

John, where do you put this code? What event or what field? Is it attached to
the detail section textbox?

I think this may work for you.

Add a textbox to the detail section of your report.
Name: txtCounter
Control Source: =1
Running Sum: Over All

Add a textbox to the group footer
Name: txtTotalRecords
Control Source: =Count(*)

Now you can test if the last record has printed with
If Me.TxtCounter = me.txtTotalRecords Then
'Not the last record so, show stuff
ELSE
'Hide Stuff
END IF

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a very vanilla application. I run a query against a table to select
the records I want. I use the query to feed a report. There is absolutely no

[quoted text clipped - 7 lines]

I appreciate any help I can get.

 

[John Spencer]

I would put it into the group footer that you want to suppress.

Probably use the format event to cancel the formatting of the section.
So something like the following might work for you. Watch out for line
wrapping.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.TxtCounter = me.txtTotalRecords)
End Sub


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, where do you put this code? What event or what field? Is it attached to
the detail section textbox?

I think this may work for you.

Add a textbox to the detail section of your report.
Name: txtCounter
Control Source: =1
Running Sum: Over All

Add a textbox to the group footer
Name: txtTotalRecords
Control Source: =Count(*)

Now you can test if the last record has printed with
If Me.TxtCounter = me.txtTotalRecords Then
'Not the last record so, show stuff
ELSE
'Hide Stuff
END IF

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a very vanilla application. I run a query against a table to select
the records I want. I use the query to feed a report. There is absolutely no

[quoted text clipped - 7 lines]

I appreciate any help I can get.

 

[mredmond13 via AccessMonster.com]

Thanks. I'll give it a shot.

I would put it into the group footer that you want to suppress.

Probably use the format event to cancel the formatting of the section.
So something like the following might work for you. Watch out for line
wrapping.

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
Cancel = (Me.TxtCounter = me.txtTotalRecords)
End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John, where do you put this code? What event or what field? Is it attached to
the detail section textbox?

[quoted text clipped - 26 lines]

[quoted text clipped - 7 lines]
I appreciate any help I can get.