A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

tricky validation code



 
 
Thread Tools Display Modes
  #1  
Old September 23rd, 2008, 09:52 PM posted to microsoft.public.access
wjn london
external usenet poster
 
Posts: 1
Default tricky validation code

I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2 bookings
can be booked on the same day, same start time and for the same resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.

Thanks for any help in advance
Warren
  #2  
Old September 24th, 2008, 01:21 AM posted to microsoft.public.access
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default tricky validation code

It sounds like you are trying to re-create some of the functionality that a
calendaring/scheduling application like, say, Microsoft Outlook already
provides.

Is there a reason this has to be done using Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"wjn london" wjn wrote in message
...
I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2 bookings
can be booked on the same day, same start time and for the same resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls
within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.

Thanks for any help in advance
Warren



  #3  
Old September 24th, 2008, 06:39 AM posted to microsoft.public.access
Dominic Vella
external usenet poster
 
Posts: 80
Default tricky validation code

Yes that is tricky

Well, whilst I can think of some technical ways of doing this, it may be
best to add a "Check Availability" button. In there I would write a routine
to do the check.

Without knowing your field and table names it would be difficult to explain
any suggested code.


Dom.
www.effectivedata.com.au

"wjn london" wjn wrote in message
...
I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2 bookings
can be booked on the same day, same start time and for the same resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls
within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.

Thanks for any help in advance
Warren


  #4  
Old September 24th, 2008, 03:08 PM posted to microsoft.public.access
Peter Hibbs
external usenet poster
 
Posts: 871
Default tricky validation code

Warren,

This problem is tricky but I do it like this.

You haven't given us much information so I will make a few
assumptions. I will assume you have a table which holds the existing
bookings (call it tblDiary) and it has an AutoNumber field called ID,
a Date/Time field called StartTime which holds the time AND date of
the start time for a booking and also another field called EndTime
which holds the time AND date of the end time of the booking.

I'll also assume you are only concerned with bookings that do not run
into two or more days. The code below will handle that with a small
modification.

The trick is to compare the Start and End times of the new booking
with the start and end times of each existing booking, if the new
start time is less than the existing booking end time AND the new end
time is greater than the existing booking start time then the time
periods must overlap. It takes a bit of lateral thinking to get your
head round the logic but it works (I hope).

To try it out paste the code below into your form code module and
change the table and field names as appropriate. Call it with the new
start and end time like this :-

Dim vID As Long

vID = ApptCheck(txtStartTime, txtEndTime)
If vID 0 then
MsgBox "Times overlap"
Exit Sub
End If
'Add new record to table here.

If the time periods overlap the function returns the ID of the booking
record which it overlaps, you can then use this information to show
the user which existing booking record already exists for that time.

The way I normally do it is to create an unbound form which has
controls such as Text and Combo boxes to enter the booking
information. I have an 'OK' button which, when the user has entered
the required start and end times and any other info, he clicks which
does the above test and then adds a new record to the table or shows
an appropriate error message.

You could also do it the way you mentioned using the Before Update
event but I don't like adding records to tables and then removing
them, especially in a multi-user system.

Another option you might like to consider is to show the existing
bookings in a 'graphical mode' so that the user can see all the
existing bookings along with the start and end times. This will make
it a lot easier for them to see when they are trying to create an
overlapping booking. You can do this with a Flex Grid control, see
this site for my Flex Grid demo program which does just that :-

http://www.rogersaccesslibrary.com/f...3b 1d2zcccfb4

I also have another Flex Grid demo which does more or less what you
are trying to do, if you are interested in seeing it then drop me an
email, my address is in the Flex Grid documentation.



Public Function ApptCheck(vStart As Date, vEnd As Date) As Long

'Checks if appointment overlaps existing appointment
'Entry vStart = Start date and time of new appt
' vEnd = End date and time of new appt
'Exit ApptCheck = ID of existing app't if new appt overlaps or 0 if
no overlap

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblDiary WHERE "
_
& "DateValue(StartTime) = #" & Format(vStart, "yyyy/m/d") & "#")
Do Until rst.EOF
If vStart rst!EndTime And vEnd rst!StartTime Then
ApptCheck = rst!ID
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Function

(Watch out for word wrapping when you paste the code).

HTH

Peter Hibbs.



On Tue, 23 Sep 2008 13:52:01 -0700, wjn london wjn
wrote:

I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2 bookings
can be booked on the same day, same start time and for the same resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.

Thanks for any help in advance
Warren

  #5  
Old September 24th, 2008, 05:29 PM posted to microsoft.public.access
wjn london[_2_]
external usenet poster
 
Posts: 2
Default tricky validation code

Peter,

Thanks for the great reply you've given me lots to think about. You're
assumptions where almost correct (in fact i store the date in a seperate
field from the start and end times). I'm sure I will be able to devise a
solution from here, and I will definately take a look at the flex grid over
the weekend when i've got some time.

Thanks again
Warren


"Peter Hibbs" wrote:

Warren,

This problem is tricky but I do it like this.

You haven't given us much information so I will make a few
assumptions. I will assume you have a table which holds the existing
bookings (call it tblDiary) and it has an AutoNumber field called ID,
a Date/Time field called StartTime which holds the time AND date of
the start time for a booking and also another field called EndTime
which holds the time AND date of the end time of the booking.

I'll also assume you are only concerned with bookings that do not run
into two or more days. The code below will handle that with a small
modification.

The trick is to compare the Start and End times of the new booking
with the start and end times of each existing booking, if the new
start time is less than the existing booking end time AND the new end
time is greater than the existing booking start time then the time
periods must overlap. It takes a bit of lateral thinking to get your
head round the logic but it works (I hope).

To try it out paste the code below into your form code module and
change the table and field names as appropriate. Call it with the new
start and end time like this :-

Dim vID As Long

vID = ApptCheck(txtStartTime, txtEndTime)
If vID 0 then
MsgBox "Times overlap"
Exit Sub
End If
'Add new record to table here.

If the time periods overlap the function returns the ID of the booking
record which it overlaps, you can then use this information to show
the user which existing booking record already exists for that time.

The way I normally do it is to create an unbound form which has
controls such as Text and Combo boxes to enter the booking
information. I have an 'OK' button which, when the user has entered
the required start and end times and any other info, he clicks which
does the above test and then adds a new record to the table or shows
an appropriate error message.

You could also do it the way you mentioned using the Before Update
event but I don't like adding records to tables and then removing
them, especially in a multi-user system.

Another option you might like to consider is to show the existing
bookings in a 'graphical mode' so that the user can see all the
existing bookings along with the start and end times. This will make
it a lot easier for them to see when they are trying to create an
overlapping booking. You can do this with a Flex Grid control, see
this site for my Flex Grid demo program which does just that :-

http://www.rogersaccesslibrary.com/f...3b 1d2zcccfb4

I also have another Flex Grid demo which does more or less what you
are trying to do, if you are interested in seeing it then drop me an
email, my address is in the Flex Grid documentation.



Public Function ApptCheck(vStart As Date, vEnd As Date) As Long

'Checks if appointment overlaps existing appointment
'Entry vStart = Start date and time of new appt
' vEnd = End date and time of new appt
'Exit ApptCheck = ID of existing app't if new appt overlaps or 0 if
no overlap

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblDiary WHERE "
_
& "DateValue(StartTime) = #" & Format(vStart, "yyyy/m/d") & "#")
Do Until rst.EOF
If vStart rst!EndTime And vEnd rst!StartTime Then
ApptCheck = rst!ID
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

End Function

(Watch out for word wrapping when you paste the code).

HTH

Peter Hibbs.



On Tue, 23 Sep 2008 13:52:01 -0700, wjn london wjn
wrote:

I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2 bookings
can be booked on the same day, same start time and for the same resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.

Thanks for any help in advance
Warren


  #6  
Old September 24th, 2008, 10:19 PM posted to microsoft.public.access
Paul Shapiro
external usenet poster
 
Posts: 635
Default tricky validation code

You can check in the BeforeUpdate routine for the new row. If you want to
identify the existing conflicting booking, you could use something like
this:

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") &
"# "

strSQL = "Select * From tblBooking Where " _
& strProposedStart & " Between startDateTime And EndDateTime " _
& "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _
& "Or (" & strProposedStart & " startDateTime And " & strProposedEnd & "
EndDateTime);"


The logic identifies a conflicting row as one whe
a) the new start is within an existing booking
or b) the new end is within an existing booking
or c) the new booking "surrounds" an existing booking

Opening a recordset with this expression returns any overlapping existing
row(s), which you can choose to display to the user and then cancel the
update. If the recordset is empty, there's no conflict. If you only want to
know if there is a conflict, and don't need the details, use a Count
function and check for greater than zero.

"Dominic Vella" wrote in message
...
Yes that is tricky

Well, whilst I can think of some technical ways of doing this, it may be
best to add a "Check Availability" button. In there I would write a
routine to do the check.

Without knowing your field and table names it would be difficult to
explain any suggested code.


Dom.
www.effectivedata.com.au

"wjn london" wjn wrote in message
...
I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2
bookings
can be booked on the same day, same start time and for the same resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls
within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.

Thanks for any help in advance
Warren



  #7  
Old September 24th, 2008, 10:33 PM posted to microsoft.public.access
Paul Shapiro
external usenet poster
 
Posts: 635
Default tricky validation code

Actually, I think the logic can be simplified a bit. To avoid a conflict,
the new start time must be greater than the existing end time, or the new
end time must be before the existing start time. So a conflict is the
netative of this.

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") &
"# "

strSQL = "Select * From tblBooking Where NOT (" _
& strProposedStart & " endDateTime Or & strProposedEnd startDateTime);"

"Paul Shapiro" wrote in message
...
You can check in the BeforeUpdate routine for the new row. If you want to
identify the existing conflicting booking, you could use something like
this:

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn")
& "# "

strSQL = "Select * From tblBooking Where " _
& strProposedStart & " Between startDateTime And EndDateTime " _
& "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _
& "Or (" & strProposedStart & " startDateTime And " & strProposedEnd & "
EndDateTime);"


The logic identifies a conflicting row as one whe
a) the new start is within an existing booking
or b) the new end is within an existing booking
or c) the new booking "surrounds" an existing booking

Opening a recordset with this expression returns any overlapping existing
row(s), which you can choose to display to the user and then cancel the
update. If the recordset is empty, there's no conflict. If you only want
to know if there is a conflict, and don't need the details, use a Count
function and check for greater than zero.

"Dominic Vella" wrote in message
...
Yes that is tricky

Well, whilst I can think of some technical ways of doing this, it may be
best to add a "Check Availability" button. In there I would write a
routine to do the check.

Without knowing your field and table names it would be difficult to
explain any suggested code.


Dom.
www.effectivedata.com.au

"wjn london" wjn wrote in message
...
I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various
validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2
bookings
can be booked on the same day, same start time and for the same
resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls
within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.


  #8  
Old September 26th, 2008, 07:58 PM posted to microsoft.public.access
wjn london[_2_]
external usenet poster
 
Posts: 2
Default tricky validation code

Paul,
Thanks a lot for the help. Between you and peter I now have lots of options
and will spend some time experimenting with the code. Of course there may be
more than 1 booking per day of varying lengths but i get the idea of using
the select statement to scan the bookings table and the logic to check for
clashes.

thanks again
warren

"Paul Shapiro" wrote:

Actually, I think the logic can be simplified a bit. To avoid a conflict,
the new start time must be greater than the existing end time, or the new
end time must be before the existing start time. So a conflict is the
netative of this.

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn") &
"# "

strSQL = "Select * From tblBooking Where NOT (" _
& strProposedStart & " endDateTime Or & strProposedEnd startDateTime);"

"Paul Shapiro" wrote in message
...
You can check in the BeforeUpdate routine for the new row. If you want to
identify the existing conflicting booking, you could use something like
this:

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy hh:nn")
& "# "

strSQL = "Select * From tblBooking Where " _
& strProposedStart & " Between startDateTime And EndDateTime " _
& "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _
& "Or (" & strProposedStart & " startDateTime And " & strProposedEnd & "
EndDateTime);"


The logic identifies a conflicting row as one whe
a) the new start is within an existing booking
or b) the new end is within an existing booking
or c) the new booking "surrounds" an existing booking

Opening a recordset with this expression returns any overlapping existing
row(s), which you can choose to display to the user and then cancel the
update. If the recordset is empty, there's no conflict. If you only want
to know if there is a conflict, and don't need the details, use a Count
function and check for greater than zero.

"Dominic Vella" wrote in message
...
Yes that is tricky

Well, whilst I can think of some technical ways of doing this, it may be
best to add a "Check Availability" button. In there I would write a
routine to do the check.

Without knowing your field and table names it would be difficult to
explain any suggested code.


Dom.
www.effectivedata.com.au

"wjn london" wjn wrote in message
...
I am developing a db for my local community centre. They hire out some of
thier facilities. In the booking table and form i have various
validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2
bookings
can be booked on the same day, same start time and for the same
resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls
within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update" and
somehow check if my new start time falls between any previously entered
start-end times.
then cancel update if needed or allow update if no conflict.



  #9  
Old September 27th, 2008, 08:02 AM posted to microsoft.public.access
guzarva16
external usenet poster
 
Posts: 6
Default tricky validation code



"wjn london" wrote in message
...
Paul,
Thanks a lot for the help. Between you and peter I now have lots of
options
and will spend some time experimenting with the code. Of course there may
be
more than 1 booking per day of varying lengths but i get the idea of using
the select statement to scan the bookings table and the logic to check for
clashes.

thanks again
warren

"Paul Shapiro" wrote:

Actually, I think the logic can be simplified a bit. To avoid a conflict,
the new start time must be greater than the existing end time, or the new
end time must be before the existing start time. So a conflict is the
netative of this.

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy
hh:nn") &
"# "

strSQL = "Select * From tblBooking Where NOT (" _
& strProposedStart & " endDateTime Or & strProposedEnd
startDateTime);"

"Paul Shapiro" wrote in message
...
You can check in the BeforeUpdate routine for the new row. If you want
to
identify the existing conflicting booking, you could use something like
this:

strProposedStart = " #" & Format$(newBookingStartDateTime, "mm/dd/yyyy
hh:nn") & "# "
strProposedEnd = " #" & Format$(newBookingEndDateTime, "mm/dd/yyyy
hh:nn")
& "# "

strSQL = "Select * From tblBooking Where " _
& strProposedStart & " Between startDateTime And EndDateTime " _
& "Or " & strProposedEnd & " Between startDateTime And EndDateTime " _
& "Or (" & strProposedStart & " startDateTime And " & strProposedEnd
& "
EndDateTime);"

The logic identifies a conflicting row as one whe
a) the new start is within an existing booking
or b) the new end is within an existing booking
or c) the new booking "surrounds" an existing booking

Opening a recordset with this expression returns any overlapping
existing
row(s), which you can choose to display to the user and then cancel the
update. If the recordset is empty, there's no conflict. If you only
want
to know if there is a conflict, and don't need the details, use a Count
function and check for greater than zero.

"Dominic Vella" wrote in message
...
Yes that is tricky

Well, whilst I can think of some technical ways of doing this, it may
be
best to add a "Check Availability" button. In there I would write a
routine to do the check.

Without knowing your field and table names it would be difficult to
explain any suggested code.


Dom.
www.effectivedata.com.au

"wjn london" wjn wrote in message
...
I am developing a db for my local community centre. They hire out some
of
thier facilities. In the booking table and form i have various
validation
expressions which limit start times and end times and they work fine.
I use a secondary index on the bookings table to ensure that no 2
bookings
can be booked on the same day, same start time and for the same
resource.
eg. Main hall on 2/2/08 at 9:30:AM
my problem is How do I trap for overlapping bookings EG
Client1 books Main Hall on 2/2/08 from 9:00:AM till 11:30:AM
Client2 books Main Hall on 2/2/08 from 10:30:AM till 2:30:PM
this is missed because the start time is different but it still falls
within
the booking.

Hope this is clear.
I think i need a way to scan through the bookings on "before update"
and
somehow check if my new start time falls between any previously
entered
start-end times.
then cancel update if needed or allow update if no conflict.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.