Update table from controls on a report

[Sam]

Access 2000

When this database was designed, invoices were pulled from
a query which performs all the relevant calculations etc.

Now the client wants to be able to reprint old invoices,
so they can't be run from the query. The old information
isn't stored anywhere in the table (ie. price changes,
etc.)

I've created a new table with all relevant invoice
fields. I think I need to use a query to capture the info
from the report upon printing using Reports!reportname!
fieldname, then run an update query to put the information
in the new table, then create a duplicate report which
pulls from the new table on reprint.

Can I capture values from calculated controls on the
report? If not, does anyone have any other suggestions?

Thank you,
Sam

 

[Duane Hookom]

I would add a field in your billing details for tracking the invoice number.
Then update the invoice numbers when ready to do your billings. Then your
reports can be filter/grouped by invoice number.

 

[John Vinson]

Can I capture values from calculated controls on the
report? If not, does anyone have any other suggestions?

Possibly, in the Format event of the Report; but a far better idea
IMHO would be to do all the calculations in a Query and run an append
query using the same calculations.

Best of all would be to use a table design that retains the (say)
price as of a certain date, or an archive table recording all changes.
This leads to a fair bit more complexity but prevents this kind of
problem; in addition, if you realize that you're doing a calculation
incorrectly, you can correct the calculation and go back to the
original real data to redo the calculation correctly.

 

[sam]

Best of all would be to use a table design that retains the (say)
price as of a certain date, or an archive table recording all changes.
This leads to a fair bit more complexity but prevents this kind of
problem; in addition, if you realize that you're doing a calculation
incorrectly, you can correct the calculation and go back to the
original real data to redo the calculation correctly.

I agree but I didn't design the darn thing
Unfortunately my client didn't want to go to the expense -
they've got three years' worth of invoices - they just
want to start saving them from now on.

Thanks for the help. I didn't want to have to change the
report (as there are four or five based on this query, all
running calculations in controls and if I change one, I
probably need to change them all - ugh) so I'll probably
have to duplicate the calculations in a query to append
the invoice data. And hope that I have done them all
correctly!

Any other suggestions for doing this are very welcome.

Thanks again,
Sam

 

[John Vinson]

I agree but I didn't design the darn thing
Unfortunately my client didn't want to go to the expense -

<sigh> Penny wise, pound foolish.