Help Needed To Prevent Double Booking

[Wayne]

I'm building a bookings database for trucks that among other things
captures the TruckName, LoadDate, LoadTime, UnloadDate and UnloadTime.

Is there a simple way to prevent double bookings for any particular
truck so that when a new entry is being added, if the LoadDate and
LoadTime doubles up with an existing entry I can flag it to the user?

[Allen Browne]

Re: Help Needed To Prevent Double Booking

Use the BeforeUpdate event procedure of the *form* where you enter the data.
(Be sure to use the vent of the form, not that of the controls.)

The idea is to use DLookup() to see if another clash exists in the table. A
clash is defined as:
- that one starts before this one ends, AND
- this one starts before that one ends, AND
- it's the same truck, AND
- it's not the same record.

It would be easier if you combined the dates and times into the one field,
i.e. use 2 date/time fields only - say LoadDateTime and UnloadDateTime. If
you don't want to do this, add the 2 values together, i.e. LoadDate +
LoadTime.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Wayne" <cqdigital@volc anomail.comwrot e in message
news:15c346e9-c1bc-420c-a4d4-c6899d15c32b@s8 g2000prg.google groups.com...

I'm building a bookings database for trucks that among other things
captures the TruckName, LoadDate, LoadTime, UnloadDate and UnloadTime.
>
Is there a simple way to prevent double bookings for any particular
truck so that when a new entry is being added, if the LoadDate and
LoadTime doubles up with an existing entry I can flag it to the user?

[Albert D. Kallal]

Re: Help Needed To Prevent Double Booking

To prevent collisions, the logic here is quite simple:


A collision occurs when:


RequestStartDat e <= 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.


dim strWhere as string
dim dtRequeestStart Date as date
dim dtRequestEndDat e as date


dtRequestStartD ate = inputbox("Enter start Date")
dtRequestEndDat e = inputbox("Enter end date")


strWhere="#" & format(dtReques tStartDate,"mm/dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtReques tEndDate,"mm/dd/yyyy") & "# >= StartDate"


if dcount("*","tab leBooking",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. Howver, what is nice here is that
the simple condistion above does return ANY collsion.

The above also works well with time, or date + time fields....



--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Wayne]

Re: Help Needed To Prevent Double Booking

Thanks Allen and Albert. I'm working through this.