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
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