Has the record been printed?

        2 votes: *****     2,655 views      No comments
by Allen Browne, 18 January 2008    (for Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne, April 2007

Has the record been printed?

Record Print screenshot

This question is usually asked as, "How can I mark a record as printed? Not just previewed - when it actually goes to the printer?"

The question has some thorny aspects. Firstly, it is tricky to tell printing from previewing. Worse, printers run out of ink/toner, or the paper jams and someone turns it off before the job really prints. It needs more than just a yes/no field to mark the record as printed or not.

A better solution is to mark the records as part of a print run before they are sent to the printer. You can then send the batch again if something goes wrong. You have a record of when the record was printed, and you can can reprint a batch at any time.

So, instead of a yes/no field indicating if the record has printed, you use a Number field and store the batch number. The number is blank until the record has been printed. Then it contains the number of the print batch. If something goes wrong, you send the print run again.

Download the sample database (27kb zipped.) Requires Access 2000 or later.

Assign the number first, then print the batch

The database has a table where you enter new members (tblMember), and a table that tracks the print runs (tblBatch.) When you enter a new member, you leave the BatchID blank.

When you are ready to print the new members, open frmBatch (shown above.) Click the Create New Batch button. It creates a new entry in tblBatch, and assigns the new batch number to all the members that have not been printed (i.e. BatchID is null.) Then click Print Selected Batch to print those records. It prints the batch by filtering the report.

Not only do you know if a record was printed: you kwow when it was printed. If something goes wrong with the printer, you send the batch again. You can even undo the batch, and recreate it if necessary.

Taking it further

This section explains a couple of ways to to extend the database beyond the example.

Track each time a record is printed

In some databases, you may want to track each time a record is printed.

Since one record can be printed many times, you need a related table to do this. The table has two fields:

  • BatchID - relates to tblBatch.BatchID
  • MemberID - relates to tblMember.MemberID

Now if batch 7 should contain 12 members, you add 12 records to this table. Then print the matching records with a query that filters just the one batch.

You now have a complete history of each time a record was printed. (The sample database shows this table, but does not demonstrate how to use it.)

Simplifying the Undo

The Undo button in sample database sets the BatchID to Null for all records in the batch, and then deletes the batch number. The first of those two steps could be avoided if you use a cascade-to-null relation.

The code

View the code in your browser, or download the sample database (27kb zipped.)

HomeIndex of tipsTop

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Has the record been printed?'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).