Print Events

Question

I am trying to find a report event that will execute each time a group section is printed.  I have a routine that writes a table record each time it is called.  I tried to use the “On Print” event but that will call the routine on preview and print which causes the data to be written multiple times.  Is there a way I can only have the routine run when it is previewed and not a second time when it is printed?

Thanks

Answer 1

Hi,

Can you use a global variable to flag when the record was already created, so it will skip the code when the flag is set?

Just a thought...

Answer 2

If the report is being opened by calling the OpenReport method of the DoCmd object, which would usually be the case in a developed application, pass a value into the report as the OpenArgs argument of the OpenReport method.  The code in the section's Print event procedure can then be made conditional on the value of the report's OpenArgs property.  Also make it conditional on the value of the PrintCount property being 1.  A section's Print event procedure will usually be executed only once, but there can be situations where it executes more than once.

If the report can be opened from the navigation pane, one solution would be to use a variation on Method 1 in my ReportDialogue demo which you'll find in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, Method 1 cancels the report if a dialogue form is not open, and opens the dialogue form, from which the report is then opened.  In my demo this is to restrict the report's output, but it would be simple matter to set up the dialogue form to open the report in Normal or Print Preview mode, and pass a value via the OpenArgs mechanism when the former option is selected.

Preventing multiple rows of the same data being inadvertently inserted into a table would normally be done by means of the indexes in the table.

---

Ken Sheridan, Stafford, England

Answer 3

Good thought and I actually tried that.  I then put a couple debug statements in the routine just to watch the behavior.  The "On Print" event is supposed to run before a section is previewed, printed or saved to a file. I watched as the routine was called on every record when I previewed, then I used the print button on the ribbon. The reported actually printed but it never returned to my routine but it double the transactions in my output table. I really appreciate your reply. Any more thoughts would be appreciated.

Thanks,

Pete

   

Answer 4

Thanks for your reply and suggestion.  You sound quite experienced with Access.  I will try to explain what I am doing and maybe this will help you help me.  I am running a report that prints a record in a group footer.  I use the "On Print" event to run my sub-procedure logic which writes a record to another table.  I run the report by right clicking on the report tab in design view and selecting "Print Preview".  I put a couple debug statements in the procedure to observe the behavior.  It appears the routine is called properly once for each line to print.  Next the preview comes up and at that point my table has the correct number of records in it.  If I select print from the ribbon the report prints but my table gets a duplicate of records.  Also, the routine is not called any more.  It just appears like magic that I get a duplicate set of records added to the table.  Does this help explain what I'm doing?  I would appreciate any guidance you can give.  Oh, I'm using Access 2016.

Thanks,

Pete

Answer 5

By relying on the user interface to print the report you are losing the opportunity to exercise control.  If you print the report via a command button in a dialogue form, on the other hand, you will be able to control things more precisely by executing an 'append' query in the button's Click event procedure rather than in the report's module.  However, you cannot be absolutely sure that the report has printed, so you might wish to make the code which executes the 'append' query conditional on the return value of the MsgBox function by which the user is required to confirm that the report has printed successfully, e.g.

    Const MESSAGE_TEXT = "Did the report print successfully?"
    Dim strSQL As string

    DoCmd.OpenReport "YourReportName", etc

    If MsgBox(MESSAGE_TEXT, vbQuestion + vbYesNo,"Confirm") = vbYes Then
        strSQL = "INSERT INTO…….etc"
        CurrentDb.Execute strSQL
    End If

To insert the correct values into the table, the string expression assigned to the strSQL will reflect restrict whatever restriction might have been imposed on the report, either by the WhereCondition argument of the OpenReport method or by parameters referenced by the report's RecordSource query.

To prevent the report being opened other than via the command button you can include code in its Open event procedure which checks to see if the dialogue form is open (as in my demo to which I referred you earlier), and sets the return value of the Cancel argument to TRUE, along with popping up a message box to inform the user than the report must be opened from the dialogue form.

---

Ken Sheridan, Stafford, England