By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Add records on days selected by checkbox between date range

P: 3
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Weekdays_Click()
  2.  
  3. DoCmd.SetWarnings False
  4.  
  5. Dim dteIterator As Date
  6. dteIterator = Me!StartDate
  7. Do While dteIterator <= Me!EndDate
  8. If Weekday(dteIterator, vbSunday) <> vbSaturday And _
  9. Weekday(dteIterator, vbSunday) <> vbSunday Then
  10.     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);"
  11. End If
  12. dteIterator = DateAdd("d", 1, dteIterator)
  13. Loop
  14.  
  15. DoCmd.SetWarnings True
  16.  
  17. MsgBox "Your shifts have been added!"
  18.  
  19. DoCmd.Close
  20.  
  21. End Sub
  22.  
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.
Jan 7 '08 #1
Share this Question
Share on Google+
1 Reply


P: 45
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Weekdays_Click()
  2.  
  3. DoCmd.SetWarnings False
  4.  
  5. Dim dteIterator As Date
  6. dteIterator = Me!StartDate
  7. Do While dteIterator <= Me!EndDate
  8. If Weekday(dteIterator, vbSunday) <> vbSaturday And _
  9. Weekday(dteIterator, vbSunday) <> vbSunday Then
  10.     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);"
  11. End If
  12. dteIterator = DateAdd("d", 1, dteIterator)
  13. Loop
  14.  
  15. DoCmd.SetWarnings True
  16.  
  17. MsgBox "Your shifts have been added!"
  18.  
  19. DoCmd.Close
  20.  
  21. End Sub
  22.  
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).
Jan 11 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.