473,218 Members | 1,418 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

Report runs code when print previewing

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
11 7245
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: scoimbra | last post by:
Hello there, I have a report in an access database that always prints to the MS Image Writer. I have to do this several times a day. Does any of you know how to implement a VB code to print...
0
by: elgin | last post by:
I have a form which is formatted so as to print out for mailing. This form contains several option groups with checkboxes. These checkboxes have no data source; the state of the checkboxes are...
1
by: Scott | last post by:
Hello- I have a contol button on a form that I want to code to open a report (rptReviews), then display the print dialogue box so that users can select a printer. This report needs color, but...
5
by: Stefan Bellon | last post by:
Hi all! I am embedding Python into a GUI application in a way that the GUI is scriptable using Python. Now I have come to a problem that when the user puts a "sys.exit(0)" into his script to...
1
by: G Gerard | last post by:
Hello When I want to print a report using code I normally use the following code DoCmd.OpenReport "RptName", acViewNormal This works OK when I'm only printing one copy of the report. What...
29
by: Virtual_X | last post by:
As in IEEE754 double consist of sign bit 11 bits for exponent 52 bits for fraction i write this code to print double parts as it explained in ieee754 i want to know if the code contain any...
8
by: dponce | last post by:
What's up forum, Is this possible? Is there a way to have access ask the end user which report you want to print? If so, how.
2
by: PW | last post by:
Hi, It's an Access 2003 report. The underlying query runs fast on my PC but I have to hit Ctrl-Break to stop the report as it is stuck on "formatting page". The report runs fast on my wife's...
1
by: maximus tee | last post by:
hi, i'm using Python 2.5 and Windows XP. i'm wondering whether is a way to enlarge the font size when print out to CMD window. For eg: print "Hello World". It will print out with standard size in...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.