Tracking Depreciation with Access

I recently asked for help in finding a database template for tracking the depreciation of assets
(http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_25519267.html#a28579100)

This one from Microsoft:
http://office.microsoft.com/en-us/templates/TC010184591033.aspx?CategoryID=CT101426031033&av=ZAC000)

seems to be pretty good, but it appears that I'd have to enter each month's depreciation amount for any new asset I add.   The depreciation table has 3 fields (besides the unique ID): Asset ID, Depreciation Date, and Depreciation Amt.  Unless I'm reading it wrong, if I were to add a new asset that had a 1-year depreciation life and was worth $1200, I'd have to create 12 entries depreciating $100.  Is there some VBA script that I can write that will add the records automatically?  Something like a for loop that adds (12*depreciation years) records?

BBluAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
you can do this

dim j, dDate as date, aDate as date, lngId as long, vAmount as double
aDate=#3/1/2010#
lngId=AssetID
for j=1 to 12
   
  dDate=dateserial(year(adate),month(adate)+j,0)

  currentdb.execute "insert into depreciation(Assetid,Depreciationdate,depreciationAmount) values("& lngID &",#"& dDate &"#, " & vAmount &")"

next



0
 
BBluAuthor Commented:
this is dumb question, but how do I run this function in Access.  I know how (sort of) to call them in Excel, but not in Excel.
0
 
BBluAuthor Commented:
I created a form with a subform of the the depreciation table.

I added button and I assume I need to assign to it the action of running the above code.  But I don't know how to go about doing it.  Any help is appreciated.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
BBluAuthor Commented:
I was able to figure out how to add the code to the Click Event of a button.  And to see if it would work, I hardcoded some of the vaules (without having to have it come from completed fields).  But I'm getting a run-time 3134 error.  Do I need to open a recordsource or something first?

Here's what I have:

Private Sub Command1_Click()
Dim j, dDate As Date, aDate As Date, lngId As Long, vAmount As Double
aDate = #3/1/2010#
lngId = 1
For j = 1 To 12
   
  dDate = DateSerial(Year(aDate), Month(aDate) + j, 0)

  CurrentDb.Execute "insert into depreciation(Assetid,#3/1/2010,2500) values(" & lngId & ",#" & dDate & "#, " & vAmount & ")"

Next
End Sub
0
 
peter57rConnect With a Mentor Commented:
CurrentDb.Execute "insert into depreciation(Assetid,#3/1/2010,2500)

this part of the command must list the fieldnames where you want to add the data, not data values.
0
 
Rey Obrero (Capricorn1)Commented:
bblu,
                                                                        field names in table depreciation
---------------------------------------------------------vvvv-----vvvvvvvv-----------vvvvvvvvv
 currentdb.execute "insert into depreciation(Assetid,Depreciationdate,depreciationAmount) values("& lngID &",#"& dDate &"#, " & vAmount &")"

0
 
BBluAuthor Commented:
oh.. got it.  so I don't need to open the recordsource or anything?  In what case(s) might I have to do that?
0
 
Rey Obrero (Capricorn1)Commented:
< In what case(s) might I have to do that?>
because of this

<Is there some VBA script that I can write that will add the records automatically?  Something like a for loop that adds (12*depreciation years) records?>
0
 
BBluAuthor Commented:
Last question: If  I want to run this anytime a new asset record is created (instead of having it as part of the click event of a button), how would I do that.
0
 
BBluAuthor Commented:
Thanks to both of you.
0
All Courses

From novice to tech pro — start learning today.