Checking for duplicate entries

[terpster]

I'm trying to assign a manager to an employee using combo boxes in a form. I have an emp_mgr table that contains the empID and mgrID. I'd like to show an error message to anyone who tries to duplicate the emp/mgr relationship if it already exists in the database. How could I do this. Please be very specific!

Thanks in advance.

 

[Doc94]

You need to be a bit more specific. Are the mgrID's and empID's all unique? are the mgrs and empolyees all one to one relationships or one to many? Meaning do the mgrID's have more than one EmpID associated with them? And vise versa?

 

[SgtJarrow]

What you have is a classic many-to-many relationship here....

You have many many managers in one table and many employees in another. But what you want to do is match one manager with one employee only.....

Here's the best way to do it if all you want to do is keep the relationships unique.

Table of Managers
mgrID (primary key)

Table of Employees
empID (primary key)

Table of Relationships
AutoId
mgrID (Part 1 Primary Key)
empID (Part 2 Primary Key)

To set up the above primary key relationship, you select both the fields in the Relationship table and click the primary key icon.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[terpster]

I think my post was not clear enough. I have a emp table with a PK of empID and a manager table with mgrID with the emp_mgr table handling the many to many relationship. What I'm asking is.....

when using a form to assign the relationship how would I check to see if there is already an existing relationship in the emp_mgr table??

maybe that will help! Thanks!

 

[SgtJarrow]

If you are using a bound form to assign the relationships, then the work should already be done for you....it will detect the same key relationship and prompt you that you cannot save the record because this relationship already exists...

If you are using an unbound form, or for some reason this check is not working as it should, let me know and I will show you a bit of code that can do the check for you....but it is better to use Access' built in check when possible.

****************************
Only two things are infinite, the universe and human stupidity,
and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III
MCSA, CNA, MCP, Network+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com