Double Booking Property problem

[iainmid]

Hi

I have created a database to take bookings for holiday properties but can't figure out how to stop the properties from being booked over the same period. I have a table called booking order with the fields Booking no, property no, start date and end date. On the equivilant form for a property i need to be able to stop start and end dates being entered that are between existing start and end dates already booked for that property.

Thanks for your help

 

[Dennisk]

All booking systems except single user require record/Table locking in order to ensure that the following problems do not occur.

Double bookings
Over bookings
Overlapping bookings

There are two ways to acheive this.
1) maintain a count of the numbers booked into that resource.
2) calculate the number of free spaces by querying the database.

Point 1 is the easiest to implement and the easiest to '**** up'
Point 2 is more difficult to implement but will return the most accurate up to date value for numbers currently booked.

Which every you choose the following stratergy applies

Lock something
Determine if a record can be booked
if so update database
unlock something

all updates need to executed within a transaction

to address your problem.
create a query that returns the start and stop dates for the property in question and store them in an array.

loop through the array and test your dates to be booked against the start stop dates already booked.
If you detect a double bookings stop your processing and return a boolean.

then in your main code you would do something like this

if not overbooking(PropertyID,StartDate,EndDate) then
book your property
endif

 

[iainmid]

Thanks Dennisk

Are you able to explain this is more detail as my programming skill aren't that great or do you know of any example of this type of problem

 

[GaryPanic]

check the samples section "Booking Systems"

there are sh*t loads there .

g

 

[RuralGuy]

See if this link helps at all.

 

[Dennisk]

in order to select all the records you would use something like this (I assume you can declare functions, variables, etc) this is in DAO

Set rstBookings=Currentdb.Openrecordset("SELECT FromDate, ToDate FROM tblBookings WHERE [PropertyID] =" & varPropertyID,dbOpenForwardOnly,dbDenyRead ,dbDenyRead)
intCounter=0
do until rstBookings.eof
redim Preserve ArFromDates(intCounter)
redim Preserve ArToDates(intCounter)
arFromDates(intCounter) = rstBookings!FromDate
arToDates(intCounter) = rstBookings!ToDate
intCounter=intCounter+1
rstBookings.Movenext
Loop

for j = 0 to intCounter
if varFrom >=arFromDates(i) and varTo<=arToDates(j) then
' overlapping booking
FunctionName=false
exit for
endif
next
'at this point you have a booking that can be made and the table is fully locked so apply your bookings and exit the function with the return value set to true

so when you call the function you can determine whether the update was successful or not.

This is all 'air code' so there may be mistakes in logic and I have left out bits here and there . but if your coding is shaky, it won't be after you get this working OK.

topics to look up in the help system

Declare arrays
redim
recordsets and the various options for reading and locking







endif