Double Bookings and Scheduling Conflicts

BDW Jr

Registered User.
Local time
Yesterday, 17:27
Joined
May 10, 2009
Messages
18
I’m new to Access and this forum. I just started using Access about a year ago and really enjoy working in it. But I’m not whiz kid at it and any help is much appreciated.


My friend took over my old job and asked me to help him with room scheduling. Previously they were using Excel for room scheduling but that takes up too much of his time. I’ve put together a database but here are the two issues I come across:


1. How do I prevent a double booking? (Besides the obvious of not entering the same info :))


I have a form which allows the user to book rooms. On this form are the following fields:

Count: (Autonumber)
ProgramCode: Text box
DivisionCode: Text Box (drop down box)
Event: Text Box (drop down box)
Room: Text Box (drop down box)
StartDate: Date/Time
EndDate: Date/Time
StartTime: Date/Time
EndTime: Date/Time
NumofSessions: Number (this is a calculated field - for show only)
Semester: Text Box (drop down box)
Year: Text Box (drop down box)

The form adds this information to the Schedule table.

2. How do I have the form check to see if events overlap? If it does overlap, I want the proposed booking to be rejected with a customized message.


Thanks
 
First, I would store the date and time pairs in a single field. It will make your life a lot easier.


Now to learn about VBA coding. See:
Clashing Events/Appointments
 
Last edited:
HiTechCoah,

Thanks for some direction on these issues. But how do I incorporate it into the form so duplicates and overlaps are rejected?
 
Did you get a chance to look at the link I provided?
 
Last edited:
Yes, I created the query and it gives me the overlapping entries - that's not a problem. But how do I put it into the form? If I make this query the record source, all I get are the overlapping entries from the Schedule table. I'm looking to make the form reject overlapping events.
 
Looks like Boyd is offline. I would probably use a recordset or DCount to test that query before allowing the record to be saved. If it returns records, you have a conflict. You could put that test behind a save button, in the before update event, or wherever fits your situation.
 
Paul, Thanks for jumping in here.

I agree with Paul's suggestions.
 
Hey guys, thanks for all your help/suggestions. I've never used recordsets or dlookup. Perhaps you can help me further with this. This is the expression I used in the query to identify clashes:

NoClash: ([tSchedule_1].[StartDate]>=[tSchedule].[EndDate]) Or ([tSchedule_1].[EndDate]<=[tSchedule].[StartDate]) Or ([tSchedule_1].[StartTime]>=[tSchedule].[EndTime]) Or ([tSchedule_1].[EndTime]<=[tSchedule].[StartTime]) Or ([tSchedule].[Room]<>[tSchedule_1].[Room]) Or ([tSchedule].[Count]=[tSchedule_1].[Count])

Just a reminder: I'm looking to put this in the form and have it reject it if it is an overlap. I used in index option on the table to prevent duplicate bookings. Again I really appreciate your help with this database.
 

Users who are viewing this thread

Back
Top Bottom