By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,473 Members | 3,504 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,473 IT Pros & Developers. It's quick & easy.

Report runs code when print previewing

P: n/a
When I open a certain report, it runs some code that generates the records
that will be displayed in that report. This works fine. When I go to print
preview the report it appears that the code is run again? This is causing
certain error problems.

1 Why does the code run for a print preview when the report already
exists?

2 What command do I use in code to find out if a report (or any other
object for that matter) is already open/loaded?

Thanks

Gord
Aug 2 '08 #1
Share this Question
Share on Google+
11 Replies


P: n/a
Gord,

What do you mean by "the report already exists?" If you can't see it in
print preview, it doesn't exist. If you have printed it before, it prints
and closes.

Bonnie
http://www.dataplus-svc.com

Gord wrote:
>When I open a certain report, it runs some code that generates the records
that will be displayed in that report. This works fine. When I go to print
preview the report it appears that the code is run again? This is causing
certain error problems.

1 Why does the code run for a print preview when the report already
exists?

2 What command do I use in code to find out if a report (or any other
object for that matter) is already open/loaded?

Thanks

Gord
--
Message posted via http://www.accessmonster.com

Aug 3 '08 #2

P: n/a
I 'open' the report from the navigation pane and see it in report view. The
records shown in the report are derived from the code I have written and are
displayed just fine. It is at this point I refer to the report as
'existing'. Once in report view, when I click print preview it would appear
my code is run again. (don't understand why?). When the code runs it
creates some errors because the report is already open (exists).

Thanks,

Gord
"bhicks11 via AccessMonster.com" <u44327@uwewrote in message
news:88179f7fcbbd9@uwe...
Gord,

What do you mean by "the report already exists?" If you can't see it in
print preview, it doesn't exist. If you have printed it before, it prints
and closes.

Bonnie
http://www.dataplus-svc.com

Gord wrote:
>>When I open a certain report, it runs some code that generates the records
that will be displayed in that report. This works fine. When I go to
print
preview the report it appears that the code is run again? This is causing
certain error problems.

1 Why does the code run for a print preview when the report already
exists?

2 What command do I use in code to find out if a report (or any other
object for that matter) is already open/loaded?

Thanks

Gord

--
Message posted via http://www.accessmonster.com

Aug 3 '08 #3

P: n/a
Maybe I am missing something Gord (which is highly possible!). Are you
hitting print preview from File - Print Preview? When you open it the first
time you are in Preview mode. By the way, I am in Access 2003. Are you in
2007?

Bonnie
http://www.dataplus-svc.com

Gord wrote:
>I 'open' the report from the navigation pane and see it in report view. The
records shown in the report are derived from the code I have written and are
displayed just fine. It is at this point I refer to the report as
'existing'. Once in report view, when I click print preview it would appear
my code is run again. (don't understand why?). When the code runs it
creates some errors because the report is already open (exists).

Thanks,

Gord
>Gord,
[quoted text clipped - 20 lines]
>>>
Gord
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 3 '08 #4

P: n/a
When you preview a report, the code is still alive. Access doesn't
prepare a static "copy" of the report which you can then just browse.
For example, whenever you move to a new page in preview, the Page
event always will fire, if you have one. So you shouldn't assume the
report is produced in a single pass, as in other languages, and base
your reporting strategies on that assumption. The user can page
forwards and backwards at will, and the code will continue to fire
multiple times on page breaks, control breaks etc. And be very
careful with referring to global variables in reports; if the user
starts the report and leaves it open in preview, or executing, then
goes off on some task, say data entry, that changes the global
variable, the report will be compromised. It will have picked up
different values of the same global variable, depending on the instant
that the reference was made.

Terry
Aug 3 '08 #5

P: n/a
I'm using 2007 and am self teaching. The next reply from TerryBell would
indicate that the code is run if you take too deep a breath. (I'm hitting
the print preview from the 'Home' tab and selecting 'Print Preview' from the
'Views' control.) If this is the way it has to work, then I can write
around the problem if I can determine if the report is already 'open' or
'loaded' (depending on the correct terminology). Do you know how to
determine if a report is already open?

Thanks,

Gord

"bhicks11 via AccessMonster.com" <u44327@uwewrote in message
news:881880facb1c7@uwe...
Maybe I am missing something Gord (which is highly possible!). Are you
hitting print preview from File - Print Preview? When you open it the
first
time you are in Preview mode. By the way, I am in Access 2003. Are you
in
2007?

Bonnie
http://www.dataplus-svc.com

Gord wrote:
>>I 'open' the report from the navigation pane and see it in report view.
The
records shown in the report are derived from the code I have written and
are
displayed just fine. It is at this point I refer to the report as
'existing'. Once in report view, when I click print preview it would
appear
my code is run again. (don't understand why?). When the code runs it
creates some errors because the report is already open (exists).

Thanks,

Gord
>>Gord,
[quoted text clipped - 20 lines]
>>>>
Gord

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 3 '08 #6

P: n/a
Terry,

Thanks for the info. How can I determine in code if a particular report (or
any object) is already open?

Thanks,

Gord
<te**********@gmail.comwrote in message
news:55**********************************@b30g2000 prf.googlegroups.com...
When you preview a report, the code is still alive. Access doesn't
prepare a static "copy" of the report which you can then just browse.
For example, whenever you move to a new page in preview, the Page
event always will fire, if you have one. So you shouldn't assume the
report is produced in a single pass, as in other languages, and base
your reporting strategies on that assumption. The user can page
forwards and backwards at will, and the code will continue to fire
multiple times on page breaks, control breaks etc. And be very
careful with referring to global variables in reports; if the user
starts the report and leaves it open in preview, or executing, then
goes off on some task, say data entry, that changes the global
variable, the report will be compromised. It will have picked up
different values of the same global variable, depending on the instant
that the reference was made.

Terry

Aug 3 '08 #7

P: n/a
Did you tell us where the code exists?
In a standard module?
In the report's module? (if so is it event code? what event?)
In a class module?

Did you tell us what the code is? Exactly what is it?

How about posting this code and any related code such a procedure
which is called from the code?

What are the "error problems"?

In fact, code is not run once if you take too deep a breath, let alone
twice. It may be run twice if you have placed it in an event that
occurs twice, or more.

CurrentProject.AllReports("Supplier Phone Book").IsLoaded is true if
the "Supplier Phone Book" report is open, false if it is not, error
2467 if there is no report named "Supplier Phone Book", (Access
2007) .
On Aug 2, 10:52*pm, "Gord" <x1gor...@telus.netwrote:
I'm using 2007 and am self teaching. *The next reply from TerryBell would
indicate that the code is run if you take too deep a breath.
Aug 3 '08 #8

P: n/a
Gord, I've retracted my previous post, it was part right and part
wrong.
One thing is true, and that is when you preview a report, Access
prepares the first page, and doesn't necessarily prepare the next page
until you page down; so you shouldn't assume all the code is executed
when the user opens a report in preview mode. I think (not certain!)
the point at which code is executed is further complicated by the
existence or not of totals in control breaks and other report
properties.
You're best off not trying to predict this and design your report so
it doesn't have any dependencies on when the code is executed.

I'm curious as to why you need to determine whether or not the report
is open?

But if you must, here's a function that will tell you.

Function ReportOpen(ReportName) As Boolean
Dim rpt As Report
For Each rpt In Reports
If rpt.Name = ReportName Then
ReportOpen = True
Exit Function
End If

Next rpt
End Function

eg
ReportOpen("MyReport1") will return true if the report is open, false
if it's not. Note, "Open" can be open in different states - normal,
print preview, design etc.

Aug 3 '08 #9

P: n/a
I'm using the report's 'On load' event which runs a small bit of code that
calls a procedure that does most of the work.

Private Sub Report_Load()

MyModule.MyProcedure

Me.RecordSource = "MyJustCreatedTable"

End Sub

As you see the record source for the report will be a table created by the
sub procedure. The way I want this to work and the way I have written the
sub, is to delete the table first (if it exists) and then create the table
and populate with records as determined by the rest of the code. The
portion of code in the sub that deletes the table is as follows:

Dim chkForExistTbl As TableDef
For Each chkForExistTbl In dBase.TableDefs
If chkForExistTbl.Name = "MyJustCreatedTable" Then _
dBase.TableDefs.Delete "MyJustCreatedTable"
Next

When I open the report from the navigation pane, the report opens just fine
with the just created table and records. Everything A O.K. While the
report is open in 'Report View' and I try to go to 'Print PreView' it would
seem my code runs again and generates the following error:

Run Time Error '3211'

The database engine could not lock table 'MyJustCreatedTable' because it
is already in use by another person or process.
When I click Debug, it is stalled on the line that is trying to delete the
table:

dBase.TableDefs.Delete "MyJustCreatedTable"

I assume this happens because the report itself is the process that is
'using' the table i.e. the table is it's recordsource.

In the above 'On load' event I added the following as the very first line of
code:

Me.RecordSource = ""

in the hopes that when my code runs while going into print preview, it
wouldn't see the table as being 'in use' and would allow my code to delete
the table. Again, this code works fine when opening the report from the
navigation pane. Everything A O.K. But now when I try to go into print
preview I get the following error:

Run time error '2191'

You can't set the Record Source property in print preview or after
printing has started.

So it would seen that print preview insists on the report as having it's
recordsource property set right at the get go.

My next thought was to make the first bit of code in the event check to see
if the report was loaded or not. (I had hoped (fingers crossed) that the
report wouldn't report itself as being loaded right at the beginning of the
event when opened from the navigation pane.) This is where I used a
modified version of your example (thank you) below as the first line of
code in the event:

If CurrentProject.AllReports("MyReport").IsLoaded is true then exit sub

My thinking was that if the report was indicated as being loaded, then I
wouldn't bother running any of the code since this would be the event
triggered by going into print preview. Of course this doesn't work as the
report is indicated as being loaded before my code runs so I can't
distinguish from opening the report from the navigation pane or going into
print preview.

In either case of whether I have the report's recordsource set or not, after
I clear the error dialog with 'End' I go into print preview just fine. So I
could handle the problem with an error handler and suppress the error but I
am trying to be concise about this and not generate an error in the first
place.

I'm teaching myself Access from books/this newsgroup and I may be making an
incorrect assumption. As I understand, a report needs to have a
recordsource from an existing table (or query, which is itself based on
existing tables). This is why I create the table in the first place. I also
delete it and recreate it every time the report is run since the underlying
data (which is coming from a query) may have changed. Perhaps this an
incorrect assumption and I can create a temporary 'virtual' table within my
code to use as a recordsource for the report so I don't have to delete
anything?

Thanks,

Gord
"lyle fairfield" <ly************@gmail.comwrote in message
news:9c**********************************@b1g2000h sg.googlegroups.com...
Did you tell us where the code exists?
In a standard module?
In the report's module? (if so is it event code? what event?)
In a class module?

Did you tell us what the code is? Exactly what is it?

How about posting this code and any related code such a procedure
which is called from the code?

What are the "error problems"?

In fact, code is not run once if you take too deep a breath, let alone
twice. It may be run twice if you have placed it in an event that
occurs twice, or more.

CurrentProject.AllReports("Supplier Phone Book").IsLoaded is true if
the "Supplier Phone Book" report is open, false if it is not, error
2467 if there is no report named "Supplier Phone Book", (Access
2007) .
On Aug 2, 10:52 pm, "Gord" <x1gor...@telus.netwrote:
I'm using 2007 and am self teaching. The next reply from TerryBell would
indicate that the code is run if you take too deep a breath.

Aug 3 '08 #10

P: n/a
Thanks for the info. If you look at the reply above to Lyle Fairfield I've
written more than probably anyone wants to read if you're interested in the
whole problem I'm dealing with.

Basically, I was trying to determine if I could tell if a report was loaded
or not right at the beginning of the report's 'On load' event. I was trying
to see if I could distinguish between if the report was just being loaded
from the navigation pane, or was already loaded and was being sent to print
preview. Of course the report is seen as being loaded right at the
beginning of the event so my code is too late to see if it was being loaded
from the navigation pane.

I'm getting errors when I go to print preview because my code runs again and
tries to delete the reports recordsource table.

Thanks for the function suggestion anyway,

Gord

<te**********@gmail.comwrote in message
news:26**********************************@b38g2000 prf.googlegroups.com...
Gord, I've retracted my previous post, it was part right and part
wrong.
One thing is true, and that is when you preview a report, Access
prepares the first page, and doesn't necessarily prepare the next page
until you page down; so you shouldn't assume all the code is executed
when the user opens a report in preview mode. I think (not certain!)
the point at which code is executed is further complicated by the
existence or not of totals in control breaks and other report
properties.
You're best off not trying to predict this and design your report so
it doesn't have any dependencies on when the code is executed.

I'm curious as to why you need to determine whether or not the report
is open?

But if you must, here's a function that will tell you.

Function ReportOpen(ReportName) As Boolean
Dim rpt As Report
For Each rpt In Reports
If rpt.Name = ReportName Then
ReportOpen = True
Exit Function
End If

Next rpt
End Function

eg
ReportOpen("MyReport1") will return true if the report is open, false
if it's not. Note, "Open" can be open in different states - normal,
print preview, design etc.

Aug 3 '08 #11

P: n/a
Yes, we can't set a report's record-source after the report has opened
in Print Preview or Print Mode.
Yes, when the Report is Open in report mode and using MyCreatedTable
as its record-source, we cannot delete MyCreatedTable.
Yes, when the Report changes from Report-View to Print-Preview Mode
its On-Load event code is run again.

What's the solution? My guess is that the code which creates
"MyCreatedTable" should be rewritten as a Select Query and the Select
Query should be used as the Report's RecordSource.

On Aug 3, 2:39*pm, "Gord" <x1gor...@telus.netwrote:
I'm using the report's 'On load' event which runs a small bit of code that
calls a procedure that does most of the work.

* * Private Sub Report_Load()

* * * * MyModule.MyProcedure

* * * * Me.RecordSource = "MyJustCreatedTable"

* * End Sub

As you see the record source for the report will be a table created by the
sub procedure. *The way I want this to work and the way I have written the
sub, is to delete the table first (if it exists) and then create the table
and populate with records as determined by the rest of the code. *The
portion of code in the sub that deletes the table is as follows:

* * Dim chkForExistTbl As TableDef
* * * * For Each chkForExistTbl In dBase.TableDefs
* * * * * * If chkForExistTbl.Name = "MyJustCreatedTable" Then _
* * * * * * * * dBase.TableDefs.Delete "MyJustCreatedTable"
* * * * Next

When I open the report from the navigation pane, the report opens just fine
with the just created table and records. *Everything A O.K. *While the
report is open in 'Report View' and I try to go to 'Print PreView' it would
seem my code runs again and generates the following error:

* * Run Time Error '3211'

* * The database engine could not lock table 'MyJustCreatedTable' because it
is already in use by another person or process.

When I click Debug, it is stalled on the line that is trying to delete the
table:

* * dBase.TableDefs.Delete "MyJustCreatedTable"

I assume this happens because the report itself is the process that is
'using' the table i.e. the table is it's recordsource.

In the above 'On load' event I added the following as the very first lineof
code:

* * Me.RecordSource = ""

in the hopes that when my code runs while going into print preview, it
wouldn't see the table as being 'in use' and would allow my code to delete
the table. *Again, this code works fine when opening the report from the
navigation pane. *Everything A O.K. *But now when I try to go into print
preview I get the following error:

* * Run time error '2191'

* * You can't set the Record Source property in print preview or after
printing has started.

So it would seen that print preview insists on the report as having it's
recordsource property set right at the get go.

My next thought was to make the first bit of code in the event check to see
if the report was loaded or not. *(I had hoped (fingers crossed) that the
report wouldn't report itself as being loaded right at the beginning of the
event when opened from the navigation pane.) *This is where I used a
modified version of your example (thank you) below *as the first line of
code in the event:

* * If CurrentProject.AllReports("MyReport").IsLoaded is true then exit sub

My thinking was that if the report was indicated as being loaded, then I
wouldn't bother running any of the code since this would be the event
triggered by going into print preview. *Of course this doesn't work as the
report is indicated as being loaded before my code runs so I can't
distinguish from opening the report from the navigation pane or going into
print preview.

In either case of whether I have the report's recordsource set or not, after
I clear the error dialog with 'End' I go into print preview just fine. *So I
could handle the problem with an error handler and suppress the error butI
am trying to be concise about this and not generate an error in the first
place.

I'm teaching myself Access from books/this newsgroup and I may be making an
incorrect assumption. *As I understand, a report needs to have a
recordsource from an existing table (or query, which is itself based on
existing tables). *This is why I create the table in the first place. Ialso
delete it and recreate it every time the report is run since the underlying
data (which is coming from a query) may have changed. *Perhaps this an
incorrect assumption and I can create a temporary 'virtual' table within my
code to use as a recordsource for the report so I don't have to delete
anything?

Thanks,

Gord

"lyle fairfield" <lyle.fairfi...@gmail.comwrote in message

news:9c**********************************@b1g2000h sg.googlegroups.com...
Did you tell us where the code exists?
In a standard module?
In the report's module? (if so is it event code? what event?)
In a class module?

Did you tell us what the code is? Exactly what is it?

How about posting this code and any related code such a procedure
which is called from the code?

What are the "error problems"?

In fact, code is not run once if you take too deep a breath, let alone
twice. It may be run twice if you have placed it in an event that
occurs twice, or more.

CurrentProject.AllReports("Supplier Phone Book").IsLoaded is true if
the "Supplier Phone Book" report is open, false if it is not, error
2467 if there is no report named "Supplier Phone Book", (Access
2007) .

On Aug 2, 10:52 pm, "Gord" <x1gor...@telus.netwrote:
I'm using 2007 and am self teaching. The next reply from TerryBell would
indicate that the code is run if you take too deep a breath.
Aug 3 '08 #12

This discussion thread is closed

Replies have been disabled for this discussion.