Tracking Depreciation with Access
I recently asked for help in finding a database template for tracking the depreciation of assets
(https://www.experts-exchange.com/questions/25519267/Access-Database-for-tracking-assets-and-Depreciation.html?anchorAnswerId=28579100#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?
(https://www.experts-exchange.com/questions/25519267/Access-Database-for-tracking-assets-and-Depreciation.html?anchorAnswerId=28579100#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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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/
Next
End Sub
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/
Next
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
bblu,
field names in table depreciation
--------------------------
currentdb.execute "insert into depreciation(Assetid,Depre
field names in table depreciation
--------------------------
currentdb.execute "insert into depreciation(Assetid,Depre
ASKER
oh.. got it. so I don't need to open the recordsource or anything? In what case(s) might I have to do that?
< 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?>
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?>
ASKER
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.
ASKER
Thanks to both of you.
ASKER