Incorrect Data on Last Page

[Guest]

Ok, I have an unusual problem with a report. I'll try to explain. I have a
main report that prints some header/footer data with no problem. In the
detail section of the report is a subreport that has a group on StockNum.
The detail section of the sub report has serial#s but they are not printed in
the detail section. Instead, I'm stringing them together in the subreport
Detail_Format event and then printing that string in the subreport footer.
Everything works beautifully until it prints the last page (only if more than
one page). On the last page, the serial# string that gets printed on the sub
report's 1st group footer is the serial # string of the last group footer of
the page - instead of the 1st serial# string of the page. I know this may be
confusing so here's an example.

last page of report:

1st group footer prints
Stock# abcd
Serial# 9999

2nd group footer prints
Stock# efgh
Serial# 2222

3rd (and last of page/report) group footer prints
Stock# ijkl
Serial# 9999

The serial# of the 1st group footer should be 1111 not 9999 (the last
serial#). This occurs everytime, no matter what the data.

If I open the subreport by itself, the serial# are correct for each footer.

Sorry if this doesn't make sence and any help is greatly appreciated.

 

[Duane Hookom]

"I'm stringing them together in the subreport Detail_Format event and then
printing that string in the subreport footer". I expect this is your issue.
I assume you want to concatenate "child" values together into a single
expression. If this is the case, there is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Guest]

Duane,
Thanks for the response. I had already looked at your example and I don't
think that addresses the issue.

My "stringing" works 99% of the time. Every row on every page works, except
the first row of the last page. It's printing the last row of the last page
instead of the first row. I think if the "stringing" was wrong, it would be
incorrect on all rows. Also, it is correct when I just print the sub form by
itself.

I'm completely at a loss here. Are there any know issues with Access having
this problem? Any more help is greatly appreciated.

--
Thanks in advance
Tom

"I'm stringing them together in the subreport Detail_Format event and then
printing that string in the subreport footer". I expect this is your issue.
I assume you want to concatenate "child" values together into a single
expression. If this is the case, there is a generic concatenate function at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Duane Hookom]

I expect your On Format is running multiple times so I always avoid doing
aggregation in code in a section format (or even print) event.

Maybe you should tell us what you want to do rather than referring to how
you have attempted to do it.

--
Duane Hookom
MS Access MVP

Duane,
Thanks for the response. I had already looked at your example and I don't
think that addresses the issue.

My "stringing" works 99% of the time. Every row on every page works,
except
the first row of the last page. It's printing the last row of the last
page
instead of the first row. I think if the "stringing" was wrong, it would
be
incorrect on all rows. Also, it is correct when I just print the sub form
by
itself.

I'm completely at a loss here. Are there any know issues with Access
having
this problem? Any more help is greatly appreciated.

 

[Guest]

Duane,
Thanks again for your time.

You're correct, the detail_format fires multiple times, once per record, as
expected. Here is what I'm doing there:
If txtSerNums = "" Then
txtSerNums = Serial_Num
Else
txtSerNums = txtSerNums & ", " & Serial_Num
End If

I also have code in the GroupHeader0_Format event that clears out txtSerNums
when it starts a new group:
txtSerNums = ""

txtSerNums (a textbox) "lives" in the Footer of the report, so what's
happening (correctly) is everytime a new record is "processed" by the report
engine, it adds the serial# to txtSerNums. Then the string is displayed in
the footer, similar to a counter.

So my endstate is to diplay a string of serial#'s that are in the group.
This works fine for ALL but one group. No matter how many pages, no matter
how many records, only the 1st group of the last page is incorrect.

I hope this clears things up. Sorry if it doesn't.

 

[Duane Hookom]

Now that we know what you are attempting to do, consider using the generic
concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You should be able to use a text box in your footer with a control source
like:
=Concatenate("SELECT Serial_Num FROM tblYourTable WHERE GroupField='" &
[GroupField] & "'")

 

[Guest]

Duane,
Well, once again, you solved the problem. Your solution worked. Thank you
very much. I'm still at a loss why the other way did not work...only on the
last page. Nonetheless, it works and that's what matters. Thanks again and
have a good one.

--
Thanks in advance
Tom

Now that we know what you are attempting to do, consider using the generic
concatenate function found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

You should be able to use a text box in your footer with a control source
like:
=Concatenate("SELECT Serial_Num FROM tblYourTable WHERE GroupField='" &
[GroupField] & "'")