Format and Print Events in a Report

[AaronWestcott via AccessMonster.com]

I hope someone can anwer this question as it is bugging me and I can't seem
to get an answer out of the help files.

For each record that are to be printed in a report does the format event fire
first and then the print even and ony then does the report go to the next
record? The question comes from the following code found on Microsoft's site
in an article on creating dynamic crosstabs:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub

It seems to me that the code overwrites the values of the textboxes before
they print?

Thanks,

Aaron

 

[Marshall Barton]

I hope someone can anwer this question as it is bugging me and I can't seem
to get an answer out of the help files.

For each record that are to be printed in a report does the format event fire
first and then the print even and ony then does the report go to the next
record? The question comes from the following code found on Microsoft's site
in an article on creating dynamic crosstabs:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub

It seems to me that the code overwrites the values of the textboxes before
they print?

In general, the sections in a report are processed as many
times and in whatever order are needed to generate the
report (think about CanGrow, KeepTogether, etc). This can
be further confused when you preview a report and jump
around the pages.

One consequence ot the above is that the Print event(s) do
not always immediately follow the section's Format event(s).

Because the code you posted uses MoveNext in a futile
attempt to synchronize the recordset with the report's
record source, that code is not reliable. You need to find
another way to deal with your problem.

Perhaps some of Duane Hookom's stuff will give some ideas:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

http://www.access.hookom.net/Reports.htm

 

[AaronWestcott via AccessMonster.com]

Marshall,

Thanks for your reply. The code actually worked once I gave the report a
recordsource the same as the query that is used to create the recordset used
in the code.

That is what the example from Microsofts example does. Now the question is
why does that work?!? I do hope you have an idea (I sure don't) as I will
probably learn quite a bit from finding out.

Thanks again for you help.

Aaron

I hope someone can anwer this question as it is bugging me and I can't seem
to get an answer out of the help files.

[quoted text clipped - 30 lines]

It seems to me that the code overwrites the values of the textboxes before
they print?

In general, the sections in a report are processed as many
times and in whatever order are needed to generate the
report (think about CanGrow, KeepTogether, etc). This can
be further confused when you preview a report and jump
around the pages.

One consequence ot the above is that the Print event(s) do
not always immediately follow the section's Format event(s).

Because the code you posted uses MoveNext in a futile
attempt to synchronize the recordset with the report's
record source, that code is not reliable. You need to find
another way to deal with your problem.

Perhaps some of Duane Hookom's stuff will give some ideas:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

http://www.access.hookom.net/Reports.htm

 

[Marshall Barton]

Some of the people at MS that create the examples are not
experts in the nuances of all aspects of Access. Whoever
created the one you are refering to was apparently unaware
of the nonsequential nature of section event processing.

"Unreliable" does not mean that it will always fail. It
means that even if you do get it to work, it may fail in the
future when you change some seemingly unrelated property.

Are you certain you need to use the recordset?

Does the code really have to run in a detail section event?

Assuming the report controls are bound to the same query as
the recordset, why can't you just check the text box values?

It also strikes me as odd that you are going to so much
effort to make empty text boxes invisible or am I missing
the point of all this?
--
Marsh
MVP [MS Access]

Thanks for your reply. The code actually worked once I gave the report a
recordsource the same as the query that is used to create the recordset used
in the code.

That is what the example from Microsofts example does. Now the question is
why does that work?!? I do hope you have an idea (I sure don't) as I will
probably learn quite a bit from finding out.

I hope someone can anwer this question as it is bugging me and I can't seem
to get an answer out of the help files.

[quoted text clipped - 30 lines]

It seems to me that the code overwrites the values of the textboxes before
they print?

In general, the sections in a report are processed as many
times and in whatever order are needed to generate the
report (think about CanGrow, KeepTogether, etc). This can
be further confused when you preview a report and jump
around the pages.

One consequence ot the above is that the Print event(s) do
not always immediately follow the section's Format event(s).

Because the code you posted uses MoveNext in a futile
attempt to synchronize the recordset with the report's
record source, that code is not reliable. You need to find
another way to deal with your problem.

Perhaps some of Duane Hookom's stuff will give some ideas:
http://www.rogersaccesslibrary.com/...Generic Function To Concatenate Child Records'

http://www.access.hookom.net/Reports.htm

 

[AaronWestcott via AccessMonster.com]

I came accross a situation where it would have been helpful to make a
"dynamic crosstab report". Where I did not always know how many columns I
would have. So I started looking for examples and I found the one we are
discussing.

That is the only reason I got involved and the example, while interesting,
struck me as odd and hence the question.

I am not set on any one way of doing it. But having an approach that I can
count on working is important, I do not want to try this on another database
and have it not work.

Also, as I am fairly new to Access, <1 year, I try get to know why things are
working as opposed to just getting them to work.

Some of the people at MS that create the examples are not
experts in the nuances of all aspects of Access. Whoever
created the one you are refering to was apparently unaware
of the nonsequential nature of section event processing.

"Unreliable" does not mean that it will always fail. It
means that even if you do get it to work, it may fail in the
future when you change some seemingly unrelated property.

Are you certain you need to use the recordset?

Does the code really have to run in a detail section event?

Assuming the report controls are bound to the same query as
the recordset, why can't you just check the text box values?

It also strikes me as odd that you are going to so much
effort to make empty text boxes invisible or am I missing
the point of all this?

Thanks for your reply. The code actually worked once I gave the report a
recordsource the same as the query that is used to create the recordset used

[quoted text clipped - 28 lines]

 

[Marshall Barton]

The code you posted is not what I would expect for a dynamic
crosstab. First, I see no need to loop through the records
in the recordset. Instead it would loop through the
recordset's Fields collection.

Second, it would be placed in the report's Open event, not
in a detail section event.

This would mean that you do not need to synchronize the
recordset records with the report's record source so that
psrt of the problem would not exist.

OTOH, maybe you have a more complex situation than what you
have explained so far.

Did you take a look at Duane's crosstab report examples?
--
Marsh
MVP [MS Access]

I came accross a situation where it would have been helpful to make a
"dynamic crosstab report". Where I did not always know how many columns I
would have. So I started looking for examples and I found the one we are
discussing.

That is the only reason I got involved and the example, while interesting,
struck me as odd and hence the question.

I am not set on any one way of doing it. But having an approach that I can
count on working is important, I do not want to try this on another database
and have it not work.

Also, as I am fairly new to Access, <1 year, I try get to know why things are
working as opposed to just getting them to work.

Some of the people at MS that create the examples are not
experts in the nuances of all aspects of Access. Whoever
created the one you are refering to was apparently unaware
of the nonsequential nature of section event processing.

"Unreliable" does not mean that it will always fail. It
means that even if you do get it to work, it may fail in the
future when you change some seemingly unrelated property.

Are you certain you need to use the recordset?

Does the code really have to run in a detail section event?

Assuming the report controls are bound to the same query as
the recordset, why can't you just check the text box values?

It also strikes me as odd that you are going to so much
effort to make empty text boxes invisible or am I missing
the point of all this?

Thanks for your reply. The code actually worked once I gave the report a
recordsource the same as the query that is used to create the recordset used

[quoted text clipped - 28 lines]

http://www.access.hookom.net/Reports.htm

 

[AaronWestcott via AccessMonster.com]

The article that I was reading is located at
http://support.microsoft.com/kb/328320. I followed it step by step to get
the report to run.

I did take a look at Duanes stuff and am going to try and implement that as
usually my columns are years. So using his stuff as a template may work out
quite well. It is also alot simpler!

The code you posted is not what I would expect for a dynamic
crosstab. First, I see no need to loop through the records
in the recordset. Instead it would loop through the
recordset's Fields collection.

Second, it would be placed in the report's Open event, not
in a detail section event.

This would mean that you do not need to synchronize the
recordset records with the report's record source so that
psrt of the problem would not exist.

OTOH, maybe you have a more complex situation than what you
have explained so far.

Did you take a look at Duane's crosstab report examples?

I came accross a situation where it would have been helpful to make a
"dynamic crosstab report". Where I did not always know how many columns I

[quoted text clipped - 35 lines]

 

[Marshall Barton]

Well, I took a look at that article and there are more
things wrong with it than I imagined. It is ridiculously
complicated and uses a several highly dubious concepts.
IMO, it would be a gross waste of time and brain cells to
use it as a starting point if you can not use Duane's much
simpler approach.
--
Marsh
MVP [MS Access]

The article that I was reading is located at
http://support.microsoft.com/kb/328320. I followed it step by step to get
the report to run.

I did take a look at Duanes stuff and am going to try and implement that as
usually my columns are years. So using his stuff as a template may work out
quite well. It is also alot simpler!

The code you posted is not what I would expect for a dynamic
crosstab. First, I see no need to loop through the records
in the recordset. Instead it would loop through the
recordset's Fields collection.

Second, it would be placed in the report's Open event, not
in a detail section event.

This would mean that you do not need to synchronize the
recordset records with the report's record source so that
psrt of the problem would not exist.

OTOH, maybe you have a more complex situation than what you
have explained so far.

Did you take a look at Duane's crosstab report examples?

I came accross a situation where it would have been helpful to make a
"dynamic crosstab report". Where I did not always know how many columns I

[quoted text clipped - 35 lines]

http://www.access.hookom.net/Reports.htm

 

[AaronWestcott via AccessMonster.com]

Thank you for all your help. I truly appreciate you saving me time now and
trouble down the road.

Well, I took a look at that article and there are more
things wrong with it than I imagined. It is ridiculously
complicated and uses a several highly dubious concepts.
IMO, it would be a gross waste of time and brain cells to
use it as a starting point if you can not use Duane's much
simpler approach.

The article that I was reading is located at
http://support.microsoft.com/kb/328320. I followed it step by step to get

[quoted text clipped - 25 lines]