Canceling from print within a While loop.

  • Thread starter Thread starter Michael Daly
  • Start date Start date
M

Michael Daly

I have created a program to print individual member
reports of one page per member. My program creates a query
for each member in the list, then sends that report to the
printer. The code is as follows:

While Not Rst.EOF
DoEvents
CreateQryMemberResults (MemberId) ' Create new query.
DoCmd.OpenReport stDocName, acViewNormal
Rst.MoveNext
Wend

This works okay, but each time it executes the
DoCmd.OpenReport line of the code it opens and displays a
msgbox with a [Cancel] button. If I press this button it
only cancels that one report, and not break out of my
While... Wend loop. How can I trap if a user presses the
[Cancel] button on the OpenReport msgbox, and cancel all
further processing of the While... Wend loop?

Alternatively, can I suppress the displaying of the
OpenReport msgbox, and display my own form with a status
of what is printing, and control when a [Cancel] button is
clicked?

p.s. I'm using Access 97. Are the above problems fixable
with Access 97, or would a newer version of Access (say
2000) have features to achieve the above if it can fix the
problem?

Thanks for your assistance.
 
This may help with suppressing the dialog.

http://www.mvps.org/access/api/api0037.htm

However, the best way to handle this may be to create the query that will
use all of the Member IDs in the recordset at one time and have the report
set up to give you a new page after each record.

One possible way would be to concatenate the IDs together into an IN
statement for the WHERE clause of the query. Another possibility is to fill
a temporary table with the IDs. The query would include this table along
with the one(s) it currently has. The tables would be linked on the ID
field. This would only allow those records that had IDs in the temp table to
be returned by the query. You could have a permanent query this way and just
clear and add data to the temp table as needed.

Example of IN statement:

SELECT [Field1] FROM Table1 WHERE [MemberID] IN (1,2,3,6);
 
Thanks Wayne

A problem with trying to set up the results in one
temporary table or Select query with the IN clause, is
that I have approx. 200 members to print reports for. With
the query I use in the While... Wend
loop 'CreateQryMemberResults (MemberId)' I create a new
query for each Member Id. This query in turn is used by a
crosstab query. This crosstab query is the source for the
report. It sounds a little complicated but it works well,
and is data driven - in that I have been able to get
around the hard coding of dates for Row Headings in the
crosstab query, and thus in the reports. With all that
said I don't know if I can create the solution you
suggested, but I will look into it further. Thank you.

I thought there might have been a way of trapping the
[Cancel] button on the msgbox, such as:
if vbCancel then ...
if vbCancel referred to the action of trapping the user's
actions of clicking the [Cancel] button on the Print
msgbox?

Michael Daly
-----Original Message-----
This may help with suppressing the dialog.

http://www.mvps.org/access/api/api0037.htm

However, the best way to handle this may be to create the query that will
use all of the Member IDs in the recordset at one time and have the report
set up to give you a new page after each record.

One possible way would be to concatenate the IDs together into an IN
statement for the WHERE clause of the query. Another possibility is to fill
a temporary table with the IDs. The query would include this table along
with the one(s) it currently has. The tables would be linked on the ID
field. This would only allow those records that had IDs in the temp table to
be returned by the query. You could have a permanent query this way and just
clear and add data to the temp table as needed.

Example of IN statement:

SELECT [Field1] FROM Table1 WHERE [MemberID] IN (1,2,3,6);

--
Wayne Morgan
Microsoft Access MVP


I have created a program to print individual member
reports of one page per member. My program creates a query
for each member in the list, then sends that report to the
printer. The code is as follows:

While Not Rst.EOF
DoEvents
CreateQryMemberResults (MemberId) ' Create new query.
DoCmd.OpenReport stDocName, acViewNormal
Rst.MoveNext
Wend

This works okay, but each time it executes the
DoCmd.OpenReport line of the code it opens and displays a
msgbox with a [Cancel] button. If I press this button it
only cancels that one report, and not break out of my
While... Wend loop. How can I trap if a user presses the
[Cancel] button on the OpenReport msgbox, and cancel all
further processing of the While... Wend loop?

Alternatively, can I suppress the displaying of the
OpenReport msgbox, and display my own form with a status
of what is printing, and control when a [Cancel] button is
clicked?

p.s. I'm using Access 97. Are the above problems fixable
with Access 97, or would a newer version of Access (say
2000) have features to achieve the above if it can fix the
problem?

Thanks for your assistance.
Click to expand...


.
Click to expand...
 
If there is a way of trapping that cancel button, it would probably be a
hook into the Windows API. Disabling that dialog and creating your own
inside the loop may be the easier way. Have it pop-up for 2 or 3 seconds
after each print command then close itself. You wouldn't be able to cancel
the current job that way, but you could then drop out of the loop. The
message box would actually have to be a pop-up form (opened with the
acDialog window mode argument) that was designed to look like a message box.
This would halt the code until the "message box" was closed. To cancel the
current print job(s) you would have to use the print spooler window (the
printer icon in the tray by the clock).

--
Wayne Morgan
Microsoft Access MVP


Michael Daly said:
I thought there might have been a way of trapping the
[Cancel] button on the msgbox, such as:
if vbCancel then ...
if vbCancel referred to the action of trapping the user's
actions of clicking the [Cancel] button on the Print
msgbox?

Michael Daly
-----Original Message-----
This may help with suppressing the dialog.

http://www.mvps.org/access/api/api0037.htm

However, the best way to handle this may be to create the query that will
use all of the Member IDs in the recordset at one time and have the report
set up to give you a new page after each record.

One possible way would be to concatenate the IDs together into an IN
statement for the WHERE clause of the query. Another possibility is to fill
a temporary table with the IDs. The query would include this table along
with the one(s) it currently has. The tables would be linked on the ID
field. This would only allow those records that had IDs in the temp table to
be returned by the query. You could have a permanent query this way and just
clear and add data to the temp table as needed.

Example of IN statement:

SELECT [Field1] FROM Table1 WHERE [MemberID] IN (1,2,3,6);

--
Wayne Morgan
Microsoft Access MVP


I have created a program to print individual member
reports of one page per member. My program creates a query
for each member in the list, then sends that report to the
printer. The code is as follows:

While Not Rst.EOF
DoEvents
CreateQryMemberResults (MemberId) ' Create new query.
DoCmd.OpenReport stDocName, acViewNormal
Rst.MoveNext
Wend

This works okay, but each time it executes the
DoCmd.OpenReport line of the code it opens and displays a
msgbox with a [Cancel] button. If I press this button it
only cancels that one report, and not break out of my
While... Wend loop. How can I trap if a user presses the
[Cancel] button on the OpenReport msgbox, and cancel all
further processing of the While... Wend loop?

Alternatively, can I suppress the displaying of the
OpenReport msgbox, and display my own form with a status
of what is printing, and control when a [Cancel] button is
clicked?

p.s. I'm using Access 97. Are the above problems fixable
with Access 97, or would a newer version of Access (say
2000) have features to achieve the above if it can fix the
problem?

Thanks for your assistance.
Click to expand...


.
Click to expand...
Click to expand...
 
Wayne

Thanks for your assistance. I had a look around the
www.mvps.org site and saw a piece of code that has done
the job. I don't know how to write my own hook into the
api (which if I did would be the best solution) so I have
placed a little piece of code that looks for the [ESC] key
to be pressed, then allows you to exit the loop.

Thanks very much again.

Michael Daly
-----Original Message-----
If there is a way of trapping that cancel button, it would probably be a
hook into the Windows API. Disabling that dialog and creating your own
inside the loop may be the easier way. Have it pop-up for 2 or 3 seconds
after each print command then close itself. You wouldn't be able to cancel
the current job that way, but you could then drop out of the loop. The
message box would actually have to be a pop-up form (opened with the
acDialog window mode argument) that was designed to look like a message box.
This would halt the code until the "message box" was closed. To cancel the
current print job(s) you would have to use the print spooler window (the
printer icon in the tray by the clock).

--
Wayne Morgan
Microsoft Access MVP


I thought there might have been a way of trapping the
[Cancel] button on the msgbox, such as:
if vbCancel then ...
if vbCancel referred to the action of trapping the user's
actions of clicking the [Cancel] button on the Print
msgbox?

Michael Daly
-----Original Message-----
This may help with suppressing the dialog.

http://www.mvps.org/access/api/api0037.htm

However, the best way to handle this may be to create
Click to expand...
the
query that will
use all of the Member IDs in the recordset at one time and have the report
set up to give you a new page after each record.

One possible way would be to concatenate the IDs
Click to expand...
together
into an IN
statement for the WHERE clause of the query. Another possibility is to fill
a temporary table with the IDs. The query would include this table along
with the one(s) it currently has. The tables would be linked on the ID
field. This would only allow those records that had IDs in the temp table to
be returned by the query. You could have a permanent query this way and just
clear and add data to the temp table as needed.

Example of IN statement:

SELECT [Field1] FROM Table1 WHERE [MemberID] IN (1,2,3,6);

--
Wayne Morgan
Microsoft Access MVP


I have created a program to print individual member
reports of one page per member. My program creates a query
for each member in the list, then sends that report
Click to expand...
to
the
printer. The code is as follows:

While Not Rst.EOF
DoEvents
CreateQryMemberResults (MemberId) ' Create new query.
DoCmd.OpenReport stDocName, acViewNormal
Rst.MoveNext
Wend

This works okay, but each time it executes the
DoCmd.OpenReport line of the code it opens and
Click to expand...
displays
a
msgbox with a [Cancel] button. If I press this button it
only cancels that one report, and not break out of my
While... Wend loop. How can I trap if a user presses the
[Cancel] button on the OpenReport msgbox, and cancel all
further processing of the While... Wend loop?

Alternatively, can I suppress the displaying of the
OpenReport msgbox, and display my own form with a status
of what is printing, and control when a [Cancel]
Click to expand...
button
is
clicked?

p.s. I'm using Access 97. Are the above problems fixable
with Access 97, or would a newer version of Access (say
2000) have features to achieve the above if it can
Click to expand...
fix
the
problem?

Thanks for your assistance.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top