Depreciation Schedule

  • Thread starter Thread starter Annette
  • Start date Start date
A

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!
 
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
 
Back
Top