How to avoid using an update during each cycle of a loop?

[Steve82]

I've written some code that calculates the interest portion of 180 loan payments and updates the fields INT1 …INT180. It works fine but takes forever to run because its doing 180 updates per record. Is their a more efficient way to do this?
Public Function IntPmt()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim f As String
Dim t As Double
Dim c As Double
Dim r As Double
Dim b As Double
Set db = CurrentDb()
Set rs = db.OpenRecordset("tbl_IntPmt", dbOpenDynaset)
Do While Not rs.EOF
t = rs![TERM]
r = rs![Rate] / 12


b = rs![RPMT BAL]
c = 1
For c = 1 To t
f = "INT" & c
rs.Edit
rs(f) = Abs(IPmt(r, c, t, b, 0, 0))
rs.Update
Next c
rs.MoveNext
Loop

rs.Close

Set rs = Nothing
Set db = Nothing
End Function

[June7]

Did you try putting the rs.Update outside the loop?

Why are you saving calculated data?

[Steve82]

I tired that but it wouldn't work. I'm saving because I'm generating an amortization schedule; principal, interest and dates paid.

[June7]

Still don't understand why you can't just do a query with the calcs and use that as the amortization 'table'.

Actually, try the Update just after the Next c line. Should only have to update the record once, not after each field populated.

[Steve82]

I'm trying to reduce the code. I'm using a variable to change the field names so I don't have to list all 180 fields. Update after Next c didn't work.

[June7]

I understand the technique and use it.

I have a procedure that opens a recordset, adds a new record, in a loop populates each field of the new record, then outside the loop runs the Update. It works. Unfortunately, never had need to do this with existing record. Probably also need to move the rs.Edit outside the For loop, keep the two commands at the same level:

Do While Not rs.EOF
t = rs![TERM]
r = rs![Rate] / 12
b = rs![RPMT BAL]
c = 1
rs.Edit
For c = 1 To t
f = "INT" & c
rs(f) = Abs(IPmt(r, c, t, b, 0, 0))
Next c
rs.Update
rs.MoveNext
Loop

Having 180 similar rows indicates non-normalized data structure.

[Steve82]

I'll give it a shot. I guess I could read from on table or record set and add to another. Is that what you're talking about?

[June7]

My procedure is taking data from one record and creating a new record in same table.

In your case, I suggested you calculate the interest values in a query and use the query as source for amortization values but you would have to build 180 expressions because of non-normalized table structure.