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.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dennis" <(E-Mail Removed)> wrote in message
news:7391B22D-B556-4AA3-BD22-(E-Mail Removed)...
> 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
>
> --
> Dennis
Similar Threads
