Duplicate bookings

[Dominic A]

I have a database that schedules staff availability.

If I have a booking at 9am for 1.5 hours, how do I stop a booking being made
for say 9.15am ?

I'm happy to use a SQL statement or VBA, but just not sure what to check
for?

Any examples would be great.

Dom

 

[Guest]

not full code and not tested, maybe put code in beforeupdate event

Dim sqlst As String
Dim con, rs As Object

sqlst = "Select *
From tblbookings
Where (StartTime < " & Me![timeentered] & ") and ((StartTime + Duration) >
" & Me![timeentered] ' Maybe you could use between

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.recordset")

rs.Open sqlst, con, 1
If not rs.eof then
msgbox("Time alreay scheduled")
end if

rs.close
set rs = nothing

 

[Albert D.Kallal]

The trick in a booking system is to only store the start and end date of the
booking.

And, to prevent collisions, the logic here is quite simple:

A collision occurs when:


RequestStartDate <= EndDate
and
RequestEndDate >= StartDate

The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.

In addtion, if you store date+time, then you can book a room for 1 hour,or
even a week!!


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates (and time in your case).

Howver, what is nice here is that
the simple condistion above does return ANY collsion....


--