Rule to prohibit someone from working on a project or in a time sl

[Craig]

Hi

I have a database table where you enter records of people with specific
tasks in certain time slots.
For example:
Record 1: Jim Smith Project A Tues 10-12pm
Record 2: Nancy Jones Project B Wed 2-4pm
Record 3: Nancy Jones Project B Fri 10-12
Record 4: Jim Smith Project A Fri 10-12

Can I create a rule that prohibits entering records of certain people at
specific time slots? For example, you cannot enter Jim Smith for Project A
on Monday at 10-12?

Thank you!

Craig

 

[Keith Wilby]

Hi

I have a database table where you enter records of people with specific
tasks in certain time slots.
For example:
Record 1: Jim Smith Project A Tues 10-12pm
Record 2: Nancy Jones Project B Wed 2-4pm
Record 3: Nancy Jones Project B Fri 10-12
Record 4: Jim Smith Project A Fri 10-12

Can I create a rule that prohibits entering records of certain people at
specific time slots? For example, you cannot enter Jim Smith for Project
A
on Monday at 10-12?

Thank you!

Craig

Not at table level, no, but you should be using queries and forms to
manipulate your data. If you use a form you can put some validation code in
its Before Update event to determine whether or not the data is saved.
Having said that, it might be worth considering using a combo box on a form,
the combo could be set to show only available times for the user to choose
from.

Any mileage in that for you?

Keith.
www.keithwilby.co.uk

 

[KARL DEWEY]

If you mean you do not want a duplicate of 'Jim Smith for Project A on Monday
at 10-12' then create an index in the table and set to unique.

 

[TedMi]

From the data you provide, there is no way to determine that Jim Smith
Project A Mon 10-12 is disallowed. If that's what you really meant, you need
a table of disallowed times per user/project combo. Or did you mean that
times when users are already assigned are not available for new assignments?

In either case, I would recommend that you split the start and stop times
into separate fields, to handle this: if Jim Smith cannot be assinged on Mon
10-12, then presumably he also cannot be assigned Mon 9-11. If the two times
are in the same field, then 10-12 <> 9-11 and the assignment will be
allowed.

As Keith Wilby says, this is done at the form level, in the AfterUpdate
event of the form that creates a new assignment.
Say your input form is named fAsgn and contains the text fields txtEmpID,
txtDayOfWeek, txtStartTime, txtStopTime, bound to fields in tblAsgn with the
same names, but without the txt prefix.
I'm assuming that you want to disallow any assignments whose times overlap
existing assignments for a given person, regardless of project.

Disclaimer - Untested Code!
Create a query like this:

SELECT EmpID FROM tblAsgn WHERE
Forms!fAsgn!txtEmpID=EmpID AND Forms!fAsgn!txtDayOfWeek=DayOfWeek AND
(Forms!fAsgn!txtStartTime >= StartTime AND Forms!fAsgn!txtStartTime<StopTime
OR
Forms!fAsgn!txtStopTime > StartTime AND Forms!fAsgn!txtStopTime<=StopTime)

Say you name it qFindOverlap

In the fAsgn_AfterUpdate proc:

If DCount("EmpID", "qFindOverlap") > 0 then
MsgBox "whatever"
Cancel=TRUE
End If

An alternative is to build the criteria (the WHERE clause without the word
WHERE) in a string (say sCrit) within your proc, and:
If DCount("EmpID", "tblAsgn", sCrit)>) then...
However, this has two drawbacks - functions of this type run slower than
queries, and building the string in code requires some complex punctuation
to concatenate variables with literals.

Good luck!
-TedMi

 

[CraigH]

Hi,
Well Keith is correct you will be doing this through a form and not the
table - and it didn't look like is was only that you didn't want dupes for a
person. But there are questions that need to be asked before anything can be
considered.

The big problems is how do you pick the times - if it is something that is
standard the gets put in a table Like Tues 10-12, Mon 10-12, Mon 12-2 then
Karls method of non dups will work but I don't think that is correct - for a
meriod (sp) of reasons. i.e. what if you want 1-3, or 10-1.

The time should be a start time and and end time. But beyond that lets talk
about what you said "A rule that prohibits entering records of certain people"

Basically for that you wan't another table that has those conditions. You
will modify this to what your exact needs are (i.e. if you realy do have
slots laid out instead of real time)

PersonID ProjectID Days(use the day number) StartTime EndTime
John Smith A Mon (2) 10:00 AM 4:00 PM
John Smith Tues 8:00 AM
5:00 PM

John Smith can't be assigned to project A between 10 and 4 on mondays and no
projects on Tuesdays.

This would be compaired to the entry as Keith said in the before update
event when you put in the times.