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

Report quirk

P: n/a
Here's one that's going to leave me bald before long -

I have a report (no headers or footers, small detail section) that runs
normally when I open it from the database window. When I run it from code:

DoCmd.OpenReport "rpt_Name", acViewPreview

I get five records on the first page, and only one record on the second
page, and there it stops, regardless of how many records there are in the
source. At first I thought I was always getting exactly six records, but
after playing with the size of the detail section I see that I'm getting
whatever records will fit on page 1, and only one record on page 2. Nothing
after.

Oh - Access 2000. Any thoughts?

Armando

Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Have you tried this from the VBA immediate window? No reason that it should
not work as expected. I suspect that you have some other preceding code
that interferes with the underlying recordset and/or the report filtering
property.
-Ed

"Armando" <ar*****@bogusaddress.com> wrote in message
news:j0*******************@news1.epix.net...
Here's one that's going to leave me bald before long -

I have a report (no headers or footers, small detail section) that runs
normally when I open it from the database window. When I run it from
code:

DoCmd.OpenReport "rpt_Name", acViewPreview

I get five records on the first page, and only one record on the second
page, and there it stops, regardless of how many records there are in the
source. At first I thought I was always getting exactly six records, but
after playing with the size of the detail section I see that I'm getting
whatever records will fit on page 1, and only one record on page 2.
Nothing
after.

Oh - Access 2000. Any thoughts?

Armando

Nov 13 '05 #2

P: n/a
Ed -

Thanks for the reply. I suspected something similar, but it baffled me why
just resizing the detail section would give a different number of records
(but always just one record on page 2).

I did figure it out what the problem was, though - in the code, right after
I open the report, I clear the table which is the data source for the
report. I guess that happens too soon for all the records to make it into
the report. Why just one on page 2, regardless of how many make it onto
page 1? I'd guess that the individual records making it onto the first page
(however many actually do) take a relatively small amount of time, but the
final format/layout of the page takes a bunch of time, and it's during this
that the data disappears. And, that one last record has to be read before
the report knows it won't fit on page 1, so that's why it makes it even
after the data goes away.

I guess my new problem is to find the best way to sense when the report is
done before I clear the table. Any ideas here? I don't think
StillExecuting will apply here.

Thanks again,

Armando

=====

"Ed Robichaud" <ed*********@wdn.com> wrote in message
news:_A******************@monger.newsread.com...
Have you tried this from the VBA immediate window? No reason that it should not work as expected. I suspect that you have some other preceding code
that interferes with the underlying recordset and/or the report filtering
property.
-Ed

"Armando" <ar*****@bogusaddress.com> wrote in message
news:j0*******************@news1.epix.net...
Here's one that's going to leave me bald before long -

I have a report (no headers or footers, small detail section) that runs
normally when I open it from the database window. When I run it from
code:

DoCmd.OpenReport "rpt_Name", acViewPreview

I get five records on the first page, and only one record on the second
page, and there it stops, regardless of how many records there are in the source. At first I thought I was always getting exactly six records, but after playing with the size of the detail section I see that I'm getting
whatever records will fit on page 1, and only one record on page 2.
Nothing
after.

Oh - Access 2000. Any thoughts?

Armando


Nov 13 '05 #3

P: n/a
Another few things I tried -

Used the report's OnOpen/OnClose events to set/clear a global boolean, and
then a While <boolean> Wend in the calling code to make it wait to clear the
table. Result - the report freezes after just its outline paints, with
ctrl-alt-del stubbornness.

Tried the same thing with the report's OnActivate/OnDeactivate events, with
the same result.

Originally the report itself was set to Modal, but that just disables
focus-shifting but not (calling) code execution.

Ultimately, this report will go directly to a printer (theatre tickets), but
I'd like to see the Preview work right before I get that far. I've yet to
print anything, it seems too early for that. If simply formatting the
preview doesn't happen fast enough, I couldn't expect queueing pages to the
PC to be much faster...

Armando
Nov 13 '05 #4

P: n/a
Thanks for experimenting. Why not run something like the following in the
report's OnClose event:

docmd.DeleteObject acTable "myTable"

You could also keep the table, but delete all the records:

DoCmd.OpenTable "myTable"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDeleteRecord

You'd probably want to turn off the standard warnings while either of these
run, then turn them back on.

I assume that this is a temp table used just to recreate a fresh record
source for each printing, so you definitely want it to remain open until the
report completes. Is this ticketing info so complex that you can't use a
dynamic query as the record source?

-Ed
"Armando" <ar*****@bogusaddress.com> wrote in message
news:WJ*******************@news1.epix.net...
Another few things I tried -

Used the report's OnOpen/OnClose events to set/clear a global boolean, and
then a While <boolean> Wend in the calling code to make it wait to clear
the
table. Result - the report freezes after just its outline paints, with
ctrl-alt-del stubbornness.

Tried the same thing with the report's OnActivate/OnDeactivate events,
with
the same result.

Originally the report itself was set to Modal, but that just disables
focus-shifting but not (calling) code execution.

Ultimately, this report will go directly to a printer (theatre tickets),
but
I'd like to see the Preview work right before I get that far. I've yet to
print anything, it seems too early for that. If simply formatting the
preview doesn't happen fast enough, I couldn't expect queueing pages to
the
PC to be much faster...

Armando

Nov 13 '05 #5

P: n/a
Ed -

This is probably a no-no, but my replies are interspersed so I can comment
on your several points.
Thanks for experimenting. Why not run something like the following in the
report's OnClose event:
docmd.DeleteObject acTable "myTable"
This is promising - printing the tickets is actually the very event that
means the data has been handled and not lost (given that it printed OK), so
maybe that's the (only) right place to clear the table. I still won't
delete it, just empty it.
You could also keep the table, but delete all the records:
DoCmd.OpenTable "myTable"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDeleteRecord
I do want to keep the table. Otherwise the first record in would have to
create the table, a special case to handle. If the table exists, all
records are handled the same, as adds. Right now I run
DoCmd.RunSQL "DELETE * FROM tbl_TicketQueue"
with warnings turned off then on.
You'd probably want to turn off the standard warnings while either of these run, then turn them back on. Yup.
I assume that this is a temp table used just to recreate a fresh record
source for each printing, so you definitely want it to remain open until the report completes. Is this ticketing info so complex that you can't use a
dynamic query as the record source?


It's not complex per se, but it accumulates information as the employee
presses buttons for tickets requested, then dumps it all as a multi-page
report, where each page is a single ticket. I'm assuming that whatever
(probably specialized) printer we use will allow very short form size in its
driver/dialog box, that's not a problem yet. I've been treating the Preview
and actual Print as acting the same for now, but there's one thing that
might be different when printing - if the report opens acNormal (direct to
printer), will there even BE a report opening and closing to sense?

and - If by "dynamic query" you mean something like the RunSQL above, I'm no
t sure how you'd apply that here.

I'm may just hard-code a second or two delay and move on...

Armando
Nov 13 '05 #6

P: n/a
OK, sounds like you've decided to create a "temp" table, then select/delete
its records before starting over. That should work; I have some similar
setups.

Every report, regardless of mode (preview, normal, design) has both Open and
Close events that trigger when the report is run/viewed/printed.
-Ed

"Armando" <ar*****@bogusaddress.com> wrote in message
news:be*******************@news1.epix.net...
Ed -

This is probably a no-no, but my replies are interspersed so I can comment
on your several points.
Thanks for experimenting. Why not run something like the following in
the
report's OnClose event:
docmd.DeleteObject acTable "myTable"


This is promising - printing the tickets is actually the very event that
means the data has been handled and not lost (given that it printed OK),
so
maybe that's the (only) right place to clear the table. I still won't
delete it, just empty it.
You could also keep the table, but delete all the records:
DoCmd.OpenTable "myTable"
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdDeleteRecord


I do want to keep the table. Otherwise the first record in would have to
create the table, a special case to handle. If the table exists, all
records are handled the same, as adds. Right now I run
DoCmd.RunSQL "DELETE * FROM tbl_TicketQueue"
with warnings turned off then on.
You'd probably want to turn off the standard warnings while either of

these
run, then turn them back on.

Yup.
I assume that this is a temp table used just to recreate a fresh record
source for each printing, so you definitely want it to remain open until

the
report completes. Is this ticketing info so complex that you can't use a
dynamic query as the record source?


It's not complex per se, but it accumulates information as the employee
presses buttons for tickets requested, then dumps it all as a multi-page
report, where each page is a single ticket. I'm assuming that whatever
(probably specialized) printer we use will allow very short form size in
its
driver/dialog box, that's not a problem yet. I've been treating the
Preview
and actual Print as acting the same for now, but there's one thing that
might be different when printing - if the report opens acNormal (direct to
printer), will there even BE a report opening and closing to sense?

and - If by "dynamic query" you mean something like the RunSQL above, I'm
no
t sure how you'd apply that here.

I'm may just hard-code a second or two delay and move on...

Armando

Nov 13 '05 #7

P: n/a
"Armando" <ar*****@bogusaddress.com> wrote in
news:WJ*******************@news1.epix.net:
Another few things I tried -

Used the report's OnOpen/OnClose events to set/clear a global
boolean, and then a While <boolean> Wend in the calling code
to make it wait to clear the table. Result - the report
freezes after just its outline paints, with ctrl-alt-del
stubbornness.

Tried the same thing with the report's OnActivate/OnDeactivate
events, with the same result.

Originally the report itself was set to Modal, but that just
disables focus-shifting but not (calling) code execution.

Ultimately, this report will go directly to a printer (theatre
tickets), but I'd like to see the Preview work right before I
get that far. I've yet to print anything, it seems too early
for that. If simply formatting the preview doesn't happen
fast enough, I couldn't expect queueing pages to the PC to be
much faster...

Armando

The Access mvp site has a code snippet to determine if a form is
open. http://www.mvps.org/access/forms/frm0002.htm
It isn't hard to modify so that it detects if a report is open,
in preview or normal mode.

I used this in a do/while containing a single DoEvents statement
to hold off executing the next report until the first had
processed. This was to print a series of one page documents to
separate .pdf files.

--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #8

P: n/a
Thanks, Bob.

I've already (maybe a bit begrudgingly) moved my
clear-table-and-other-cleanup routine into the OnClose of the report. I
really like running things from one central loaction, as opposed to making
one thing trigger the next. That solution works pretty well (thanks Ed),
but I'm always on the lookout for better ways to do things. I had already
looked thru the Access Web, and I didn't notice this one. So back I go -
thanks again.

Armando
"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1106701298.9d67796b6bd838e4b8bf6201e307d4b5@t eranews...
"Armando" <ar*****@bogusaddress.com> wrote in
news:WJ*******************@news1.epix.net:
Another few things I tried -

Used the report's OnOpen/OnClose events to set/clear a global
boolean, and then a While <boolean> Wend in the calling code
to make it wait to clear the table. Result - the report
freezes after just its outline paints, with ctrl-alt-del
stubbornness.

Tried the same thing with the report's OnActivate/OnDeactivate
events, with the same result.

Originally the report itself was set to Modal, but that just
disables focus-shifting but not (calling) code execution.

Ultimately, this report will go directly to a printer (theatre
tickets), but I'd like to see the Preview work right before I
get that far. I've yet to print anything, it seems too early
for that. If simply formatting the preview doesn't happen
fast enough, I couldn't expect queueing pages to the PC to be
much faster...

Armando

The Access mvp site has a code snippet to determine if a form is
open. http://www.mvps.org/access/forms/frm0002.htm
It isn't hard to modify so that it detects if a report is open,
in preview or normal mode.

I used this in a do/while containing a single DoEvents statement
to hold off executing the next report until the first had
processed. This was to print a series of one page documents to
separate .pdf files.

--
Bob Quintal

PA is y I've altered my email address.

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.