Calculate Depreciation and update subform

[ragu111]

i created a simple database for keeping the track of Asset.
in the main form i have below fields:

"Date of Purchase" "Purchase Price" "Depreciable Life"

on the subform have three fields

"Month No" "date(dd/mm/yy)" "depreciation Value"

i need the subform to be updated with date and depreation value as below by an command click

Main form Data:
"Date of Purchase" = 10/01/04
"Purchase Price"   = 18000
"Depreciable Life" = 3 yrs

subForm to be filled like:
                        Depreci.
MonthNo Date	  	Value
1	10/02/2004	500
2	10/03/2004	500
3	10/04/2004	500
4	10/05/2004	500
5	10/06/2004	500
6	10/07/2004	500
7	10/08/2004	500
8	10/09/2004	500
9	10/10/2004	500
10	10/11/2004	500
11	10/12/2004	500
12	10/01/2005	500
13	10/02/2005	500
14	10/03/2005	500
15	10/04/2005	500
16	10/05/2005	500
17	10/06/2005	500
18	10/07/2005	500
19	10/08/2005	500
20	10/09/2005	500
21	10/10/2005	500
22	10/11/2005	500
23	10/12/2005	500
24	10/01/2006	500
25	10/02/2006	500
26	10/03/2006	500
27	10/04/2006	500
28	10/05/2006	500
29	10/06/2006	500
30	10/07/2006	500
31	10/08/2006	500
32	10/09/2006	500
33	10/10/2006	500
34	10/11/2006	500
35	10/12/2006	500
36	10/01/2007	500

can someone help me with the code

ragu

 

[PHV]

Are your forms bound to some tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ragu111]

yes its bound to a table called Asset_Dep_Details.

 

[PHV]

As you talked about about a main form and a subform, I expected TWO tables.
Could you please post the schema of each table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ragu111]

yes, the header table and the details table. both linked with Asset_ID.
currently i have an option to enter the details table data manually, i need the code to update the changes in detail table by a command click.

ragu

 

[PHV]

As you refuse to describe properly your environment, here a starting point with guessed control/table/field names:

intCountOfMonth = 12 * Me![Depreciable Life]
curValue = Me![Purchase Price] / intCountOfMonth
For m = 1 To intCountOfMonth
DoCmd.RunSQL "INSERT INTO Asset_Dep_Details (Asset_ID,MonthNo,[Date],[Depreciable Value]) VALUES (" _
& Me![Asset_ID] & "," & m & ",#" & DateAdd("m", m, Me![Date of Purchase]) & "#," & curValue & ")"
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ragu111]

Great PHV

It's working fine

Ragu

 

[ragu111]

here i'm again,,,

found a small problem..

when the [Date of Purchase] day is < 12 the [date] field is getting updated in the format of mm/dd/yyyy and if the day is above 12 then it's working fine (dd/mm/yyyy)

if the [Date of Purchase] = 09/04/2005 (09-apr-05)

[date]updated as:
04/09/2005
06/09/2005
07/09/2005
08/09/2005
09/09/2005
10/09/2005
11/09/2005
12/09/2005
01/09/2006

[date]should be:
09/05/2005
09/06/2005
09/07/2005
09/08/2005
09/09/2005
09/10/2005
09/11/2005
09/12/2005
09/01/2006

how it happens..?

 

[PHV]

DoCmd.RunSQL "INSERT INTO Asset_Dep_Details (Asset_ID,MonthNo,[Date],[Depreciable Value]) VALUES (" _
& Me![Asset_ID] & "," & m & ",#" & [!]Format([/!]DateAdd("m", m, Me![Date of Purchase])[!], "yyyy-mm-dd")[/!] & "#," & curValue & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ragu111]

Dear PHV

one more change require...

if the [Date of Purchase] = 09/04/2005 (09-apr-05)

[date]should be:
09/04/2005
09/05/2005
09/06/2005
09/07/2005
09/08/2005
......
......

the starting(first) date whould be 09/04/2005 instead of 09/05/2005

Ragu

 

[ragu111]

Dear PHV

here i'm coming with one more help

if [Purchase Price] = 50000 and
[Depreciable Life] = 5 then
per month [Depreciable Value] updates as 833.333333

i made a CrossTab Query by year which shows a amount of 9999.99 and the ColumnTotal 49999.99

which should be 10000 per year and the ColumnTotal 50000

i know the system calculates numbers, but is there any way to round up the value in CrossTab because the output reports shows a funny value

Ragu