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

[nakamichie]

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,

 

[MarkK]

Change this . . . .

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

. . . to this . . .

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

. . . so as the value of your loop counter 'i' goes up, your refillamount is proportionately reduced.

 

[nakamichie]

Smoking - thanks, that worked.