Form code

[ladybug via AccessMonster.com]

I have a table with four fields:
SupervisorID
EmployeeID
StartDate
EndDate

I want to set it where an employee can have multiple entries (supervisors),
but can only have one entry without an end date. I have a form that looks
like the example below. Can someone give me a code that I could use in a
before update so that this will work?

For example:

SupervisorID EmployeeID StartDate EndDate
Adams1 Smith1 06/01/07 07/01/07
Miller1 Smith1 07/02/07
08/01/07
Thompson1 Smith1 08/02/07

 

[Arvin Meyer [MVP]]

If it's not likely that an employee can have 2 different Supervisors at 1
time, create a unique index on the last 3 fields. If it is possible, create
the index on all 4 fields.

 

[ladybug via AccessMonster.com]

This table can have the same employee in there multiple times as well as
under the same superivsor (they could be under a supervisor and then moved to
another and then at a later date go back under the first supervisor).
I want to make sure that under each employee there is only one supervisor
relationship without an end date.

If it's not likely that an employee can have 2 different Supervisors at 1
time, create a unique index on the last 3 fields. If it is possible, create
the index on all 4 fields.

I have a table with four fields:
SupervisorID

[quoted text clipped - 17 lines]

08/01/07
Thompson1 Smith1 08/02/07

 

[Klatuu]

"If it's not likely that an employee can have 2 different Supervisors at 1
time"

Obviously you have not seen the movie "Office Space"
If you have ever worked in a software development company, it is one that
will be, at the same time, both hilarious and painful.
--
Dave Hargis, Microsoft Access MVP

If it's not likely that an employee can have 2 different Supervisors at 1
time, create a unique index on the last 3 fields. If it is possible, create
the index on all 4 fields.

 

[Arvin Meyer [MVP]]

By creating a unique index across all 4 fields, Access will not allow more
than 1 end date (or any other field) to be null per employee or
supervisor/employee. Null does not equal another Null. Null is an unknown,
so Access will not allow more than 1 Supervisor/Employee without an end
date. Try it and watch the error messages that are returned.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

This table can have the same employee in there multiple times as well as
under the same superivsor (they could be under a supervisor and then moved
to
another and then at a later date go back under the first supervisor).
I want to make sure that under each employee there is only one supervisor
relationship without an end date.

If it's not likely that an employee can have 2 different Supervisors at 1
time, create a unique index on the last 3 fields. If it is possible,
create
the index on all 4 fields.

I have a table with four fields:
SupervisorID

[quoted text clipped - 17 lines]

08/01/07
Thompson1 Smith1 08/02/07

 

[Arvin Meyer [MVP]]

Obviously you have not seen the movie "Office Space"
If you have ever worked in a software development company, it is one that
will be, at the same time, both hilarious and painful.

My fellow workers bought me a red stapler. As soon as I watched the movie to
understand the reason, they hid it. I retaliated with the WedOne:

http://www.datastrat.com/Download/WedOne.zip

Yes, I have worked for a software development company, several of them, in
fact. Usually, I was in charge, but I have worked under multiple
supervisors. It is a giant cluster ... well, you know what I mean.