VBA code when a report is sent to printer

[valeryk2000]

Hi there,
This is the problem:
My report is open in preview mode. When the user selects File ==> Print and then clicks OK in the dialog box I need to update field 'ReportPrinted' in the database table. I'm using Access 2003.
Please help
Thanks
Val

 

[John Big Booty]

I'm not sure that there is any way to detect if a report in preview mode has been printed.

The best I can suggest is to use something similar to the following code in the Report's On Deactivate event;

    If MsgBox("Would you like to print this report?", vbYesNo) = vbYes Then
        DoCmd.OpenReport "Your ReportName", acNormal
        DoCmd.RunSQL "UPDATE YourTableName SET YourTableName.PrintYesNoField = True " & _
                    "WHERE (((YourTableName.RecordID)=Forms!YourFormName!RecordID));"
    End If

The above code assumes that the report being previewed is being called from a form that hold the current record to which the report relates.

This code will fire when the report is closed or focus shifts to another report or form. You would also need to hide/disable the standard menu options to prevent the user using the File Print menu.

 

[valeryk2000]

Thank you John. This is a nice aproach. But we do not know if the report was printed. Is there any way to get feedback from the printer (throgh windows api or else)?

 

[DCrake]

Do not allow the user to print from a print preview position. Force them to use a custom print routine.

 

[valeryk2000]

Well D, I can create the custom printing routine. But the question remains: how can I get a feedback from printer?
Thanks
Val

 

[John Big Booty]

If you look at the code I posted, part of it is an Update query that changes a check box to true once the report is printed, and I thought that was what you where after

 

[vbaInet]

The OP wants to check the printer spool.

Well, this isn't a standard interface in VBA but perhaps this link:

http://www.merrioncomputing.com/Programming/WatchPrinter.htm

 

[John Big Booty]

Sorry but that's not the way I read this

Hi there,
...
When the user selects File ==> Print and then clicks OK in the dialog box I need to update field 'ReportPrinted' in the database table. I'm using Access 2003.
Please help
Thanks
Val

 

[vbaInet]

Yep, it wasn't made clear in his first post, but his last two follow-up posts mentioned getting feedback from the printer and wanting to know if the report was printed.

 

[John Big Booty]

Fair enough

 

[smig]

and what if he print to a file or a PDF printer ?

As I'm working on tax application now I looked in many other.
for some paper you should mark them as printed to have only one original.
In all applications I looked even opening the paper in preview mark them as printed.
I took it one step farther, made my oun print dialog where I remove all PDF and File printers (at least I try) and mark the paper as printed when it sent to a printer.
The user has the option to print few copies. in this case I send one copy, mark the paper as printed, refresh the report, and send the other copies.

if, as me, you need one original only, using the original print dialog is not good as you can print several copies.

BTW - this "Original" reuirement is stupid as hell, as in all applications I looked user can print several copies or send it to a PDF file

 

[vbaInet]

Good point about pdf, but I would regard sending to PDF as printing as you're still using a print driver for this

If your printing failed (maybe because of a driver problem or device fault) then your document will be wrongly marked "printed". So you still need feedback from the print driver to confirm a print was successful.

But like you guys said, you definitely need a UDF print routine.

 

[John Big Booty]

Smig in your situation, once you have confirmed the first print you could set your print indicator to True and then use something along the lines of;

    If Me.Printed = True Then
        Me.YourLabelName.Caption = "Duplicate"
    Else
        Me.YourLabelName.Caption = "Original"
    End If

In the Report's On Activate event.

 

[smig]

@vbaInet - as all other applications I tested mark the paper as printed as soon as it load as preview, and this is OK for Tax authority I think my approach setting it as printed after it was sent to printer is even better (So the user can close the preview without it beeing mark as printed)
In case there is a driver problem or any other situation caused it not to print they can print a "Copy" and write on it "Identical to original" and sign it. this is legal.

@John Big Booty - this is exactly what I do. but I want to make sure the first print is for one copy only (all other applications I looked at don't ) that's why after the first copy sent to printer I close the report, update the data and print the other copies.

geting the printer spool information is too much not required work for me. maybe also for the OP.

 

[vbaInet]

In case there is a driver problem or any other situation caused it not to print they can print a "Copy" and write on it "Identical to original" and sign it. this is legal.

geting the printer spool information is too much not required work for me. maybe also for the OP.

Not the same with the OP. He has already been advised about using a print button but the OP exclaimed that it didn't suffice. So getting feeback from the printer device is necessary in this case.

 

[valeryk2000]

Thank you vbaInet - Merrion's article looks promising. Will try it. And be back (on Monday).
Happy Chinese New Year to everybody!
Val

 

[John Big Booty]

...

@John Big Booty - this is exactly what I do. but I want to make sure the first print is for one copy only (all other applications I looked at don't ) that's why after the first copy sent to printer I close the report, update the data and print the other copies.

geting the printer spool information is too much not required work for me. maybe also for the OP.

Ah, sorry to try and reinvent the wheel you are doing exactly as I would do

 

[kerrmuir]

I'm not sure that there is any way to detect if a report in preview mode has been printed.

The best I can suggest is to use something similar to the following code in the Report's On Deactivate event;

    If MsgBox("Would you like to print this report?", vbYesNo) = vbYes Then
        DoCmd.OpenReport "Your ReportName", acNormal
        DoCmd.RunSQL "UPDATE YourTableName SET YourTableName.PrintYesNoField = True " & _
                    "WHERE (((YourTableName.RecordID)=Forms!YourFormName!RecordID));"
    End If

The above code assumes that the report being previewed is being called from a form that hold the current record to which the report relates.

This code will fire when the report is closed or focus shifts to another report or form. You would also need to hide/disable the standard menu options to prevent the user using the File Print menu.

OK, I can't get this to work.

I have a report that when opened asks for a date which filters to print letters for a specific date.

It filters records for this date then I print it. I want it to update a Y/N field in the main table to say that this record has been printed.

The code above works great until line 4, I guess coz I'm printing from the report and not a form.

I have tired this but something is missing? Please help..

If MsgBox("Would you like to print this report?", vbYesNo) = vbYes Then
DoCmd.OpenReport "LETTER 1", acNormal
DoCmd.RunSQL "UPDATE Sheet1 SET Sheet1.LETTER1YN = True " & _
"WHERE (((Sheet1.RecordID)=Reports!LETTER 1!RecordID));"
End If

I am using access 2007.