Double Bookings

[Flint]

I am setting up a bookings system, within this I would like a feature to book dates and times for jobs. This is proving difficult. I need to set up a validation rule to stop double bookings, has anyone got an idea of how I can do this?

The bookings table contains the following fields:

CustomerID
JobID
Date
StartTime
FinishTime

The system must eliminate double bookings so that the same time on same date cannot be entered more then once

E.g. If one record already has 'time start' as 14:00 and 'time finish' as 16:00, 'Date' as 31/03/04, when a new record is being added it should not accept it if you have 31/03/04 as "Date" with a "TimeStart or "TimeFinish" between 14:00 to 16:00.
It should accept it if its on the same date but outside this range, or in this range but on a different date
It should prevent any times from overlapping

If anyone could help in any way it would be massively appreciated

 

[dcx693]

Why not structure your table so that the date and start time become a compound primary key? Access would automatically not allow a user to enter a duplicate. You could also show your own custom error message in that case.

That's the easiest route, but if you want to do the checking yourself, use a DCount function in the AfterUpdate events of your Date and StartTime fields to check if any other records with the same Date/StartTime have already been entered. Something like:

IIf DCount("[Date]","myTable","[Date]=" & Me.txtDate & " AND [StartTime]=" & Me.txtStartTime) > 0 Then
    Cancel=True
End If

By the way, "Date" is not a good name to use for one of your fields. Date is a reserved VBA word, and it can cause confusion. Best to change it.

 

[WayneRyan]

Flint,

You should be able to use the BeforeInsert event of your
form to check for that.

The "Me." are the names of the controls on your form.

If DCount("[JobID]", _
         "tblBooking", 
         "[Date] = #" & Me.Date & "# And " & _
         "[CustomerID] = " & Me.CustomerID & " And (" & _
         Me.StartTime Between [StartTime] And [FinishTime] Or " & _
         Me.FinishTime Between [StartTime] And [FinishTime]& ")") > 0 Then
   MsgBox("That Time Is Already Booked")
End If

Wayne

 

[Flint]

I have gone onto form properties, event, BeforeInsert and put in that code. In visual basic it appeared in Red, and doesn't appear to be working. Any ideas why ?

 

[Flint]

Also why does it say:

"Compile Error

Expected: Expression"


When I try to enter the code in Visual Basic?

 

[dcx693]

Try removing these parentheses from Wayne's code:

MsgBox[COLOR=Red]([/COLOR]"That Time Is Already Booked"[COLOR=Red])[/COLOR]

But keep a space in between MsgBox and the first ".

 

[Mile-O]

Me.StartTime Between [StartTime] And [FinishTime]

Wayne's code does not look right.

The first thing in a Between structure should be the field name; not a control and the string literal isn't broken up by the correct delimiters.

 

[WayneRyan]

Hi all,

That must have been AnotherWayne.

I heard that he was out celebrating his Birthday ...

It seemed right at the time.

If DCount("[JobID]", _
          "tblBooking", _
          "[Date] = #" & Me.Date & "# And " & _
          "[CustomerID] = " & Me.CustomerID & " And " & _
          "([StartTime]  Between #" & [StartTime] & "# And #" & [FinishTime] & "# Or " & _
          " [FinishTime] Between #" & [StartTime] & "# And #" & [FinishTime] & "#)") > 0 Then
   MsgBox("That Time Is Already Booked")
End If

p.s. DCX is right, DON'T use DATE as a name.
p.p.s.s. (or whatever) As usual, you're right Mile!
p.p.p.s.s.s I'm regressing, I need training wheels!

Wayne

 

[Flint]

I have entered the code in my form in the properties on the "Code Builder" next to "Before Insert"

Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[JobID]", _
          "frmJobBookings", _
          "[JobDate] = #" & Me.JobDate & "# And " & _
          "[CustomerID] = " & Me.CustomerID & " And " & _
          "([StartTime]  Between #" & [StartTime] & "# And #" & [FinishTime] & "# Or " & _
          " [FinishTime] Between #" & [StartTime] & "# And #" & [FinishTime] & "#)") > 0 Then
   MsgBox ("That Time Is Already Booked")
End If
End Sub

Every time I try to insert data it starts the de bugger
Any ideas where its gone wrong?

The form is named: frmJobBookings
Fields are now:
CustomerID
JobID
JobDate
StartTime
FinishTime
The table this form is based on is: tblJobBookingDetails

 

[raskew]

It might be helpful to take a look at Allen Browne's article

on Clashing Events/Appointments.

Bob

 

[Flint]

Thanks for that link, the site has given me a way to check for double bookings, I've now solved my problem although not in the way I originally intended to