Assign batch # to rcds so report can be recreated.

[Dennis]

Hi,

I'm running Access via Office XP Pro on Windows 7 Pro.

I've cross posted this to the Report group (I hope) and I'm trying to
develop a couple of SQL statements to help me recreate previously printed
forms / reports.



Goal / Background ------------------------

I want to print the actual invoice for all transactions that have NOT been
previously been printed. The invoice numbers were assigned when the
transactions were created. I just want to “group†all the transactions into a
single batch so that I can re-produce that given batch at any given point in
time.

Our customers must be members of the organization. Therefore, each year
they must pay a membership fee to renew for that year. We will print a
membership card for them once we receive payment. We only want to print that
membership card once (even though we can re-print it if they loose it). We
also want to know when we printed that card. These cards are printed once a
week in a batch, to which we will assign a batch number.

I already have my reports up and working. Currently, I have to manually
select the transaction I want to print by manually modifying my query. So
not a good solution, but I was to develop, test, and debug my report.

To do this, I want to:
1. Generate a batch number to be used. I already have this code working.
It goes out to the tblBatchRpt and uses DMax to get the current highest batch
number in the table. It adds 1 to it and writes a new batch ctl record out
with date, time, report name, and some other stuff.

2. My report will then select all unprinted transactions and update the
batch number field on those transaction records with the new batch number.

3. I also want to create an central audit trail file that will track each
record that was assigned to a batch, regardless of which report it was on, or
in which table the data record resides.

4. My report will then set it Filter property (I’m not sure I used the
correct terminology - My.Filter = “xxx†or is it My.ReportFilter = “xxxâ€)
to select just the record with the current batch number. It will then print
the data.

5. My “Reprint†option will have a form with a combo box what will list all
of the dates and time the report was run and as the user which report they
want. This combo box will work off the tblBatchRpt table and list just those
batches that were created by the current report. When the user selects the
batch they want to re-print, the report will set it current filter to that
batch and re-print that batch.


Database --------------------------------------------------

tblBatchCtl: 1 rcd per batch

Key: BatchNo - Autoassign number by Access
Print Date
Print Time
Report Name
Comment

tblBatchAudit I will use this file to have a centralize place so that
I can see all of the records that have been assigned to a batch. Is it
possible (with future planned upgrades) that one record would be printed on
different report in different batches at different points in time. This
table will allow me to see all of the batches in which a single record has
been included / printed.

I will also create a query table / logical table that will only show the
records for a given table (tableNo). This way I can join the tblRenew to
the qtBatchAudit_Renew and see which renewal record had been printed and when
they were printed. I will be able to do the same thing for the tblInvoice
and tblCustomer tables.

Key: AuditId - Autoassigned number by Accss
BatchNo
TableNo - 1 = tblRenew, 2 = tblInvoice, etc
RcdID - This will be the key to the record that was assigned
the batch number.



TblCustomer: 1 rcd per customer
CustId
Name
Etc.

TblRenew: Each customer is a member and they must renew each year.
1 rcd per customer per year.
RenewalId Auto assigned number by Access
CustId
RenewalYear


tblInvoice 1 rcd per invoice multiple rcds per customer.

Key: Invoice Number – auto assigned number by Access
CustId
InvoiceDt



tblInvoiceDet - 1 rcd per transaction – Multiple rcd per invoice.
Key: TransId - autoassigned assigned number by Access
Details field for each line on the invoice.



Question ---------------------------------------------------

A: What would be the SQL statement to:

1. Select all unprinted records and assign a batch number to them.
The select criteria would be WITH IsNull(BatchNo) or BatchNo = “â€


2. Select all of the records that have been assigned the batch number by
the above step and write out a record to the tblBatchAudit table?


B. How do I execute those SQL statements within a report’s VBA code?


Are there any suggestions for how to make this a simpler or cleaner process?


Thank you so much for you assistance.

Dennis

 

[Allen Browne]

Dennis, that's a fairly involved question. The simplest answer I can give is
to point you to a sample database that does something like what you ask, and
let you figure it out from there.

See:
Has the record been printed?
at:
http://allenbrowne.com/ser-72.html

It assigns a batch number to those that have not been printed, and then
prints the batch (or allows you to print that batch again later.)

 

[Dennis]

Allen,

Once again, thanks. I looked at your code and it does pretty everything I
need to do and how to do it.

I saw on your web site that you suggested comp.database.ms-access usenet.
Is that were you will be "hanging out"?


Thanks

Dennis

 

[Allen Browne]

Once again, thanks. I looked at your code and it does pretty everything I
need to do and how to do it.

I saw on your web site that you suggested comp.database.ms-access usenet.
Is that were you will be "hanging out"?

That's one of the groups I've been involved in since 1994. C U there.

 

[Dennis]

Allen,

Thanks one again. I'll c u in comp.database.ms-access usenet.

Thanks for all of your help.

Dennis