Check Date/Time Function

[Guest]

Here is my situation. I track a number of committees. I need to record meetings by location, committee, and meeting start and end times. In other words, our secretary will record a meeting by Committee A on January 22, 2004 at 13:00 until 14:00 in the Boardroom. The tables I have are:
<table> atnMEETING
<fields>
CommitteeID
MeetingDate
StartTime
EndTime
LocationID

<table> refLOCATION
<fields>
LocationID
LocationName
Capacity

<table> atnCOMMITTEE
<fields>
CommitteeID
CODE
CommitteeName
Active
CreationDate
EndDate

I would like to create a function that checks the date and time before the secretary enters the meeting. This way, she will know if there is a conflict on the specific date/time and location.

Thanks,

Eric

 

[Steve Schapel]

Eric,

Caution: untested "air code" ahead...

************
Private Function CheckAvailability() As Boolean
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM atnMEETING" & _
" WHERE (MeetingDate =#" & Me.MeetingDate & "#)" & _
" AND (LocationID =" & Me.LocationID) & _
" AND ((StartTime Between #" & Me.StartTime & _
"# And #" & Me.EndTime & "#)" & _
" OR (EndTime Between #" & Me.StartTime & _
"# And #" & Me.EndTime & "#))"
Set rst = CurrentDb.OpenRecordset(strSQL)
CheckAvailability = (rst.RecordCount = 0)
rst.Close
Set rst = Nothing
End Function

.... then, on the BeforeUpdate event of the applicable controls...

If (IsNull(Me.MeetingDate) + IsNull(StartTime) + IsNull(EndTime) _
+ IsNull(LocationID)) = 0 Then
If CheckAvailability Then
' proceed
Else
Cancel = True
MsgBox "Room already booked!"
End If
End If
**************