Form code

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

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
 
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.
 
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
Click to expand...
[quoted text clipped - 17 lines]
08/01/07
Thompson1 Smith1 08/02/07
Click to expand...
Click to expand...
 
"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


Arvin Meyer said:
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.
Click to expand...
 
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

ladybug via AccessMonster.com said:
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
Click to expand...
[quoted text clipped - 17 lines]
08/01/07
Thompson1 Smith1 08/02/07
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...

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.
 
Back
Top