Hi Wobbles

I am assuming that your form has the following names for the controls:

[Start] calStart
[End] calEnd
User txtUser
LocationID cboLocationID (this is a combo box - that looks up the locations
from a separate table)

If you select the form in design view & open the property window & select
the Event tab, you should see an event called "Before Update".  If you then
click on the ellipsis (...) & choose code builder the VBA Editor will open.

You should see two lines of code like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

End Sub

Copy & paste the following code between the xxxxxxxxxxxxxxxxxxxxx's

xxxxxxxxxxxxxxxxxxxxxxxxxxxx

   Dim cnn     As ADODB.Connection
   Dim rs      As ADODB.Recordset
   Dim strSQL  As String
   
   'create new instances of the connection to the DB and the recordset for
the table tEvent
   Set cnn = New ADODB.Connection
   Set cnn = CurrentProject.Connection
   Set rs = New ADODB.Recordset
   
   'string expression to select all fields from the table tEvent but
limited to the location specified _
   in the Location combo box and ordered by the Start Date
   strSQL = "SELECT * FROM tEvent WHERE [LocationID] = " & Me.cboLocationID
& " ORDER BY [Start] ASC"
   
   With rs
   
       'open the recordset based upon the string expression above
       .ActiveConnection = cnn
       .CursorLocation = adUseServer
       .CursorType = adOpenKeyset
       .LockType = adLockReadOnly
       
       .Open strSQL, , , , adCmdText
       
       'start st the first record
       .MoveFirst
       
       'loop through all the records returned, and check to see if there
are any overlaps in dates _
       for the specified location
       
       'if there is an overlap, then generate a msg for the user and cancel
the update
       Do
       
           'there are three possibilities for an overlap
         
           '(1)    the new start date is before or equal to the
pre-existing end date _
                   and is also after or equal to the pre-existing Start Date
               
           '(2)    the new end date is between (or equals) the pre-existing
end date & start date
           
           '(3)    the new start date is before or equals the pre-existing
start date and the _
                   end date is is after or the same as the pre-existing end
date          
           If Me.calStart.Value <= ![End] And Me.calStart.Value >= ![Start]
Then
         
               MsgBox "sorry, this booking overlaps with a pre-existing
booking.", vbOKOnly + vbExclamation, "Overlap"
               Cancel = True
         
           End If
         
           If Me.calEnd.Value >= ![Start] And Me.calEnd.Value <= ![End] Then
           
               MsgBox "sorry, this booking overlaps with a pre-existing
booking.", vbOKOnly + vbExclamation, "Overlap"
               Cancel = True
               
           End If
           
           If Me.calStart.Value <= ![Start] And Me.calEnd.Value >= ![End]
Then
           
               MsgBox "sorry, this booking overlaps with a pre-existing
booking.", vbOKOnly + vbExclamation, "Overlap"
               Cancel = True
               
           End If
           
           'go to the next record & continue looping unless have reached EOF
           .MoveNext
       
       Loop Until .EOF
   
   End With
   
   'having done all teh checks, just clean up
   
   If rs.State <> adStateClosed Then
       rs.Close
   End If
   
   If cnn.State <> adStateClosed Then
       cnn.Close
   End If
   
   Set rs = Nothing
   Set cnn = Nothing

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You may have to amend the control names in the code to suit the names that
you have used for the various controls.

Basically, what this code does is before a new record is saved, (the Before
Update event happens before a record is saved - so it can be cancelled
easily), it does a lookup in the underlying table for the selected Location
(cboLocationID) and then runs a series of checks against each of the records
that have been returned (these are previous records that have already been
saved - not the new one).

If there is an overlap, thena message box is generated advising the user and
the update is cancelled (Cancel = True)

That Do ... Loop is just there to loop through all the records that have
been returned.

The final bit down the bottom of the code is just there to close the
recordset & the connection & release resources.

I know I went into a lot of detail, but you wanted it step by step.  If you
need any extra help, just yell.

Hope this helps.

Kind regards

Ross Petersen