Solved Room availability (1 Viewer)

[alcwho]

Hi all,

I am quite new to VBA and i wrote the following function. I try to double check if there is any double booking for a time slot. And now i need to add if the room is available, but i am still stuck how to add the room availability logic. Could someone please help? Thanks in advance.

Private Function IsConflicts(SD As Date, ED As Date) As Boolean

Dim ID As Long

'check start date and room availability
ID = Nz(DLookup("OrderID", "OrderT", _
"OrderID <>" & OrderID & " AND " & _
"StartDateTime<=#" & SD & "# AND " & _
"EndDateTime > #" & SD & "# AND " & _
"RoomID = " & RoomID), 0) ' <-- i tried to add this here, but couldn't get through if i change the room type with the same time slot.

If ID = 0 Then
' check end date
ID = Nz(DLookup("OrderID", "OrderT", _
"OrderID <>" & OrderID & " AND " & _
"StartDateTime<#" & ED & "# AND " & _
"EndDateTime >= #" & ED & "#"), 0)
End If

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

If ID <> 0 Then
IsConflicts = True
Else
IsConflicts = False
End If


End Function

 

[theDBguy]

Hi. Welcome to AWF!

Did you also add RoomID as a parameter to the function?

 

[ebs17]

Overlapping records

PARAMETERS
   parRoomID Long,
   parBookingStart DateTime,
   parBookingEnd DateTime
;
SELECT
   RoomID,
   StartDateTime,
   EndDateTime,
   parBookingStart AS BookingStart,
   parBookingEnd AS BookingEnd
FROM
   OrderT
WHERE
   StartDateTime <= parBookingEnd
      AND
   EndDateTime >= parBookingStart
      AND
   RoomID = parRoomID
ORDER BY
   StartDateTime

 

[alcwho]

Hi. Welcome to AWF!

Did you also add RoomID as a parameter to the function?

You are right. Let me try.
Thanks for your help.

 

[alcwho]

Overlapping records

PARAMETERS
   parRoomID Long,
   parBookingStart DateTime,
   parBookingEnd DateTime
;
SELECT
   RoomID,
   StartDateTime,
   EndDateTime,
   parBookingStart AS BookingStart,
   parBookingEnd AS BookingEnd
FROM
   OrderT
WHERE
   StartDateTime <= parBookingEnd
      AND
   EndDateTime >= parBookingStart
      AND
   RoomID = parRoomID
ORDER BY
   StartDateTime

Thanks , let me take a look, i haven't learnt SQL yet, but will definitely be the next step for it.

 

[jdraw]

There is an older sample/demo here that may help

 

[alcwho]

Hi. Welcome to AWF!

Did you also add RoomID as a parameter to the function?

Hi Sir,

as you said, after adding the parameter, it works flawlessly. And also I tried to add the SQL code from the above, seems much easier to understand, but let me learn the VBA basic first.

Thanks all.

 

[alcwho]

Situation comes a bit complicated now. I have 5 types of room which has its own max capacity. One is 10, another one is 4, others are 1.

i tried to add the max capacity check coding to the end, but seems the logic not right.

E.g. For room A which max cap is 4. I filled 3 records with the same time slot which works fine, but when i tried to add one more, it said it reach max cap. Actually, there is one record with the same room A but in different time slot. Thus, it counts it as well.

Could someone please help to see how I can modify it to fit the purpose?

Again, very much appreciated for you guys help.

-------------------------

Private Function IsConflicts(SD As Date, ED As Date, RMID As Integer) As Boolean

Dim ID As Long
Dim maxCapacity As Integer
Dim currentCapacity As Integer

'check start date and room availability
ID = Nz(DLookup("OrderID", "OrderT", _
"OrderID <>" & OrderID & " AND " & _
"StartDateTime<=#" & SD & "# AND " & _
"EndDateTime > #" & SD & "# AND " & _
"RoomID = " & RMID), 0)

If ID = 0 Then
' check end date
ID = Nz(DLookup("OrderID", "OrderT", _
"OrderID <>" & OrderID & " AND " & _
"StartDateTime<#" & ED & "# AND " & _
"EndDateTime >= #" & ED & "# AND " & _
"RoomID = " & RMID), 0)
End If

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

' Check if room reaches its capacity
maxCapacity = Nz(DLookup("MaxCapacity", "RoomTypeT", "RoomID = " & RMID), 0)

currentCapacity = Nz(DCount("OrderID", "OrderT", _
"RoomID = " & RMID & " AND " & _
"StartDateTime<=#" & SD & "# AND " & _
"EndDateTime > #" & SD & "#"), 0)

currentCapacity = currentCapacity + Nz(DCount("OrderID", "OrderT", _
"RoomID = " & RMID & " AND " & _
"StartDateTime<#" & ED & "# AND " & _
"EndDateTime >= #" & ED & "#"), 0)

currentCapacity = currentCapacity + Nz(DCount("OrderID", "OrderT", _
"RoomID = " & RMID & " AND " & _
"StartDateTime>#" & SD & "# AND " & _
"EndDateTime < #" & ED & "#"), 0)

If currentCapacity > maxCapacity Then
IsConflicts = True
Else
IsConflicts = False
End If

End Function

-------------------

 

[alcwho]

Hi. Welcome to AWF!

Did you also add RoomID as a parameter to the function?

i am bit frustrated now. I added the RoomID parament (pls see code below). And it's ok when i try to enter some records with today or before today, it pops up the double booking message. However, when i try to enter a new entry with future date with the same time slot, it can get it pass. For example,

3rd record:
Start : Oct 1, 2023 9am
End: Oct 1, 2023 10am
Room A

when i try to add a new record, with the same data in 3rd record, it can let it pass the double checking logic. it won't happen in the old date record. Eg. 1st record is Sep 28, 2023 12pm Room B. If I add a new record with the same data in 1st record, it won't let me pass and pops up the wrong message.

Could you please help? I don't know where the problem is.

Again, thanks a lot.

--------------------

Private Function IsConflicts(SD As Date, ED As Date, RMID As Integer) As Boolean

Dim ID As Long

'check start date and room availability
ID = Nz(DLookup("OrderID", "OrderT", _
"OrderID <>" & OrderID & " AND " & _
"StartDateTime<=#" & SD & "# AND " & _
"EndDateTime > #" & SD & "# AND " & _
"RoomID = " & RMID), 0)

If ID = 0 Then
' check end date
ID = Nz(DLookup("OrderID", "OrderT", _
"OrderID <>" & OrderID & " AND " & _
"StartDateTime<#" & ED & "# AND " & _
"EndDateTime >= #" & ED & "# AND " & _
"RoomID = " & RMID), 0)
End If

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

If ID <> 0 Then
IsConflicts = True
Else
IsConflicts = False
End If



End Function
------------------------------

 

[CJ_London]

Not clear what you are saying. Are you saying you have a room which can be booked by 4 (i or 10 depending on the room) different people for the same or overlapping time periods?

perhaps it is time to clarify exactly what you are trying to do - what are the rooms? Who are the people.

it sounds to me like perhaps you are providing work spaces/desks in different capacity rooms, so you should be looking at space/desk, not a room

 

[alcwho]

Not clear what you are saying. Are you saying you have a room which can be booked by 4 (i or 10 depending on the room) different people for the same or overlapping time periods?

perhaps it is time to clarify exactly what you are trying to do - what are the rooms? Who are the people.

it sounds to me like perhaps you are providing work spaces/desks in different capacity rooms, so you should be looking at space/desk, not a room

Sorry, it's not very clear in the very beginning.

Here is the situation.

I have 5 rooms for booking, each room has seats capacity as follow,
Room max capacity:
- Room A: 10 seats (means has 10 different booking with same time slot)

- Room B: 4
- Room C to E: only 1

In addition, if I mark a room for cleaning for a certain period, then no booking is allowed regardless the capacity.

That's the situation.

I try to write the VBA step by step now, but bumped with those problems.

Again, very much appreciated for your help

 

[arnelgp]

another simpler solution for Overlapping dates
Blog of the Baodad Tree: Date Range Overlap

 

[ebs17]

Side thought: VBA versus SQL
A DCount is an SQL query wrapped in an Access function.
I certainly don't need to justify the extreme similarity.

DCount("*", "NameTable")
SELECT COUNT(*) FROM NameTable

So you would play the same mental games. A correct (= complete) query is then certainly more useful than a sequence of individual queries that are not properly coordinated with each other.

I have 5 types of rooms for booking

Exactly one room per room type?
The database schema is used for correct considerations. You should show this.

It could be a useful logic to count the overlaps or bookings for the desired time window, based on a specific room or for all rooms. If you subtract existing numbers from the maximum capacity, you get the open capacity.

 

[alcwho]

Exactly one room per room type?

Sorry, actually its only 5 rooms from Room A to E. I amended the post.

Thanks

 

[alcwho]

Anyone can help?

 

[arnelgp]

where are you putting your code? create a form and add your code on the BeforeUpdate event of your form.

 

[alcwho]

where are you putting your code? create a form and add your code on the BeforeUpdate event of your form.

Yes correct, after a user choose the start date, end date and room, then VBA code will check.

Thanks again.

 

[arnelgp]

here is a sample db to look at.
it might not be the same as you have but the principle applies.
open OrderForm and enter same RoomID with overlapping date as on the first record.
look at the BeforeUpdate event of the form.

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

 

[arnelgp]

sorry forgot the RoomID as criteria also.

 

[arnelgp]

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