Mark when a report is Printed

[kerrmuir]

I have a Y/N field in a table that I want ticked when a report is printed for a record or batch of records. The report filters my record/s for a certain date.

The code I have is this but i'm not sure what the WHERE should be exactly as if fails at that point!

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

but maybe something else would be better?

 

[AccessBlaster]

I have a Y/N field in a table that I want ticked when a report is printed for a record or batch of records. The report filters my record/s for a certain date.

The code I have is this but i'm not sure what the WHERE should be exactly as if fails at that point!

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

but maybe something else would be better?

Maybe have a field called printed

Update YourTable
Set Printed = True
Where YourTable.RecordID = YourForm.RecordID

 

[kerrmuir]

Maybe have a field called printed

Update YourTable
Set Printed = True
Where YourTable.RecordID = YourForm.RecordID

So, what I need to do is print my report from a filtered form. That's what I'm seeing from everything I'v Googled so far and from your advice!

Is there any way it can be recorded that the print request was from a report and not from a form????

 

[AccessBlaster]

So, what I need to do is print my report from a filtered form. That's what I'm seeing from everything I'v Googled so far and from your advice!

Is there any way it can be recorded that the print request was from a report and not from a form????

I use reports to display information or print. I use forms to view, edit or trigger some other actions. What you want may be possible but, I don't use reports like that.

 

[kerrmuir]

SOLVED

My original code works perfectly for both forms and reports. I had a \ after the first commas which should not be there and also there were spaces in my field and report names which caused the errors, a beginners mistake!

The report is opened with a filter on the date sent field.

This now works perfectly.
If MsgBox("Would you like to print this report?", vbYesNo) = vbYes Then
DoCmd.OpenReport "LETTER1", acNormal
DoCmd.RunSQL "UPDATE Sheet1 SET Sheet1.LETTER1YN = True " & _
"WHERE (((Sheet1.DateSent)=Reports!LETTER1!DateSent));"
End If

I also put
DoCmd.Close acReport, "LETTER1", acSaveNo

to close the report immediately after the print.


Happy days, thanks Access Blaster for your help.