[rpes]

Hello,

I am looking for some help with looping through a recordset and performing an action that involves each record. The problem involves two tables; tblShiftID and tblNames.
tblShiftID has two fields; IdStart and IdEnd (both are number fields). TblNames has several fields but my only concern is a field named ShiftID. The ShiftID in tblNames needs to be changed to generate new schedules.

tblShiftID's two fields give the start (IdStart) and end (IdEnd) of an employees rotation through a schedule - i.e. 1-7. If the employee is on ShiftID 2 this week I need him on 3 next week. Another employee may be on rotation 11-17 with his ShiftID as 17. I would need his ShiftID to be 11 next week.

There are 43 sets of rotations like this so a loop would be great. The following works to change the 1-7 rotation:

DoCmd.RunSQL "UPDATE tblNames SET tblNames.ShiftID = IIf([ShiftID]=7,1,[ShiftID]+1)" & _
"WHERE (((tblNames.ShiftID) Between 1 And 7));"

Then for the 11-17 rotation:

DoCmd.RunSQL "UPDATE tblNames SET tblNames.ShiftID = IIf([ShiftID]=17,11,[ShiftID]+1)" & _
"WHERE (((tblNames.ShiftID) Between 11 And 17));"

The new code would loop through and update each employees ShiftID getting the 1 from tblShiftID.IdStart and the 7 from tblShiftID.IdEnd then move to the next record to the 11 and 17 and so on.

Thank you in advance for any help.

Rick

[rpes]

The problem has been solved. Here is the final code in case it might help someone out:


Private Sub cmdRotate_Click()
If MsgBox("You are about to change the ShiftID numbers in order to create a new schedule!!! Are you sure?", vbYesNo, "Continue?") = vbYes Then
Dim db As Database
Dim rs As Recordset
Dim iCount As Integer
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM tblShiftID;") 'open the recordset for use

With rs

If Not (rs.BOF And rs.EOF) Then 'Ensure that there are actually records to work with
rs.MoveLast 'This is required otherwise you may not get the right count
iCount = rs.RecordCount 'Determine the number of returned records
rs.MoveFirst 'not necessary but starting from the first record to troubleshoot problems

Do While Not .EOF

DoCmd.RunSQL "UPDATE tblNames SET tblNames.ShiftID = IIf([ShiftID]=" & rs!IdEnd & ", " & rs!IdStart & ",[ShiftID]+1) " & _
"WHERE (((tblNames.ShiftID) Between " & rs!IdStart & " AND " & rs!IdEnd & "));"

.MoveNext

Loop
End If
End With
Else
End If
End Sub

[bootneck02]

Thanks for the heads up rpes, an answer to a question is always of help someone else. Oh hello & welcome to PCR nice to have you along.