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


"Annette" <annettem@co.saint-croix.wi.us> wrote in message 
news:1174567323.085629.130490@n59g2000hsh.googlegroups.com...
> 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!
>