WineSnob
Not Bright but TENACIOUS
- Local time
- Today, 00:57
- Joined
- Aug 9, 2010
- Messages
- 211
I have a table tblExtInc that has multiple records with fields
Here is the code I have to Update a table tblExternalIncomeSchedule
It works fine and updates the table for the first record.
How do I move to the next record StartYear 6 thru EndYear 10 and write that data to the same table without overwriting the data from the first record?
It should add 5 more records to the table
Yr 6 2000
Yr 7 2000 (since inflation = 0)
Yr 8 2000 (since inflation = 0)
Yr 9 2000 (since inflation = 0)
Yr 10 2000 (since inflation = 0)
Code:
StartYear EndYear Amount Inflation Freq Source
2 8 1000.00 .02 12 SS
6 10 2000.00 .00 12 Pension
Here is the code I have to Update a table tblExternalIncomeSchedule
It works fine and updates the table for the first record.
How do I move to the next record StartYear 6 thru EndYear 10 and write that data to the same table without overwriting the data from the first record?
It should add 5 more records to the table
Yr 6 2000
Yr 7 2000 (since inflation = 0)
Yr 8 2000 (since inflation = 0)
Yr 9 2000 (since inflation = 0)
Yr 10 2000 (since inflation = 0)
Code:
Function FillExternalIncomeSchedule(nClientID As Long, nProposalId As Integer, nProposalYears As Integer, nRetireAge As Integer)
Dim rst As Recordset
Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblExternalIncomeSchedule] WHERE [ProposalID] = " & nProposalId)
qdf.Execute
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblExternalIncomeSchedule")
xAge = nRetireAge - 1
NextExtinc = DLookup("Amount", "tblExtIncome")
iFreq = DLookup("Freq", "tblExtIncome")
For iYear = 1 To nProposalYears
xAge = xAge + 1
tSource = DLookup("Source", "tblExtIncome")
If iYear >= DLookup("StartYear", "tblExtIncome") Then
If iYear <= DLookup("EndYear", "tblExtIncome") Then
With rst
.AddNew
![ProposalID] = nProposalId
![ClientID] = nClientID
'![BucketYear] = nBucket
![Age] = xAge
![Yr] = iYear
![External Income] = NextExtinc
![AnnualExtIncome] = NextExtinc * iFreq
![Source] = tSource
.Update
End With
dinflation = DLookup("Inflation", "tblExtIncome")
tSource = DLookup("Source", "tblExtIncome")
NetInc = (cExternalIncome * dinflation) + NextExtinc
NextExtinc = NetInc + (NextExtinc * dinflation)
Debug.Print "Year" & iYear & " amt " & NextExtinc
End If
End If
Next iYear
End Function