Assigning print date / batch number to reports & invoices

[Dennis]

Hi,


I want to assign a print date to things that are only supposed to be printed
once such as invoices, statements, etc.

I have a invoice table. On the invoice table, I have PrintDate, which is
the date the individual invoice was printed. The query select all invoices
with no print date and prints them.

I will be changing the PrintDate to the BatchNo field. I'll have a
ReportBatch table (keyed by BatchNo) where I will record the date, time,
report name, and other information about the different reports that are
printed. And my query will be change to select all invoices with no BatchNo.

I realize that I have to run an update question to flag all of the rows that
were printed on the particular report.

When the user prints the report, I would like to have a pop up form appear
and ask if the report printed ok. If the users answers yes, I will run the
update query. If the user answer no, I'll ask if the want to run the report
again.

Here are my questions:

1. Is there a way to tell between PrintPreview and Print? I would like
the pop up window to only appear when the report is actually printed and not
in print preview.

2. How do I generate update the BatchReport table so I can get a batch
number?

3. In what report event should I put the pop up window, the update of the
BatchReport table, and the running of update query.

4. How do I reopen my report query so the update query will know which
items to update? I'm concerned about timing issues. For example, how do I
prevent invoices from being flag that were entered after the invoice query
ran and when the update query ran? I realize that it will only be a matter
of minutes, but I've see it happen before.


Thank you in advance. Any advice would be helpful.

Dennis

 

[Daryl S]

Dennis -

I would suggest creating the batch number first, then updating the table
with the batch number, and using the batch number to select which records to
print. If you use Autonumber for the batch number, then you have no fear of
there being a duplicate batch number. If the print fails, you can just
re-run the same query (no new records will be added). You could even add a
field to the Batch Number table to indicate who ran it (machine name or logon
name, etc.).

As for your questions:
1. You can have both a print preview button and a print button on the form,
but I don't think that will help, as the user can print from print preview
mode, and depending on the printer dialog box, they can choose print preview
even after hitting the print button. Someone else may know more than me on
that one.

2. Use an append query. If all the information you need (other than the
batch number) is on the form, then you can run it from there. Then you will
need a select query (using the same form controls as criteria) to get the
batch number that was assigned (assuming autonumber). Put this batch number
on the form (it can be invisible if you don't want the users to see it).

3. When the user pushes your Print button - I would first check to see if
you have already have a batch number (see #2). If so, use that to re-print
the report. If not, then run the append query from #2, and your update query
to put the batch numbers on the records. Then run the report. I'm not sure
if you want a confirmation then, or if you want to ask the user before
leaving the form or pulling new records if they received the printout. If
the user did not actually print the report, you would only remove the batch
number from the records - do not remove the batch number from the batch table.

4. If you set up the batch number first as described above, this will not
be a problem.

 

[Dennis]

Daryl,

Thanks for the info. I will work on it today. I think I have the idea.
Let me see if I can translate the idea into actual code.


Thanks,

Dennis

 

[Dennis]

Daryl,

The tblRptBatch table contains the following fields:

BatchNo (Primary key - autonumber)
ReportCode (Foreign key to Report Name table)
RunDate
RunTime
UserName
ReportOptions

Can you think of any other fields I might want in this table?

 

[Daryl S]

Dennis -

Is it true that if ANY report is run for an invoice, that it should be
marked as printed? If so, then which report was run makes sense to me. I
had assumed there would only be one InvoiceReport that would be tracked, but
if there are multiple, then this sounds like a good field to populate.

Not sure what is in the Report Options, and if that would tell you anything.
Again, you know your requirements, so this might be good.

As for anything else, just think about what questions you would like to
answer about any report or invoice that has been printed. We have these
covered:
1. When was this invoice printed?
2. Who printed this invoice?
3. Which report was used to print this invoice?
4. What options were used to print this invoice?
5. How many invoices were printed per day... etc.
6. How many invoices were printed by UserName... etc.

If there are any other questions you would like to answer based on the
running of invoice reports, then this is the time/place to add the data
fields.

Looks good to me!

 

[Dennis]

Daryl,

Sorry, I got move to other items. Pesky customers. .

Anyhow, let me answers your questions:

Your comment: Is it true that if ANY report is run for an invoice, that it
should be marked as printed? If so, then which report was run makes sense to
me. I had assumed there would only be one InvoiceReport that would be
tracked, but if there are multiple, then this sounds like a good field to
populate.

Response: I don’t understand your comment. There is only one invoice form
that prints. However, I will be using this technique for other report hence
the ReportCode field.


Your Comment: Not sure what is in the Report Options, and if that would
tell you anything.

Response: There are the report options that were selected at run time. I
don’t know if I will implement this because I don’t know if it will really
tell me anything.


Your comment: As for anything else, just think about what questions you
would like to answer about any report or invoice that has been printed. …..
If there are any other questions you would like to answer based on the
running of invoice reports, then this is the time/place to add the data
fields.

Response: You are correct. I asked the questions before I figured out the
data fields. I don’t know of any other questions.


Thanks for your input. I’ll be using your ideas in my final version.


Dennis

 

[Daryl S]

Dennis -

It sounds like you have everything thought out. Good luck!