Finding A Scheduling Conflict

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

I have an Access 97 db that handles scheduling for
conference rooms. A requirement is that if a conflicting
time is entered for a room, the user is notified.

Didn't think this would be such a headache. I've tried:

Set db = DBEngine(0)(0)
Set rst = db.OpenRecordset(strRecSource)
If rst.EOF And rst.BOF = True Then
Exit Sub
ElseIf rst.EOF And rst.BOF = False Then
DoCmd.OpenForm "frmDetectConflict"
End If

Because the query has criteria in it for room code, date
and times, I get an error message: "Too few parameters.
Expected 4".

Can anyone suggest a different approach? Thanks in
advance for your help.
 
Try entering the SQL into the OpenRecordset statement;

Set rst = db.openrecordset("SELECT * FROM tbl")

Hope this helps,
Steve.
 
Thanks Steve - the SQL is very long, can you tell me how
to enter it? I've never been able to break up long lines
of code, probably simple but I have a block....

Here it is:

SELECT [VIDEOCONFERENCE ROOM SCHEDULE].DATE,
tblRoom.Room_Name, [VIDEOCONFERENCE ROOM
SCHEDULE].Room_Code, [VIDEOCONFERENCE ROOM SCHEDULE].
[MEETING START TIME], [VIDEOCONFERENCE ROOM SCHEDULE].
[MEETING END TIME], [VIDEOCONFERENCE ROOM
SCHEDULE].BUREAU, [VIDEOCONFERENCE ROOM SCHEDULE].
[SCHEDULED BY], [VIDEOCONFERENCE ROOM
SCHEDULE].Date_scheduled, [VIDEOCONFERENCE ROOM
SCHEDULE].DESCRIPTION
FROM [VIDEOCONFERENCE ROOM SCHEDULE] INNER JOIN tblRoom ON
[VIDEOCONFERENCE ROOM SCHEDULE].Room_Code =
tblRoom.Room_Code
WHERE ((([VIDEOCONFERENCE ROOM SCHEDULE].DATE)=[forms]!
[frmScheduling]![date]) AND ((tblRoom.Room_Name)=[forms]!
[frmScheduling]![cmbRoom]) AND (([VIDEOCONFERENCE ROOM
SCHEDULE].[MEETING START TIME]) Between [forms]!
[frmScheduling]![meeting start time] And [forms]!
[frmScheduling]![meeting end time])) OR
((([VIDEOCONFERENCE ROOM SCHEDULE].DATE)=[forms]!
[frmScheduling]![date]) AND ((tblRoom.Room_Name)=[forms]!
[frmScheduling]![cmbRoom]) AND (([VIDEOCONFERENCE ROOM
SCHEDULE].[MEETING END TIME]) Between [forms]!
[frmScheduling]![meeting start time] And [forms]!
[frmScheduling]![meeting end time]));
 
Depends on your data structure.

If your table has fields:
BookingID AutoNumber primary key
RoomID Text unique identifier for the room booked
BookDateTime Date/Time date and time booking starts
then you would normally use the BeforeUpdate event procedure of the form
where the booking is entered to look for other conflicts. DLookup() could
to that. For basic help with DLookup() see:
http://allenbrowne.com/casu-07.html

Example:

----------code starts--------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String

If IsNull(Me.RoomID) Or IsNull(Me.BookDateTime) Then
Cancel = True
MsgBox "Both room and date/time required."
Else
If (Me.RoomID = Me.RoomID.OldValue) And (Me.BookDateTime =
Me.BookDateTime.OldValue) Then
'do nothing
Else
strWhere = "(RoomID = """ & Me.RoomID & """) AND (BookDateTime =
" & Format(Me.BookDateTime, "\#mm\/dd\/yyyy hh:nn:ss\#") & ")"
varResult = DLookup("BookingID", "tblBooking", strWhere)
If Not IsNull(varResult) then
strMsg = "Booking " & varResult & "is for the same room,
date, and time." & vbCrLf & "Continue anyway?"
If MsgBox (strMsg, vbYesNo + vbDefaultButton2) <> vbYes Then
Cancel = True
End If
End If
End If
End If
End Sub
----------code ends--------------------

If your bookings have both a start time, and an end time, and you need to
find the matches, a clash occurs if:
- booking A starts before booking B ends, AND
- booking B starts before booking A ends, AND
- it's the same room.

For ideas on how to find all the clashes in a table, even where the ending
date may be unknown, see:
http://allenbrowne.com/appevent.html
 
The best way to do it would be to create a String;

Bearing in Mind SQL uses 'American' format dates
(mm/dd/yy), the format command may be needed (if in UK) to
convert them. e.g. The First part of the Where statment
would be;
"WHERE ((([VIDEOCONFERENCE ROOM SCHEDULE].DATE)=#" & format
([forms]!
[frmScheduling]![date],"mm/dd/yy") & "#)

strSQL = "SELECT [VIDEOCONFERENCE ROOM SCHEDULE].DATE,
tblRoom.Room_Name, [VIDEOCONFERENCE ROOM
SCHEDULE].Room_Code, [VIDEOCONFERENCE ROOM SCHEDULE].
[MEETING START TIME], [VIDEOCONFERENCE ROOM SCHEDULE].
[MEETING END TIME], [VIDEOCONFERENCE ROOM
SCHEDULE].BUREAU, [VIDEOCONFERENCE ROOM SCHEDULE].
[SCHEDULED BY], [VIDEOCONFERENCE ROOM
SCHEDULE].Date_scheduled, [VIDEOCONFERENCE ROOM
SCHEDULE].DESCRIPTION " & _
"FROM [VIDEOCONFERENCE ROOM SCHEDULE] INNER JOIN tblRoom
ON
[VIDEOCONFERENCE ROOM SCHEDULE].Room_Code =
tblRoom.Room_Code " & _
"WHERE ((([VIDEOCONFERENCE ROOM SCHEDULE].DATE)=#" &
[forms]!
[frmScheduling]![date] & "#) AND ((tblRoom.Room_Name)=" &
[forms]!
[frmScheduling]![cmbRoom] & ") AND (([VIDEOCONFERENCE ROOM
SCHEDULE].[MEETING START TIME]) Between #" & [forms]!
[frmScheduling]![meeting start time] & "# And #" & [forms]!
[frmScheduling]![meeting end time] & "#)) OR
((([VIDEOCONFERENCE ROOM SCHEDULE].DATE)= #" & [forms]!
[frmScheduling]![date] & "#) AND ((tblRoom.Room_Name)= " &
[forms]!
[frmScheduling]![cmbRoom] & ") AND (([VIDEOCONFERENCE ROOM
SCHEDULE].[MEETING END TIME]) Between #" & [forms]!
[frmScheduling]![meeting start time] & "# And #" & [forms]!
[frmScheduling]![meeting end time] & "#));"

Phew !)

Steve.
-----Original Message-----
Thanks Steve - the SQL is very long, can you tell me how
to enter it? I've never been able to break up long lines
of code, probably simple but I have a block....

Here it is:

SELECT [VIDEOCONFERENCE ROOM SCHEDULE].DATE,
tblRoom.Room_Name, [VIDEOCONFERENCE ROOM
SCHEDULE].Room_Code, [VIDEOCONFERENCE ROOM SCHEDULE].
[MEETING START TIME], [VIDEOCONFERENCE ROOM SCHEDULE].
[MEETING END TIME], [VIDEOCONFERENCE ROOM
SCHEDULE].BUREAU, [VIDEOCONFERENCE ROOM SCHEDULE].
[SCHEDULED BY], [VIDEOCONFERENCE ROOM
SCHEDULE].Date_scheduled, [VIDEOCONFERENCE ROOM
SCHEDULE].DESCRIPTION
FROM [VIDEOCONFERENCE ROOM SCHEDULE] INNER JOIN tblRoom ON
[VIDEOCONFERENCE ROOM SCHEDULE].Room_Code =
tblRoom.Room_Code
WHERE ((([VIDEOCONFERENCE ROOM SCHEDULE].DATE)=[forms]!
[frmScheduling]![date]) AND ((tblRoom.Room_Name)=[forms]!
[frmScheduling]![cmbRoom]) AND (([VIDEOCONFERENCE ROOM
SCHEDULE].[MEETING START TIME]) Between [forms]!
[frmScheduling]![meeting start time] And [forms]!
[frmScheduling]![meeting end time])) OR
((([VIDEOCONFERENCE ROOM SCHEDULE].DATE)=[forms]!
[frmScheduling]![date]) AND ((tblRoom.Room_Name)=[forms]!
[frmScheduling]![cmbRoom]) AND (([VIDEOCONFERENCE ROOM
SCHEDULE].[MEETING END TIME]) Between [forms]!
[frmScheduling]![meeting start time] And [forms]!
[frmScheduling]![meeting end time]));


-----Original Message-----
Try entering the SQL into the OpenRecordset statement;

Set rst = db.openrecordset("SELECT * FROM tbl")

Hope this helps,
Steve.

.
Click to expand...
.
Click to expand...
 
Back
Top