show booking times graphically 2

[BradCollins]

Some time ago with the help of people here I manage to create a booking system for my work to be able to book rooms. This has been in use very successfully for over a year now and everyone is happy.
Now however they want to be able to book the rooms in smaller time amounts.
Currently things are booked by the day, now they want to be able to book them by the half hour, my question is how do I now show this graphically on the booking calendar I created.

I have added fields to record a start time and and end time, but of course the calendar works on the entire day.

The calendar is currently laid out with the rooms listed down the side (Y axis) and the days across the top (X axis) this is populated from a query

Now I want the same format, but have the time periods (30 minutes) across the top instead of the days.

The query that fills the boxes for each room at the moment is

day1Booking: isBooked([VenueID],forms!frmBookingsCalendar!txtDate1)

this is backed by a module

Public Function isBooked(intRoomID As Integer, dtmDay As Date) As String
  On Error GoTo errLbl
  
  Dim strWhere As String
  
  strWhere = " fldBookAccom = " & intRoomID & " AND fldBookIn<=" & sqlDate(dtmDay) & " AND ([fldBookOut]-1)>=" & sqlDate(dtmDay)
  
  Debug.Print strWhere
  If Not IsNull(DLookup("fldBookAccom", "tblBookings", strWhere)) Then
    'isBooked = "Booked"
   Else
    isBooked = "-"
  End If
  
  Exit Function

errLbl:
  MsgBox Err.Number & Err.Description

End Function

any hints would be most appreciated as I have spent over a week trying to figure this one out. Thanks

 

[dhookom]

If you are looking for a report, consider the sample Calendar Reports sample.

I have used similar code in a form to make multiple text boxes visible and positioning them on a form.

Duane
Hook'D on Access
MS Access MVP

 

[BradCollins]

Thanks but I am looking to display it on a form,so if I can get the query to work right, i can use that as the bassis of the form.

 

[dhookom]

It isn't clear what your exact table structure is or how you are displaying the records on your form. Do you have a grid of controls set up on your form?

There are 48 30 minute time periods in a day. How would you want these displayed?

Duane
Hook'D on Access
MS Access MVP

 

[BradCollins]

my appologies, the form is set up (but I am open to changing it if required) with the time fields on the Y-axis and the rooms on the X-Axis, this way you have as shown below

8-830 830-9 9-930 930-10 10-1030
Room1 Booked Booked
Room2 Booked Booked Booked
Room3 Booked Booked Booked

 

[BradCollins]

After a nearly 2 weeks trying to figure this out, I think I am almost there.
I have the form setup with a record source of tblRooms and set to be a continuous form. This gives me all the room details. I then created 26 unbound fields (1 each to represent a 30 minute time period) eg: 6-6:30 , 6:30-7 etc

now I just need to populate these fields with the booking number, now what I have at the moment is

=DLookUp("[fldBookingID]","[tblBookings]","[StartTime]Between #8:00:00 AM# And #8:29:59 AM# And [fldBookAccom]=[VenueID] and [fldBookIn] = [txtDate1]")

this works fine in giving me the correct booking id number for the start time, but what I need is to populate each unbound field with the booking ID for the entire length of the booking.
So if I have a room booked from 0800 to 1030 then I will need that booking ID in the boxes covering those 30 minute periods.

As always your help is appreciated. Thanks

 

[dhookom]

Your form data is clearly not updateable. That suggests you could easily use a report as I first suggested. If you really want to use a form, I would build a table of time slots and then combine this table with your schedule table in a crosstab query with the time slots as Column Headings, Rooms as the Row Headings and bookingID as the value.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I have a feeling I showed you this already, but it does everything that you are talking about.

http://www.4shared.com/file/62047092/5791dec7/RoomBooking_Ver_30.html

Basically this is my trick. The data is inputed in a normalized format

RoomID
Start Time
End Time

Now I build a non normalized table for display purposes.
tbl_Times_Room

It has the following fields:
A label for each Time period: 7:00 - 7:30
A Start Time: 07:00 AM
A End Time: 07:29 AM
Fields For Each Room:

I prepopulate the period label, start Time, and End Time.

Now I build a form off of this table. There will be a record for each period and field for each room The form is continous, bound, and tabular. Basically a grid.

Prior to opening the form I read from my normalized table, and stick in the information for each room and period. Like this:

Public Sub loadData()
  Dim rsReservations As DAO.Recordset
  Dim rsGrid As DAO.Recordset
  Dim strWhere As String
  Dim intNumberPeriods As Integer
  Dim intRoomID As Integer
  Dim dtmPeriodStart As Date
  Dim dtmPeriodEnd As Date
  Dim strFieldName As String
  Dim duration As Long
  Dim intervals As Integer
  Dim intervalCount As Integer
  Set rsReservations = CurrentDb.OpenRecordset("qrySelectedDateReservations", dbOpenDynaset)
  Set rsGrid = CurrentDb.OpenRecordset("tblTimes_Rooms", dbOpenDynaset)
  DoCmd.SetWarnings (False)
  Call ClearReservations
  DoCmd.SetWarnings (True)
  Do While Not rsReservations.EOF
    intRoomID = rsReservations.Fields("intRoomID")
    dtmPeriodStart = rsReservations.Fields("dtmStartPeriod")
    dtmPeriodEnd = rsReservations.Fields("dtmEndPeriod")
    strFieldName = rsReservations.Fields("strAlias")
    strWhere = "TimeValue([dtmRefTimePeriodStart]) = '" & TimeValue(dtmPeriodStart) & "'"
    'Debug.Print intRoomID & dtmPeriodStart & dtmPeriodEnd & strFieldName
    rsGrid.FindFirst (strWhere)
    duration = DateDiff("n", dtmPeriodStart, dtmPeriodEnd)
    intervals = Int(duration / 29)
    rsGrid.Edit
      rsGrid.Fields(strFieldName) = rsReservations.Fields("Details")
    rsGrid.Update
    If intervals > 1 Then
      For intervalCount = 2 To intervals
       rsGrid.MoveNext
         rsGrid.Edit
         rsGrid.Fields(strFieldName) = rsReservations.Fields("Details")
         rsGrid.Update
     Next intervalCount
    End If
    rsReservations.MoveNext
  Loop
End Sub

This design provides a lot of flexibility. I can easily add more rooms without modifying any codes. I only need 1 field per room, a start field, end field, and period label field. I unbound form is (# rooms x number of intervals). You can put any kind of detail in the display field without changing any code, just the query.

In your method I will have to know the naming convention for each of your unbound fields, and know your data table structure. But basically I will get a record for a room. Determine the start time. Determine how many intervals to populate and then populate the records. Assume your ctrl names are named something like

rm1per1, rm1per2, rm1per3,...... rm1perN
rm2per1, rm2per2, rm2per3...... rm2perN
...
rmNper1........................ rmNperN

read your first record
determine which room and the start period
determine end period
determine # period
pseudo code
dim RoomCtrlName as string
dim intperiod as integer
get roomName
get periodStart
get periodEnd
#intervals = periodEnd-periodStart/interval time
select Case roomName
case "Library"
roomCtrlName = "rm1"
case ...
end select

Select case period
case 07:00 AM
intPeriod = 1
case xx:xx XM
end select

frm = forms("displayForm")
frm.controls(roomCtrlName & "per" & intPeriod) = some display information

for intCount = 1 to #intervals
frm.controls(roomCtrlName & "intPeriod + intCount) = some display information
next intCount

get next reservation information

 

[BradCollins]

MajP,

Once again thank you

You did indeed show me this before, but with your help we had modified it so that it you could book a room per day with no time internals. Now of course a bit over a year later they want the time intervals and I had lost your original sample.

however the link provided doesnt work for me, but I think I now get the idea of how to get this working...Thank you

 

[MajP]

Retry the link. I tested it from the hyperlink and it seems to work fine for me. Good luck.

 

[dhookom]

You don't need any code to display the 1/2 hour time slots. Create a table [tblSlots] with a field [SlotStart] and records. I created the values in Excel and pasted them into a new table.
[tt][blue]
SlotStart
=========
6:00 AM
6:30 AM
7:00 AM
7:30 AM
-- etc--
7:00 PM
[/blue][/tt]
Then create a query [qselSlots] to find the end times:

SELECT SlotStart, DateAdd("n",30,[SlotStart]) AS SlotEnd
FROM tblSlots;

Then create a crosstab query [qxtbSlots] with SQL like:

TRANSFORM First(SchedID) AS TheValue
SELECT SchedDate, Room
FROM tblBookings, qselSlots
WHERE (((qselSlots.SlotStart)<[EndTime]) AND ((qselSlots.SlotEnd)>[StartTime]))
GROUP BY SchedDate, Room
PIVOT Format([SlotStart],"Short Time");

Then add a subform control to a form and set its Source Object property to Query.qxtbSlots
[tt][blue]
Source Object: Query.qxtbSlots
[/blue][/tt]
The result in your form should look something like:
[tt][blue]
SchedDate Room 08:00 08:30 09:00 09:30 10:00 10:30 11:00 11:30 13:00 13:30
12/4/2000 RoomA 153 154 155 155 156 157 157 162
12/4/2000 RoomB 168 169 169 170 170 171 171 176 176
12/4/2000 RoomC 139 139 140 140 141 141 142 146
[/blue][/tt]

Did I mention you don't need any code? You can add more time slots if you want or even change them to 1/4 hours or 2 hours slots. The only other thing you might need to do is enter the short time values to the Column Headings property of the crosstab.

Duane
Hook'D on Access
MS Access MVP

 

[BradCollins]

A star for you Duane, thank you very much

your way is so easy to do and very easy for a relative amateur like me to understand. Thank you very much.

 

[MajP]

I am trying to remember why I did not originally use a crosstab query. I believe that I needed the fields to be editable, and display different values within the fields. So what I did was code an "editable crosstab query." But if you do not need to edit the results then this is a much better approach. Also this gives the same flexibility of adding rooms and changing periods.

Either way these are both far better ideas then an unbound form.