Moving from one record to the next (1 Viewer)

[WineSnob]

I have a table tblExtInc that has multiple records with fields

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)

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

 

[pbaldy]

For starters, since your DLookup's don't have a criteria, they will always use the first record. Is that appropriate?

 

[WineSnob]

Hey Paul.
I thought of that but didn't know what criteria to use. I filter the table down to the records related to the ProposalID only. There should only be a few records associated with each ProposalID maybe 5-6 max. Usually 2-3

 

[rzw0wr]

Rick, My I ask why you are using a recordset to do this?
This should be straight forward with a form if you table structure is correct.

Some good reading.

http://symplebyte.com/microsoft_office/access/how_to_normalize_data_access.html
and
View attachment Entity_Relationship.zip

Dale