How to Define the Print Job Name in Print Queue for a Access Report?

[Jay Chan]

We have one Microsoft Access report that we use to print labels for
various projects. Let say it is called "LabelReport". This label-
report are being printed automatically from our server. Because the
report is being printed numerous times for many different projects, we
will see a long list of print-jobs showing up in the print-queue from
this report. Unfortunately, the print job names are all the same --
they all are called "LabelReport". Our users cannot tell which print
job is for which project. We want to be able to tell which print job
is for which project; then we can cancel a print job that was printed
by mistake, and we can move up a print job for a rush job that needs
to be printed as soon as possible (this report may have many pages of
labels and may take a long time to print). Therefore, we want to add
the project-ID as a part of the print job name.

The question is how to do this?

The logical place to start is to add the project-ID in the Caption of
the report. The project-ID shows up in the Windows Caption in Print-
Preview mode. But when we print the report, the print job name
doesn't have the project-ID. The only workaround is to show the
report in Print-Preview mode and then immediately print the report.
Somehow this can force the report Caption to show up as the print job
name in the print queue. The command is like this:

DoCmd.OpenReport "LabelReport", acViewPreview, , "PrjID=12345"
DoCmd.OpenReport "LabelReport", acViewNormal, , "PrjID=12345"

This trick works fine for many years. Unfortunately this trick causes
a problem when we move our server to a new server that has a different
version of Access in it (Access 2002 instead of Access 2000). Somehow
Access ignores the filter in the where-clause (the "PrjID=12345" shown
above) in the second OpenReport command above (the filter works fine
in the first OpenReport command). This would have printed labels for
all of our projects ever saved in our database and waste a lot of
labels if I didn't cancel that print job. When I search the net, I
see some suggestions about Access will ignore the where-clause if the
report has a ServerFilter property. But that ServerFilter is only
available in Access 2007 and mine is Access 2002.

Somehow the filter problem doesn't show up if I don't use the trick
and only use one OpenReport statement instead of two, like this:

DoCmd.OpenReport "LabelReport", acViewNormal, , "PrjID=12345"

But then I don't have the project-ID showing up in the print job name
in print queue.

This means this trick doesn't work any more in the new server. I need
to find a better way to get the project-ID showing up in print job
name.

Any idea? Please help.

Thanks in advance for any suggestion.

Jay Chan

 

[Sam Davis]

Hi Jay,

One solution is to copy the report and give the copy a name that includes
the Project ID, print this report and then delete it. Howver, I don't think
this will work with MDEs.

Something like.....

ThisPrjID=12345
DoCmd.CopyObject , "Labels " & ThisPrjID, acReport, "LabelReport"
DoCmd.OpenReport "Labels " & ThisPrjID, acViewNormal, , "PrjID=" & ThisPrjID
DoCmd.DeleteObject acReport, "Labels " & ThisPrjID

HTH
Sam

 

[Jay Chan]

Thanks a lot for the suggestion. This works very well.

The only winkle is that if the where-clause doesn't return any data to
the report, the report will generate an error. If the error is not
handled properly, the program will skip the DoCmd.DeleteObject
command, and the program will not clean up the copy of the report.
Luckily, this can be deal with by using an error handler.

I appreciate your help.

Jay Chan

 

[Jay Chan]

The other thing that I want to add is that this will only work if we
remove any Caption from the properties window of the report.
Otherwise, the Caption in the report properties will override the
report name, and it will be the one shows up as the print job name in
the print-queue.

Hope this helps other people.

Jay Chan