Solved Room availability

[ebs17]

Anyone can help?

I have some unanswered questions in my description that have not been answered.
Are you checking for a specific room or all rooms to accommodate someone during that period? For me, that's a significant difference, and since I like to do everything right the first time, I have to be clear about the right task.

Since you brought maximum room capacities into play, these should be documented in a table so that they can be used. I asked for a database schema that contains the necessary information. I usually don't paint something like that myself because my model certainly has nothing to do with your reality and is therefore quickly something for just the trash.
Your time is important to you, my time is important to me. The latter also means that in some cases I can be bought.

 

[alcwho]

Are you checking for a specific room or all rooms to accommodate someone during that period?

Sorry about that. Someone will book a specific room for a specific time slot, so in this case i think its for specific room.

my time is important to me. The latter also means that in some cases I can be bought.

Very much appreciated for your help

 

[alcwho]

post #19 db does not give a correct msg when the RoomID is plugged in.
the attached will now work.

Millions thanks , let me try.

 

[alcwho]

note it would be easier to demonstrate if you upload a sample db.

Hi Arnelgp,

Here is the sample file I created. You can find the OrderF there. After open the OrderF, user type the Start date, End date, and Room, then it will run the VBA to check.

you will also see my RoomTypeT (table) that contains field called "Maxcapacity" to show the max cap for each room to book at the same time for a time slot (can different person to book).

 

[arnelgp]

extract and test this db.

 

[ebs17]

I think in a database most of the logic should be executed using SQL because SQL is the native language in the database.
The following query returns the open capacity for a given room and time period. By the way, this is no different than what I wrote in #3 and #13.
qryOpenCapacity

PARAMETERS
   parRoomID Long,
   parBookingStart DateTime,
   parBookingEnd DateTime
;
SELECT
   MC.MaxCapacity - O.Occupancy AS OpenCapacity,
   MC.MaxCapacity,
   O.Occupancy
FROM
   (
      SELECT
         MaxCapacity
      FROM
         RoomTypeT
      WHERE
         RoomID = parRoomID
   ) AS MC,
   (
      SELECT
         COUNT(*) AS Occupancy
      FROM
         OrderT
      WHERE
         StartDateTime <= parBookingEnd
            AND
         EndDateTime >= parBookingStart
            AND
         RoomID = parRoomID
   ) AS O

For the necessary ease of use, the query is called and evaluated via VBA:

Public Function GetOpenCapacity(ByVal AnyRoomID As Long, ByVal AnyStart As Date, ByVal AnyEnd As Date) As Long
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set qd = db.QueryDefs("qryOpenCapacity")
    qd.Parameters("parRoomID") = AnyRoomID
    qd.Parameters("parBookingStart") = AnyStart
    qd.Parameters("parBookingEnd") = AnyEnd
    Set rs = qd.OpenRecordset(dbOpenForwardOnly)
    qd.Close
    GetOpenCapacity = rs.Fields("OpenCapacity")
    rs.Close
End Function

Sub call_GetOpenCapacity()
    If GetOpenCapacity(3, #9/29/2023 11:00:00 AM#, #9/29/2023 11:25:00 AM#) > 0 Then
        Debug.Print "yeh"
    Else
        Debug.Print "oh no"
    End If
End Sub

Only the last procedure touches one of the colorful forms directly => parameter adoption.

 

[alcwho]

extract and test this db.

Very much appreciated for your help. Let me take a look.

 

[alcwho]

I think in a database most of the logic should be executed using SQL because SQL is the native language in the database.
The following query returns the open capacity for a given room and time period. By the way, this is no different than what I wrote in #3 and #13.
qryOpenCapacity

PARAMETERS
   parRoomID Long,
   parBookingStart DateTime,
   parBookingEnd DateTime
;
SELECT
   MC.MaxCapacity - O.Occupancy AS OpenCapacity,
   MC.MaxCapacity,
   O.Occupancy
FROM
   (
      SELECT
         MaxCapacity
      FROM
         RoomTypeT
      WHERE
         RoomID = parRoomID
   ) AS MC,
   (
      SELECT
         COUNT(*) AS Occupancy
      FROM
         OrderT
      WHERE
         StartDateTime <= parBookingEnd
            AND
         EndDateTime >= parBookingStart
            AND
         RoomID = parRoomID
   ) AS O

For the necessary ease of use, the query is called and evaluated via VBA:

Public Function GetOpenCapacity(ByVal AnyRoomID As Long, ByVal AnyStart As Date, ByVal AnyEnd As Date) As Long
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set qd = db.QueryDefs("qryOpenCapacity")
    qd.Parameters("parRoomID") = AnyRoomID
    qd.Parameters("parBookingStart") = AnyStart
    qd.Parameters("parBookingEnd") = AnyEnd
    Set rs = qd.OpenRecordset(dbOpenForwardOnly)
    qd.Close
    GetOpenCapacity = rs.Fields("OpenCapacity")
    rs.Close
End Function

Sub call_GetOpenCapacity()
    If GetOpenCapacity(3, #9/29/2023 11:00:00 AM#, #9/29/2023 11:25:00 AM#) > 0 Then
        Debug.Print "yeh"
    Else
        Debug.Print "oh no"
    End If
End Sub

Only the last procedure touches one of the colorful forms directly => parameter adoption.

Millions thanks for your help on this. I'll try to learn SQL as you suggested. Again, much much appreciated for your help.

 

[alcwho]

extract and test this db.

Again, thanks a lot. I got a very quick question.

Got one situation. RoomID 3 got max cap for 4 ppl, and the first reservation starts from 10am to 12pm. Then make 3 more records to test, it looks fine.

However, if I make a record for 11am to 12pm (OrderID 33) or 11am to 1pm (OrderID 35), it can let the booking go through (but already hit the max cap for the period 10am to 12pm for 4 ppl). Thus, I think it's a spanning booking. I tried to write the following code (see below) but don't know how to incorporate it into your code. Could you please shed me light on it?

---------------------------
If ID = 0 Then
' check for spanning appointments
ID = Nz(DLookup("OrderID", "OrderT", _
"OrderID <>" & OrderID & " AND " & _
"StartDateTime>#" & SD & "# AND " & _
"EndDateTime < #" & ED & "# AND " & _
"RoomID = " & RMID), 0)
End If
------------------------------

Sorry, please bear with me for not clarifying at the very beginning.

Again and again, very much appreciated for your help and all you guys help

 

[arnelgp]

i make a copy of OrderT and delete all records from orderT.
you try to make records for Room C (3).