Need Access VBA Code (or example)

jdurand2

New member
Local time
Today, 12:40
Joined
Aug 10, 2010
Messages
6
I am working on a database tracking job costs and future projected costs.

So my form has (and these are user defined so they will change, but I am using queries to update my table to that is OK):
Filter for multiple jobs
Beginning date (User Defined, will change) = 2004
Ending date (User Defined, will change = 2006
Inflation rate (User Defined, will change = 3%

My table has:
ID Job Labor Level Year Amount

1 New York Manager 2002 $42.50
1 New York Supervisor 2002 $23.75
1 New York Associate 2002 $15.25
2 California Director 2004 $75.22
2 California Manager 2004 $45.25

And I am trying to make my table have:

ID Job Labor Level Year Amount
1 New York Manager 2002 $42.50
1 New York Supervisor 2002 $23.75
1 New York Associate 2002 $15.25
1 New York Manager 2003 $43.77
1 New York Supervisor 2003 $24.46
1 New York Associate 2003 $15.70
1 New York Manager 2004 $45.08
1 New York Supervisor 2004 $25.19
1 New York Associate 2004 $16.17
1 New York Manager 2005 $46.43
1 New York Supervisor 2005 $25.94
1 New York Associate 2005 $16.65
1 New York Manager 2006 $47.82
1 New York Supervisor 2006 $26.71
1 New York Associate 2006 $17.14
2 California Director 2004 $75.22
2 California Manager 2004 $45.25
2 California Director 2005 $77.47
2 California Manager 2005 $46.60
2 California Director 2006 $79.79
2 California Manager 2006 $47.99

So basically I need a VBA code that will copy each record increasing the year and amount until the it reached the year specified in the End year on my form. Not all of these records are in the same year, and some may already have data for multiple years (in which case I only want the years that are not there and the increase in amount to calculate on the most recent year.

Any help would be appreciated.
 
Attached is a sample to get you started. I can't guarantee I'll have more time to work on this, but feel free to post more questions.
 

Attachments

This code worked for me:
Public Sub CreateYears()
Dim rst As Recordset
Dim rsttemp As Recordset
Dim x As Integer
Dim mydate As Integer
Dim lastamount As Variant
x = [EndYear] - "2000" + 1
y = 0
Set rst = CurrentDb.OpenRecordset("select * from tbltable1 order by id")
Set rsttemp = CurrentDb.OpenRecordset("tbltemp")
rst.MoveFirst
Do Until rst.EOF
Do Until y = x
If Trim(rst!Year) = Trim("2000") Or Trim(rst!Year) = Trim("2000" + y) Then GoTo skipme
rsttemp.AddNew
rsttemp!Year = "2000" + y
rsttemp!ID = rst!ID
rsttemp!Name = rst!Name
rsttemp!Location = rst!Location
If y = 0 Then
rsttemp!Amount = 0
Else
rsttemp!Amount = 0
End If
lastamount = rsttemp!Amount
rsttemp.Update
skipme:
y = y + 1
Loop
lastamount = 0
y = 0
rst.MoveNext
Loop
End Sub

But now I need to loop through the records I created (this has to be a seperate set of code) and if there is a 0 I need to change the value to the previous amount increased by 3%, or skip if the previous amount is 0. Below is what I have, but it is not working:

Public Sub CalculateValues()
Dim rst As Recordset
Dim lastamount As Variant
Set rst = CurrentDb.OpenRecordset("select * from tbl_Rate_Reports2 order by OpportunityID")
rst.MoveFirst
Do Until rst.EOF
If rst!ActualPrice <> 0 Then
rst.MoveNext
Loop
rst!ActualPrice = 0
rst.Update
rst!ActualPrice = lastamount * [fld_Inflation] + lastamount
Else
rst.MoveNext
End If
End Sub

Any help would be great.
 
Maybe what you had in mind is this (the following code is untested) and it assumes you are pulling the records in the desired order (you have an "Order by OpportunityID" which I can only assume succeeds.

Dim rst As Recordset
Dim lastamount As Variant
Set rst = CurrentDb.OpenRecordset("select * from tbl_Rate_Reports2 order by OpportunityID")
rst.MoveFirst
Do Until rst.EOF
If rst!ActualPrice <> 0 Then
lastamount = rs!ActualPrice
Else
rs.Edit
rs!ActualPrice = lastamount * [fld_Inflation] + lastamount
rs.Update
End If
rst.MoveNext
Loop
 

Users who are viewing this thread

Back
Top Bottom