Add records on days selected by checkbox between date range

[AllyFrog]

Hello,

I have a database which manages bookings for casual staff. When bookings are for just one day, then they can be entered as a new record into a form. Frequently however, a booking may be for a staff member to attend every week day for several weeks. Rather than enter each shift in individually I have a form which allows the user to enter dates and shift times, and then hit a button which runs some code (that people from the scripts helped me with - thank you) that adds a record for each weekday between the start and end date. This code is as follows:

Private Sub Weekdays_Click()
 
DoCmd.SetWarnings False
 
Dim dteIterator As Date
dteIterator = Me!StartDate
Do While dteIterator <= Me!EndDate
If Weekday(dteIterator, vbSunday) <> vbSaturday And _
Weekday(dteIterator, vbSunday) <> vbSunday Then
    DoCmd.RunSQL "INSERT INTO tblshifts(ShiftDate, Shiftbookedby, Shiftbookeddate, Shiftbookedtime, shiftclid, shiftstart, shiftend, shiftbreaks, shiftactstart, shiftactend, shiftactbreaks, shiftmargin, shiftrole, shiftspecialrate, shiftnotes, shiftstatus) VALUES (#" & Format(dteIterator, "mm/dd/yyyy") & "#, ' " & Me![BookedBy] & " ', #" & Format(Me!BookedOn, "mm/dd/yyyy") & "#, #" & Format(Me!BookedTime, "hh:mm") & "#, ' " & Me!Centre & " ', #" & Format(Me!StartTime, "hh:mm") & "#, #" & Format(Me!EndTime, "hh:mm") & "#, ' " & Me!Breaks & " ', #" & Format(Me!StartTime, "hh:mm") & "#, #" & Format(Me!EndTime, "hh:mm") & "#, ' " & Me!Breaks & " ', ' " & Me!Margin & " ', ' " & Me!Role & " ', ' " & Me!SpecialRate & " ', ' " & Me!Notes & " ', 1);"
End If
dteIterator = DateAdd("d", 1, dteIterator)
Loop
 
DoCmd.SetWarnings True
 
MsgBox "Your shifts have been added!"
 
DoCmd.Close
 
End Sub
 

This all works fine. My new issue is that sometimes shifts repeat but they aren't every weekday - say for example a shift repeats on Monday and Wednesday for a number of weeks. What I would like to do is add to the form a check box for each day of the week, and then, when the user clicks the button, for the code to run adding shifts for the days selected between the two dates. I'm guessing this will involve altering the above code with some If and Else Ifs but I'm not quite sure how to put it all together.

Any help or suggestions would be greatly appreciated.

[zaidlig]

Hello,

I have a database which manages bookings for casual staff. When bookings are for just one day, then they can be entered as a new record into a form. Frequently however, a booking may be for a staff member to attend every week day for several weeks. Rather than enter each shift in individually I have a form which allows the user to enter dates and shift times, and then hit a button which runs some code (that people from the scripts helped me with - thank you) that adds a record for each weekday between the start and end date. This code is as follows:

Private Sub Weekdays_Click()
 
DoCmd.SetWarnings False
 
Dim dteIterator As Date
dteIterator = Me!StartDate
Do While dteIterator <= Me!EndDate
If Weekday(dteIterator, vbSunday) <> vbSaturday And _
Weekday(dteIterator, vbSunday) <> vbSunday Then
    DoCmd.RunSQL "INSERT INTO tblshifts(ShiftDate, Shiftbookedby, Shiftbookeddate, Shiftbookedtime, shiftclid, shiftstart, shiftend, shiftbreaks, shiftactstart, shiftactend, shiftactbreaks, shiftmargin, shiftrole, shiftspecialrate, shiftnotes, shiftstatus) VALUES (#" & Format(dteIterator, "mm/dd/yyyy") & "#, ' " & Me![BookedBy] & " ', #" & Format(Me!BookedOn, "mm/dd/yyyy") & "#, #" & Format(Me!BookedTime, "hh:mm") & "#, ' " & Me!Centre & " ', #" & Format(Me!StartTime, "hh:mm") & "#, #" & Format(Me!EndTime, "hh:mm") & "#, ' " & Me!Breaks & " ', #" & Format(Me!StartTime, "hh:mm") & "#, #" & Format(Me!EndTime, "hh:mm") & "#, ' " & Me!Breaks & " ', ' " & Me!Margin & " ', ' " & Me!Role & " ', ' " & Me!SpecialRate & " ', ' " & Me!Notes & " ', 1);"
End If
dteIterator = DateAdd("d", 1, dteIterator)
Loop
 
DoCmd.SetWarnings True
 
MsgBox "Your shifts have been added!"
 
DoCmd.Close
 
End Sub
 

This all works fine. My new issue is that sometimes shifts repeat but they aren't every weekday - say for example a shift repeats on Monday and Wednesday for a number of weeks. What I would like to do is add to the form a check box for each day of the week, and then, when the user clicks the button, for the code to run adding shifts for the days selected between the two dates. I'm guessing this will involve altering the above code with some If and Else Ifs but I'm not quite sure how to put it all together.

Any help or suggestions would be greatly appreciated.

Actually this would be a simple change just add a check to the lines 8 and 9 in your code to verify which days are checked on the form. Currently you check to make sure the day you are looping through is not Saturday or Sunday - just expand the check to include the new check boxes on on your form (Mon - Fri).