Depreciation Schedule

[Annette]

Is there a way with a query to calculate a depreciation schedule? I
have an assetID, DateAcquired, PurchasePrice and LifeSpan. I need to
list month end dates (monthly) along with the depreciation amount. I
would like this to display in a subform as a datasheet. It would have
a column for the date and for the depreciation like:
01/31/2006 100.00
02/28/2006 100.00
03/31/2006 100.00
etc.

I have a module I created that will determine the end date and the
amount, but I don't know how to get all those transactions to the
subform. Do I have to write the data from the module to a table before
I can reference in the form (I would rather not store it), or can I
have it just calculated and displayed with no storage (refer to it
only)?

Thanks for your help!

 

[Michel Walsh]

Access forms only allow to bind them with tables (while the framework allows
to bind Windows forms to almost anything), so, unless you have a very
special case (such as a list of 'values' for a combo box/list box), or
decide to create 'n' controls, dynamically, and push, through code, values
in each of these, it is probably preferable to append the data into a table,
and display it as subform (although you can also display the list of values
in a combo box, as I said).

You can insert 'n' times the same record in a (temp) table like this:


INSERT INTO temp(f1, f2, f3) SELECT value1, value2, value3 FROM Iotas
WHERE iota<=n


which implies you already have a table, Iotas, one field, its primary key,
iota, with values from 1 to, say, 999.


Sure, you will have initially deleted any record in your temp table, if
there are any:


DELETE * FROM temp


and through VBA code, that can be:


DoCmd.RunSQL "DELETE * FROM temp"
DoCmd.RunSQL "INSERT INTO temp(amount) " _
" SELECT FORMS!formName!Amount " _
" FROM Iotas WHERE iota<=FORMS!formName!NumberOfPayments"




Hoping it may help,
Vanderghast, Access MVP