Help with dates -

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 !!!!

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" wrote:

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 !!!!

your logic to identify an overlap makes sense, Ross; suggest you tighten up
the Loop code to make it more efficient. once a single overlap is
identified, there's no need to continue checking those particular dates by
running subsequent If statements, or checking additional records in the
recordset. how about modifying the code inside your Do...Loop a bit, as

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
Exit Do
ElseIf 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
Exit Do
ElseIf 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
Exit Do
End If
.MoveNext

hth


"Ross" wrote in message
...
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" wrote:

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 !!!!

Thanks Tina

Yes you are quite right! I just did that code very quickly as I was in a
bit of a hurry at the time. I was taking my wife & her mum out soon after
that.

Wobbles, if you have a look at Tinas modifications - each If/Then/End If
block now has an Exit Do statement. What that does is that if there is an
overlap identified with a pre-existing record then the update is cancelled,
so there is no need to go through all the other pre-existing records.

If you have quite a few records for a given location, then that will save
time in running this code & also there will not be the potential to have more
than one Message Box generated if there is more than one overlap.

Thanks Tina, appreciate your help.

Kind regards

Ross Petersen


"tina" wrote:

your logic to identify an overlap makes sense, Ross; suggest you tighten up
the Loop code to make it more efficient. once a single overlap is
identified, there's no need to continue checking those particular dates by
running subsequent If statements, or checking additional records in the
recordset. how about modifying the code inside your Do...Loop a bit, as

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
Exit Do
ElseIf 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
Exit Do
ElseIf 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
Exit Do
End If
.MoveNext

hth


"Ross" wrote in message
...
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" wrote:

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 !!!!

no problem, Ross, i've had folks help me out the same way plenty of times.



"Ross" wrote in message
news
Thanks Tina

Yes you are quite right! I just did that code very quickly as I was in a
bit of a hurry at the time. I was taking my wife & her mum out soon after
that.

Wobbles, if you have a look at Tinas modifications - each If/Then/End If
block now has an Exit Do statement. What that does is that if there is an
overlap identified with a pre-existing record then the update is
cancelled,
so there is no need to go through all the other pre-existing records.

If you have quite a few records for a given location, then that will save
time in running this code & also there will not be the potential to have
more
than one Message Box generated if there is more than one overlap.

Thanks Tina, appreciate your help.

Kind regards

Ross Petersen


"tina" wrote:

your logic to identify an overlap makes sense, Ross; suggest you tighten
up
the Loop code to make it more efficient. once a single overlap is
identified, there's no need to continue checking those particular dates
by
running subsequent If statements, or checking additional records in the
recordset. how about modifying the code inside your Do...Loop a bit, as

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
Exit Do
ElseIf 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
Exit Do
ElseIf 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
Exit Do
End If
.MoveNext

hth


"Ross" wrote in message
...
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" wrote:

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 !!!!

Thanks Ross and Tina, I am away the fourthcoming week so i will have to wait
until the end of the month to put you wonderful solutions into action , i
have every confidence they are going to solve my problems, Thanks once again !

"tina" wrote:

no problem, Ross, i've had folks help me out the same way plenty of times.



"Ross" wrote in message
news
Thanks Tina

Yes you are quite right! I just did that code very quickly as I was in a
bit of a hurry at the time. I was taking my wife & her mum out soon after
that.

Wobbles, if you have a look at Tinas modifications - each If/Then/End If
block now has an Exit Do statement. What that does is that if there is an
overlap identified with a pre-existing record then the update is
cancelled,
so there is no need to go through all the other pre-existing records.

If you have quite a few records for a given location, then that will save
time in running this code & also there will not be the potential to have
more
than one Message Box generated if there is more than one overlap.

Thanks Tina, appreciate your help.

Kind regards

Ross Petersen


"tina" wrote:

your logic to identify an overlap makes sense, Ross; suggest you tighten
up
the Loop code to make it more efficient. once a single overlap is
identified, there's no need to continue checking those particular dates
by
running subsequent If statements, or checking additional records in the
recordset. how about modifying the code inside your Do...Loop a bit, as

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
Exit Do
ElseIf 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
Exit Do
ElseIf 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
Exit Do
End If
.MoveNext

hth


"Ross" wrote in message
...
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" wrote:

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 !!!!