Reading from a database

[Israelsimba]

I have a subscription system that i have developed. the system is supposed to automatically subtract the number of copies given to a client every monthend as per request they made of the copies required upon joining. my problem is i cant make my code run through my database so that it can subtract from every record....instaed it can deal with one record at a time. i cant manually subtract because the records at one time are going to reach thousands. i justa want it to automatically subtract from all fields when a button is clicked.

i have een trying to use this code but its not working.

With rc
.ActiveConnection = deSub.conn
.LockType = adLockOptimistic
.Source = "select * from cooperatesubs"
.Open
End With
z = rc.Fields("numberofcopies")
Do Until rc.EOF
rc.AddNew
rc.Fields("numberofcopies") = z
rc.Fields("copiesgiven") = y
rc.Fields("copiesremaining") = z - y
rc.Update
rc.MoveNext
Loop
Exit Sub

[AndreRet]

Try the following -

With rc
.ActiveConnection = deSub.conn
.LockType = adLockOptimistic
.Source = "select * from cooperatesubs"
.Open
End With

Dim w As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer

Do Until rc.EOF = True

y = rc.Fields("copiesgiven") 
z = rc.Fields("numberofcopies")
x = z - y
w = y + 1

rc.AddNew
'rc.Fields("numberofcopies") = z This will not change, for it is a set number, correct?
rc.Fields("copiesgiven") = w
rc.Fields("copiesremaining") = x
rc.Update
rc.MoveNext
Loop
Exit Sub

[Israelsimba]

i strongly believe the problem is with the

.movenext

part. its not moving to the next record wich makes it effective on one row only and fails to run through the database.
i was wondering isnt there any other way of doing this besides this one
thanx you