Repeating/Cloning Records

[SDH]

I am developing an invoicing system for a (very small) cleaning
company.

The owner does almost exactly the same work for the same set of
clients each month, so each monthly set of invoices is essentially the
same, each client's invoice differing only in date and invoice number
from month to month. Typically out of about 100 clients/contracts
there will only be 5 or 6 changes (additions, deletions, price changes
etc) per month.

I have one table holding the "outer" (for want of a better term)
components of the invoice (client name, contract location, date,
invoice number, net value), linked to another table giving the
"invoice detail line" (service, price), and I have a form with a
subform for adding/editing the information. This is working OK, as is
the report which constitutes the actual invoice(s) (and a monthly
summary report).

What is the best way of (automatically) cloning/repeating one month's
set of records, the only differences being the date (to change from
the last day of this month to the last day of next month), the
invoice numbers (calculated using Dmax + 1), and the primary keys
(currently set up as autonumbers) of the two tables?

Thanks in advance for any comments.

David

 

[John Vinson]

What is the best way of (automatically) cloning/repeating one month's
set of records, the only differences being the date (to change from
the last day of this month to the last day of next month), the
invoice numbers (calculated using Dmax + 1), and the primary keys
(currently set up as autonumbers) of the two tables?

I would suggest running two Append queries from VBA code.

 

[SDH]

I would suggest running two Append queries from VBA code.

Thanks, John, I'll have a go.

As I understand it, append queries add rows to another table. So are
you suggesting that I set up a "scratchpad" table, add my set of
records to it with an append query, update the dates and invoice
numbers in that table, and then add them back to the original table
with a second append query (and then perhaps delete my scratchpad
table)?

Or are you saying I need two append queries because my data is in two
tables?

David