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
Wobbles said:
I re write this question as I think i failed to provide enought information
for my previous helpers to help me completely (that and the fact it's done my
head in) Thank you by the Way those guys. o.k Here goes .
I have a table Called "tEvent" which stores the start date and an end date
of a booking plus whom has the booking, the fields are named [Start] , [End]
& [LocationId] - I have am trying to check dates as i select them from a
calender routine filling in a field on my form , i would like to check this
date to ensure it is not already booked on any date in "tEvent" between
existing "Start" and "Finish" dates then either provide an error message or
proceed to the the bookings end date and again checking its validity. i'm a
newbie so please give it to me easy and simple.the form i'm using is called
[Booking Application] and it contains two fields with calender buttons
returning the chosen dates. HELP !!!! PLEASE !!!!
