Calendar bookings represented on a room booking form

**puppydogbuddy** · Jul 27 '09, 12:38 PM

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/answers/834323-populating-grid-dates

**ChipR** · Jul 27 '09, 12:39 PM

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:

Code:

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** · Jul 27 '09, 01:25 PM

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.

Code:

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** · Jul 27 '09, 04:43 PM

Thats a great help thanks guys

Calendar bookings represented on a room booking form

Calendar bookings represented on a room booking form

Comment

Comment

Comment

Comment