Tracking Depreciation with Access

I recently asked for help in finding a database template for tracking the depreciation of assets

This one from Microsoft:

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?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
you can do this

dim j, dDate as date, aDate as date, lngId as long, vAmount as double
for j=1 to 12

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



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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 & ")"

End Sub
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.
Rey Obrero (Capricorn1)Commented:
                                                                        field names in table depreciation
 currentdb.execute "insert into depreciation(Assetid,Depreciationdate,depreciationAmount) values("& lngID &",#"& dDate &"#, " & vAmount &")"

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?
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?>
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.
BBluAuthor Commented:
Thanks to both of you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.