validation rules

[Guest]

i have built a timetable scheduling tool as part of my database, how do i set
up validation rules to ensure i cannot double book a session at the same time
and before and after the start time of the event

here is my table tructure

TblSchedule
---------------
SchedID (PK)
EventID
StartTime
EndTime
TeacherID
SessionID

TblSessions
---------------
SessionsID (PK)
Session

TblTeachers
---------------
TeachersID (PK)
Teacher

TblEventsLocation
---------------------
ID (PK)
EventID
DayTypeID
Date
Venue

hope this is enough info

thanks

Phil

 

[Guest]

How much time before and after the session?

 

[Jeff Boyce]

Phil

It sounds like you want to have Access check "available" and "unavailable"
times, and ensure that you don't double-book a time frame.

This isn't quite what Access refers to when it uses a "validation rule".

One way would be for you to create a procedure in your form's BeforeUpdate
event. In that procedure, you'll need to tell Access how (i.e., VBA code)
to evaluate a potential time slot.

How're your coding skills?

 

[Guest]

Hi,
You want to do the validation on the form level and not table level.
This way you have much more flexability with error messages and with the
validation itself.
Since you or users shouldn't enter data directly into tables anyway, but
rather through and interface (forms) you just need to set up some validation
code on a specific event of the data entry form (maybe before update event).
HTH
Good luck

 

[Guest]

thats exactly what i want access to do, the data is already entered in via a
form the type of data i would get would be

0900 - 0930 Welcome
0930 - 1000 What is Cancer
1000 - 1200 cell biology
1200 - 1300 Lunch

etc

i dont want to have to change the times to 0900 - 0929 930 - 9-59 etc. my
coding skills are pretty basic, i have used VBA to do things in my database
but would not know what to start with here

hope you understand this

Phil

 

[Jeff Boyce]

Phil

This would seem to be an additional wrinkle on your earlier post. I
understood that you did NOT want overlapping times. How do you propose to
let Access know that your "0930" ending time for "Welcome" is NOT the same
as the "0930" beginning time for your keynote address?

I fully realize that humans can interpret that, but Access is very literal.

I suppose one "brute force" method would be to always store one minute less
than the end-point, but display +1 minute for end-points on your
forms/reports.

Have you tried searching, say at Google, for "scheduling", "calendaring",
"reservations"? I'm fairly sure the folks who build those systems have had
to resolve this issue.