Prevent Duplicate Values with alert after user enters duplicate value

[accessaccess]

Hi everyone we are working on an Access (2007) database that is on a SharePoint Site (2007).

Currently the form is operational, but there is one last thing that would be nice to have.

The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.

In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.

All data is currently bound, so once the user makes a change it is made, no submit button is required.

We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.

TABLE - UPDATED HEADCOUNT
COLUMN in UPDATED HEADCOUNT - EMPID
FORM CELL user will input an EMPID - newEMPID
FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID

So below is what we are trying to do, we are sure there are a few commands missing....

=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")


any help would be much appreciated. thanks in advanced!

 

[Cronk]

Why not have EmpID as an Autokey primary key?

If the EmpIDs are predetermined, then set the index for the field to unique values. If you want to replace the Access inbuilt error message about not having duplicate values, then catch the problem in the BeforeInsert event of the form.

Incidentally, why are you storing numbers in text fields?

 

[accessaccess]

Cronk

thanks for the quick response...

How would i assign the EMPID as a primary key? i am assuming i would adjust my table settings?

if i had open resources that were blank would this cause the EMPID field to yield errors?


I was thinking if i had a cell in the form duplicateEMPID and it did a DLOOKUP based on the value that the user entered in newEMPID would this work? if the value entered was not already in the table then it would display the new value and write the updated EMPID to the table. if it already existed it would NOT write the value back to the table but an error message would alert the user to enter a valid EMPID that is not duplicate.

any thoughts on if the DLOOKUP i attempted to write will correctly perform this?

thanks in advanced! cheers!

 

[Cronk]

You set primary key in the table design. Every table should have a primary key.

Does your post imply that some records do not have a value set for EmpID? If so, you cannot set the field as primary key or with a unique index.

You'd then have to test for duplicates in the BeforeUpdate event, ignoring nulls, which if acceptable in your data model.

 

[accessaccess]

Cronk

Yes, there are empty and duplicate values so the EMPID is not a primary key.

Would it be possible if...

In the form I had a EMPID duplicate box (EMPID_DUP) which displayed a 'Y' OR 'N'

i had an input text box where the user would type in the EMPID

The EMPID_DUP did a DLOOKUP with the EMPID from the form and DLOOKUP'ed the EMPID column in the table? If it the same EMPID was present the EMPID_DUP would display Y' if it was not present then it would display a 'N'? then could i unbound the form so it would only 'SUBMIT' if the EMPID_DUP box had a 'N' else an error message box would display warning that since EMPID_DUP had a 'Y' data could not be submitted?

i am very comfortable in Excel and just getting my feet wet in Access so would like to learn more on how to do the same programming in Access.

 

[Cronk]

The first line in your first post said
"The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text"

Now you are saying you have duplicates.

What exactly are your trying to achieve?

While in tabular form, records in Access look like an Excel spreadsheet, but they are different. Every record in an Access table should have a unique identifier.

If EmpID is meant to be unique, find and eliminate duplicates and then make the field unique. Search for references to finding duplicates by the use of queries.

Incidentally, in your data model, what is the significance of EmpID?

 

[accessaccess]

EMP_ID is unique, but it is not 'unique' in Access, poor wording on my part.

The EMP_ID is like a SSN for an employee. there are some positions that are open and have not been filled and as a result they are blank and duplicate.

You mentioned the trying beforeInsert, how would i implement this code and would I edit the VBC in the form code?

Just learning so hopefully I am being clear and using the correct terminology.

I really appreciate your help so far!

 

[accessaccess]

Also since I have a linked table in Access to the SharePoint site I cannot make the EMP_ID unique and index without duplicates. I would have to change in the source data on SharePoint which 2007 unfortunately does not allow. Is there a work around?

 

[accessaccess]

For queries, I can create a quiery to search for duplicates based on the field in the table, but how would I insert the query result into the form so it would run and search based on what the user entered? ultimately i want to provide a Y/N if the user's EMPID input is already in the EMPID table.

 

[Cronk]

From what you've now said, it's evident your table structure is not right.

You should have at least a table for employees with PK EmpID and a table for positions with a PK of say PositionID and FK EmpID. (We are in a relational database now, not a spreadsheet.)

When you clear up duplicate EmpID in tblPositions, you can then have a unique index on this field to avoid duplicates in the future.

 

[accessaccess]

Due to having a linked table is there an alternative way to prevent duplicates?

 

[MarlaC]

You need to correct the table structure on the server, then refresh your table links.