If your report contains all records where Submitted is No, and you then want
to change Submitted to Yes, you could do that in the Close event of the
report.
There's an example of the event procecure below, but before you do that
consider:
1. Could the report ever be *filtered*, so not all records were printed?
2. Could any other user or process be adding new records that were not in
the report, but would be marked as submitted anyway?
3. How do you trace these records if the user accidently answers Yes, or
later discovers that there was a paper jam and the records did not print?
For these (and other) reasons, I would never consider taking this approach.
Instead, I create a batch of the records that will be printed, and then open
the report showing that batch. If the batch is no good, I let the user
delete the batch and recreate it. If something goes wrong with the physical
print process, they can go back and print the same batch again. And we have
a complete trail of not only *that* a record was printed (hopefully), but
who printed it when, and where multiple prints may have occurred. (The
'batch' just holds the primary key values of the records in the print run.)
If you want to proceed with your approach anyway:
Private Sub Report_Close()
Dim strSql As String
If MsgBox("Mark ALL records as printed?", vbYesNo) = vbYes Then
strSql = "UPDATE Table1 SET Submitted = True WHERE Submitted =
False;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
End Sub
