Loop while renumbering two columns...This is an oldie but a goody... I needed to loop thru a Recordset after the slides had been added and make sure there were only 140 slides per tray AND make sure they were in sequential order AND I could change the TrayID or the PositionID at any time and still have it reorder...

The difficult I do immediately, the impossible takes a little bit longer.

PrivateSub cmdReorganize_Click()Dim Slide As Database
Dim Present As Recordset
Dim ITray AsInteger, IPosit AsInteger, IMax AsIntegerSet Slide = CurrentDb
Set Present = Slide.OpenRecordset("qryPresentationAddEdit", dbOpenDynaset)IfNotIsNull(Me.txtImageID)Then
IMax =140'Set Maximum number for Tray Position
Requery 'Refreshes screen
Present.Requery'Sorts to ensure first record is lowest value of tray, position
Present.MoveFirst'Ensures we start at first record
ITray = Present![txtTrayID]'Init Tray counter to value in first record
IPosit =1'forces position 1Do Until Present.EOF'loop through to End Of FileIf IPosit > IMax Then'Last one updated was at maximum or last position
IPosit =1'Reset
ITray = ITray +1'Increment TrayEndIfIf ITray < Present![txtTrayID]Then'Changed tray number, continue numbering from 1 of next tray
IPosit =1'Reset
ITray = Present![txtTrayID]'Set TrayEndIf
Present.Edit'Enable updating
Present![txtPositionID]= IPosit 'Set new Position in Tray
Present![txtTrayIDr]= ITray 'Set new Tray
Present![chkPositionFlag]=0'Set Position Flag to OFF
Present.Update
Present.MoveNext'Move to next record
IPosit = IPosit +1'Increment Position in TrayLoop'end of EOF do loop
Present.Requery'Sort records into new order
Requery 'Refreshes screen
DoCmd.GoToRecord , , acNext 'Moves to the next record
Present.Close'qryPresentationAddEditElse
MsgBox "Please look up a Presentation!", vbExclamation
EndIfEndSub

Once all the Images for the Presentation had been selected all I had to do was click the Reorganize button (above). If you look at the example (purple circle) some changed to Tray 2 and the PositionID has been adjusted because the limit of 140 slides in Tray 1 had been reached.