On Print Event - Update table with date of print

[jdttek]

I print a batch of reports. I am trying to update a table with the date, time and customer ID for each record using the On Print Event. Does anyone have an idea of how to approach this??

Thanks
JDTTEK

 

[scriverb]

Using the OnPrint event of a Detail Section to append records to a table would be very inefficient. The best way to do this is to use the same query that is the Record Source for the report and create an APPEND query to add records to the table you want to update. Since the query will supply the same records being printed then you can APPEND the records just prior to printing the report. This can be done by the following code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppPrintedRecords"
DoCmd.OpenReport "rptPrintRecords"
DoCmd.SetWarnings True

I believe this would meet your requirements and is much more efficient.
Bob Scriver

 

[pdldavis]

Hi, I have a question about this too. I date and time stamp records on Print Preview using:

Docmd.setwarnings false
Docmd.runsql.....
Docmd.setwarnings true

I would prefer to do this when the individual actually prints the record after previewing it but I have not been able to do this - and in this instance the record needs to be previewed prior to print.

Do you have any suggestions on how this might be accomplished?

Thanks, Dan

 

[scriverb]

The recommendation to use a query to update the records prior to printing a batch of records is completely different from what you want to do. You want to trigger the update of the date and time stamp at the time of the records actual review and print, correct?

I will have to think about that one. I will get back to you through this thread. We may want to post a seperate thread to do what you want to do to include other experts and because it is a little different twist to the problem.

Bob Scriver

 

[scriverb]

Okay, if you put some VBA code in the OnPrint event procedure of the Detail Section of the report the following will happen.
1. Upon reviewing the first record if the user either clicks the print button or the next record button the event procedure executes. It is at this time that you could have code that prompts the user for a confirmation that he wants to update the database record with the printing confirmation information.

2. This only seems to happen the first time that you print or move to the next record. So, if the user reviews the page and moves to the next record and elects not to update then goes back to that record the prompt for updating confirmation will not occur. You see ACCESS thinks that the print event is already accomplished.

Code like the following could be used in the Detail Section OnPrint event procedure:
Dim db as DAO.database
Dim rs as DAO.recordset
Dim vResponse as Variant
vResponse = MsgBox("Do You want to Print?", vbYesNo, "Print Prompt&quot
If vResponse = 6 then
Set db = CurrentDB
Set rs = db.OpenRecordset("tblYourTableName", dbOpenDynaset)
rs.FindFirst "[RecordID] = " & Me![RecordIDControl]
If Not rs.NoMatch then
rs.edit
rs("Field1&quot = ????
. . .
rs.Update
else
'record not found to update -- display error
end if
End if
rs.close
db.close

I will work at little longer on another more bulletproof solution. Bob Scriver

 

[pdldavis]

Thanks Bob,

Yes, that is the idea - when the user previews the records they will either get printed or they won't - in mass. There is no picking & choosing individual records to print. It is unnecessary for the application.

I currently have a little routine where they can de-select the records after they have been marked as printed and remove the date/time stamp - not the correct way to do it and that is the problem.

I'll give your suggestion a try. I won't be able to get back to this till later on in the evening.

Thanks,

Dan