Create A loop to add new records and decrease amount by one

I have the following code that works fine:



Dim db As DAO.Database

Dim rs As DAO.Recordset, i As Integer, ii As Integer

Set db = CurrentDb

Set rs = db.OpenRecordset("PatientPrescriptions1")



ii = [RefillAmount]



For i = 2 To ii





rs.AddNew

rs("PatientID") = [PatientID]

rs("PrescriptionsID") = [PrescriptionsID]

rs("RefillAmount") = [RefillAmount] - 1

rs("Quantity") = [Quantity]

rs("DatePrescribed") = [DatePrescribed]

rs("RXNumber") = [RXNumber]

rs("Price") = [Price]

rs("DoctorID") = [DoctorID]

rs("Location") = [Location]

rs("DateExpired") = [DatePrescribed] + [Quantity]



rs.Update



Next i

rs.Close

db.Close



---------------



However I am trying to make it decrease the value in [RefillAMount] each time it loops through the addnew function and I can't figure out how to do the rows keep saying the same number:



If I put Refill 3 it creates 2 extra rows and all these rows now say Refills = 2. What I want it to say is



Refills 3

Refills 2

refills 1



And end there. I can't figure out for the life me how to do this and am a very beginner on this one.



Any help would be great!



Thanks,

Create A loop to add new records and decrease amount by one