How to capture print job being started in vba

[lucky245]

I have records which get printed each month for staff to be paid. What I would like to do it when a record has been sent to print a check box be filled in the main table so I can determine whether a record has been printed off already. This is incase staff are late with their paperwork I can print their details seperatly from the ones already printed.

Thanks in advance

 

[Trevor G]

Welcome to the forum,

I would use a query to do this, with a date being added when it goes to the printer, so you would add an expression with Date() which places in todays date, so you click a button it fires of the query.

There is an issue though what happens if the printer is faulty at some stage, it will state you have done the print but not in real terms!

 

[lucky245]

Thanks for your prompt reply however what I am really after is how in vba would I capture the print command being used on this report / query as people are likely to view the query often but only print once a month. Hope that makes more sense. Once the print command is used in relation to this query the check / date field will be populated thus the next print will only have those records which have not already been printed.

 

[Trevor G]

Here is some code thatI have used in the past to place a tick in the checkbox when printing, there are 2 versions here: I have highlighted in red the parts you will have to amend to your own table and forms and reports and fields.

Sub PrintRecord()
Const cstrtitle As String = "Printing Records"
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field


Set db = CurrentDb
Set rst = db.OpenRecordset("qryNewRecordsFilter")
rst.MoveFirst
Do Until rst.EOF
rst.MoveNext
rst.Edit
rst.Fields("Printed") = True
rst.Update
rst.MoveNext
Loop


DoCmd.OpenReport "rptNewFilterReport", acViewNormal

End Sub

Function PrintSelectedRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
DoCmd.OpenReport "rptnewFilterReport", acViewNormal
Set db = CurrentDb
Set rst = db.OpenRecordset("tblRegistration") '("qryNewReport", 2
rst.MoveFirst
'Do Until rst.EOF

Do Until rst.Fields("ID").Value = Forms![frmNewRecordsFilter]![ID]
rst.MoveNext

rst.Edit
rst.Fields("printed").Value = True
rst.Update
rst.MoveNext
Loop

DoCmd.Close acReport, "rptnewFilterReport"
DoCmd.Close acForm, "frmNewRecordsFilter"

DoCmd.OpenForm "frmRegistration", acNormal, "", "", , acNormal
DoCmd.GoToRecord acForm, "frmRegistration", acNewRec
End Function

 

[lucky245]

Looks exactly what I need thankyou

 

[ghudson]

I would also [or instead] add a date field to store when the record was printed.