Calendar bookings represented on a room booking form

[phill86]

Hi,

I am trying to represent on a form time slots I have re-created the look of an outlook calendar on the form I have a table with the calendar bookings start and end time and there could be any number of bookings in a day. The following code represents the column for the first day of the week (monday) and formats the text boxes accordingly if the room is booked.

This all works fine but I have to have this code again to represent the secound day (tuesday) third day, fourth day etc

I have got it working for Monday, Tuesday and Wednesday but if I go any further I am getting the error message "compile error procedure too large" I have to repeat this process for anywhere up to 20 calendars is there any way that I can cut this code down so that is not too large for access to handle

Any help is much appreciated

Regards Phill

 
Dim dbsEquipBook As DAO.Database
Dim rstCheck As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim MyDate, MyWeekDay
Dim FirstDay As Date
Dim LastDay As Date
Dim MondayForm As Date
Dim TuesdayForm As Date
Dim WednesdayForm As Date
Dim ThursdayForm As Date
Dim FridayForm As Date
Dim SaturdayForm As Date
Dim SundayForm As Date
Dim BookedStartTimeDate As Date
Dim BookedEndTimeDate As Date
Dim BookedStartTime As Date
Dim BookedEndTime As Date
Dim txtMon0800 As Date
Dim txtMon0830 As Date
Dim txtMon0900 As Date
Dim txtMon0930 As Date
Dim txtMon1000 As Date
Dim txtMon1030 As Date
Dim txtMon1100 As Date
Dim txtMon1130 As Date
Dim txtMon1200 As Date
Dim txtMon1230 As Date
Dim txtMon1300 As Date
Dim txtMon1330 As Date
Dim txtMon1400 As Date
Dim txtMon1430 As Date
Dim txtMon1500 As Date
Dim txtMon1530 As Date
Dim txtMon1600 As Date
Dim txtMon1630 As Date
Dim txtMon1700 As Date
Dim txtMon1730 As Date
Dim txtMon1800 As Date
Dim txtMon1830 As Date
Dim txtMon1900 As Date
Dim txtMon1930 As Date
Dim txtMon2000 As Date
Dim txtMon2030 As Date
Dim txtMon2100 As Date
Dim txtMon2130 As Date
Dim txtMon2200 As Date
Dim txtMon2230 As Date
Dim txtMon2300 As Date
Dim txtMon2330 As Date
'this checks to see if euipment is booked then populates sform with time info which is then picked up by conditional formatting
 
    Set dbsEquipBook = CurrentDb()
    Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
 
qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![TxtMon]
qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104].[Form]![sun]
 
 
'works out the first and last day of the week acoording to the selected date from mydate which is a calendar control on the form that the user selects
MyDate = [Forms]![FrmCalendarMain].[frmCalendar]![txtDate]
MyWeekDay = Weekday(MyDate)
 
 
FirstDay = MyDate - MyWeekDay + 2
LastDay = MyDate - MyWeekDay + 8
 
'writes the first and last date to form
 
[Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon] = FirstDay
[Forms]![FrmCalendarMain].[SfrCalS104]![sun] = LastDay
 
MondayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon]
TuesdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![tues]
WednesdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![weds]
ThursdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![thurs]
FridayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![fri]
SaturdayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![sat]
SundayForm = [Forms]![FrmCalendarMain].[SfrCalS104]![sun]
 
 
 
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
 
Do While Not rstCheck.EOF
 
 
 
BookedStartTimeDate = Format(rstCheck!StartDate, "dd mm yyyy")
BookedEndTimeDate = Format(rstCheck!EndDate, "dd mm yyyy")
 
 
 
If BookedStartTimeDate = MondayForm Then
 
   BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
   BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
''this is here to handle the midnight time issue
 
   If BookedEndTime = "00:00:00" Then
 
   BookedEndTime = "23:59:59"
   End If
'this picks up the value of the text boxes on the form that represent the time slots
 
   txtMon0800 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00]
   txtMon0830 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30]
   txtMon0900 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00]
   txtMon0930 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30]
   txtMon1000 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00]
   txtMon1030 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30]
   txtMon1100 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00]
   txtMon1130 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30]
   txtMon1200 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00]
   txtMon1230 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30]
   txtMon1300 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00]
   txtMon1330 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30]
   txtMon1400 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00]
   txtMon1430 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30]
   txtMon1500 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00]
   txtMon1530 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30]
   txtMon1600 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00]
   txtMon1630 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30]
   txtMon1700 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00]
   txtMon1730 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30]
   txtMon1800 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00]
   txtMon1830 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30]
   txtMon1900 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00]
   txtMon1930 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30]
   txtMon2000 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00]
   txtMon2030 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30]
   txtMon2100 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00]
   txtMon2130 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30]
   txtMon2200 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00]
   txtMon2230 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30]
   txtMon2300 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00]
   txtMon2330 = [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30]
 
'this checks through to see if there is a calendar entry and formats the text boxes accordingly for the monday colum
 
If txtMon0800 >= [BookedStartTime] And txtMon0800 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:00].Enabled = False
End If
 
If txtMon0830 >= [BookedStartTime] And txtMon0830 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon08:30].Enabled = False
 
End If
 
If txtMon0900 >= [BookedStartTime] And txtMon0900 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:00].Enabled = False
End If
 
If txtMon0930 >= [BookedStartTime] And txtMon0930 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon09:30].Enabled = False
End If
 
If txtMon1000 >= [BookedStartTime] And txtMon1000 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:00].Enabled = False
 
End If
 
If txtMon1030 >= [BookedStartTime] And txtMon1030 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon10:30].Enabled = False
 
End If
 
If txtMon1100 >= [BookedStartTime] And txtMon1100 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:00].Enabled = False
 
End If
 
If txtMon1130 >= [BookedStartTime] And txtMon1130 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon11:30].Enabled = False
 
End If
 
If txtMon1200 >= [BookedStartTime] And txtMon1200 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:00].Enabled = False
 
End If
 
If txtMon1230 >= [BookedStartTime] And txtMon1230 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon12:30].Enabled = False
 
End If
If txtMon1300 >= [BookedStartTime] And txtMon1300 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:00].Enabled = False
 
End If
 
If txtMon1330 >= [BookedStartTime] And txtMon1330 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon13:30].Enabled = False
 
End If
If txtMon1400 >= [BookedStartTime] And txtMon1400 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:00].Enabled = False
 
End If
 
If txtMon1430 >= [BookedStartTime] And txtMon1430 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon14:30].Enabled = False
 
End If
If txtMon1500 >= [BookedStartTime] And txtMon1500 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:00].Enabled = False
 
End If
 
If txtMon1530 >= [BookedStartTime] And txtMon1530 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon15:30].Enabled = False
 
End If
 
If txtMon1600 >= [BookedStartTime] And txtMon1600 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:00].Enabled = False
 
End If
 
If txtMon1630 >= [BookedStartTime] And txtMon1630 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon16:30].Enabled = False
 
End If
 
If txtMon1700 >= [BookedStartTime] And txtMon1700 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:00].Enabled = False
 
End If
 
If txtMon1730 >= [BookedStartTime] And txtMon1730 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon17:30].Enabled = False
 
End If
If txtMon1800 >= [BookedStartTime] And txtMon1800 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:00].Enabled = False
 
End If
 
If txtMon1830 >= [BookedStartTime] And txtMon1830 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon18:30].Enabled = False
 
End If
If txtMon1900 >= [BookedStartTime] And txtMon1900 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:00].Enabled = False
 
End If
 
If txtMon1930 >= [BookedStartTime] And txtMon1930 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon19:30].Enabled = False
 
End If
If txtMon2000 >= [BookedStartTime] And txtMon2000 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:00].Enabled = False
 
End If
 
 
If txtMon2030 >= [BookedStartTime] And txtMon2030 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon20:30].Enabled = False
 
End If
 
If txtMon2100 >= [BookedStartTime] And txtMon2100 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:00].Enabled = False
 
End If
 
If txtMon2130 >= [BookedStartTime] And txtMon2130 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon21:30].Enabled = False
 
End If
If txtMon2200 >= [BookedStartTime] And txtMon2200 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:00].Enabled = False
 
End If
 
If txtMon2230 >= [BookedStartTime] And txtMon2230 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon22:30].Enabled = False
 
End If
If txtMon2300 >= [BookedStartTime] And txtMon2300 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:00].Enabled = False
 
End If
 
If txtMon2330 >= [BookedStartTime] And txtMon2330 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon23:30].Enabled = False
 
End If
If txtMon0000 >= [BookedStartTime] And txtMon0000 < [BookedEndTime] Then
 
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon00:00].BackColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon00:00].ForeColor = RGB(191, 191, 191)
        [Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon00:00].Enabled = False
 
End If
 
 
End If
 
 rstCheck.MoveNext
Loop
 
rstCheck.Close
 
 

[puppydogbuddy]

Hi Phil,
you can simplif things considerably by setting it up as a control array. see this thread starting with post on 10/26/2008 that has a download file.

http://bytes.com/topic/access/answer...ing-grid-dates

[ChipR]

If this is code in the Form_Load or something, you can drop all the [Forms]![FrmCalendarMain]!.
Lines 106-137 do not seem to be necessary.
Try using a loop something like this:

Dim strControlName As String
For i = 8 to 23
  For j = 0 to 1
    If i < 10 Then
      strControlName = "txtMon0" & i & ":"
    Else
      strControlName = "txtMon" & i & ":"
    End If
    If j = 0 Then 
      strControlName = strControlName & "00"
    Else
      strControlName = strControlName & "30"
    End If
    If Me.Controls(strControlName) >= BookedStartTime And Me.Controls(strControlName) < BookedEndTime
      Me.Controls(strControlName).BackColor = 12566463
      Me.Controls(strControlName).ForeColor = 12566463
      Me.Controls(strControlName).Enabled = False
  Next
Next

But don't forget to do the midnight case, since it's not covered in the loop.

[puppydogbuddy]

Hi Phil,

In case you or anyone else is interested, here is the entire code for the daily booking form used in the application. The code is triggered on the click of a calendar control button that is used to select a booking date that denotes the beginning of the 14 day booking period for which the bookings are displayed.

Private Sub Calendar4_Click()
    Dim Db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSql As String
    Dim ctl As Control
    Dim zDateCtlName As String
    Dim zCtl As Control
    Dim zCtlName As Variant
    Dim j As Integer
    Dim k As Integer
 
' Copy chosen date from calendar to originating combo box
   StartDate.Value = Calendar4.Value
' Return the focus to the combo box and hide the calendar
   StartDate.SetFocus
   Calendar4.Visible = False
 
'---------------------------------------------------------------------------------------
 
'set up calendar date array, Unbound textboxes, top row of grid, with code =DateAdd("d",0-7,[Startdate])
     For k = 1 To 8
       Me("Date" & (k)) = DateAdd("d", k, [StartDate])
     Next k
 
'dynamically assign the arrival dates and departure dates to the appropriate control on the grid based the data returned by the record source.
'-----------------------------------------------------------------------------------------------
 
'fetch arrival and departure data falling in 8 day block on the calendar.
strSql = "Select RoomNumber,BookingGridIndex ,ArrivalDate, DepartureDate,"
strSql = strSql & " DateDiff('d',ArrivalDate,DepartureDate) As numDays"
strSql = strSql & " From qryDailyBookings"
strSql = strSql & " WHERE ([RoomNumber] Between '1' And '9')"
strSql = strSql & " AND ([ArrivalDate] Between " & "#" & [Forms]![frmDailyBookings]![Date1] & "#" & " And " & "#" & [Forms]![frmDailyBookings]![Date8] & "#)"
strSql = strSql & " OR ([RoomNumber] Between '1' And '9') AND ([DepartureDate] Between "
strSql = strSql & "#" & [Forms]![frmDailyBookings]![Date1] & "#" & " And #" & [Forms]![frmDailyBookings]![Date8] & "#)"
strSql = strSql & " Order By RoomNumber, ArrivalDate, DepartureDate;"
                    '  Debug.Print strSql
 
Set Db = CurrentDb()
Set rs = Db.OpenRecordset(strSql)
 
 
'ok, now that the rooms and calendar dates have
'been populated on the grid, let's populate the
'grid with the arrival and departures for the period.
  '_________________________________________
 
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF
     'make a loop around the grid to pick up and identify all arrivals, where the calendar
       ' date is within the arrival and/or departure dates for each room.
  On Error Resume Next
  For Each ctl In Me.Controls
    If InStr(1, ctl.Name, "Date") <> 0 Then
        zDateCtlName = ctl.Name
        zCtlName = CStr("Day" & rs!RoomNumber & Right(zDateCtlName, 1))
        Set zCtl = Controls(CStr(zCtlName))
 
        If ctl.Value = rs!ArrivalDate Then
               zCtl.Value = "Arr " & Format(TimeValue(rs!ArrivalDate), "hh:mm AMPM")
               zCtl.BackColor = "16777088"
        ElseIf ctl.Value = rs!DepartureDate Then
               zCtl.Value = "Dpt " & Format(TimeValue(rs!DepartureDate), "hh:mm AMPM")
               zCtl.BackColor = vbYellow
        ElseIf ctl.Value > rs!ArrivalDate And ctl.Value < rs!DepartureDate Then
               zCtl.Value = "Occupied"
               zCtl.BackColor = "16777088"
        End If
 
   End If
 Next ctl
 On Error GoTo 0
 
 rs.MoveNext
 
Loop
 
 
  'make a final loop around the grid to pick up and identify all vacancies, where the calendar
    ' date is prior to the arrival and where the calendar date is after the departure date
    On Error Resume Next
       For Each ctl In Me.Controls
           If InStr(1, ctl.Name, "Day") <> 0 Then
              If IsNull(ctl.Value) Then
                 ctl.Value = "Vacant"
              End If
           End If
       Next
    On Error GoTo 0
 
Else
    Exit Sub
End If
 
rs.Close
Set rs = Nothing
Set Db = Nothing
 
End Sub
 
Private Sub Form_Current()
'(This works)
On Error Resume Next
  For Each c In Me.Controls
   If InStr(1, c.Name, "Day") <> 0 Then
   If c.Value = DepartureDate.Value Then
      c.ForeColor = vbRed
   Else
     c.ForeColor = vbBlack
 
End If
End If
 
Next
 
On Error GoTo 0
End Sub
 
 

[phill86]

Thats a great help thanks guys