Check if value in table already exists

[Fransdekock]

Hi

I have a problem where a user would enter a project code eg 113-099. I put in a list box that filters all the project codes for instance if the user enters 1 it will show all codes starting with a 1 and every additional number they enter it filters the list box to show which codes already exists! I hope this makes sense.

The problem i have is i need to check when they enter the project code that it is a unique value. The Database developer we got in didnt index this field as no duplicates allowed and as result we have large amount of duplicate project codes.

I have started to experiment with Dlookup but with no great success.

If anyone can help it would be much appreciated as i dont have much experience with access.

The field name on the form is

[forms]![projects_frm].[project_job_number] and the table which stores this information is [projects]

 

[neileg]

You are going to have to eliminate the duplicates before you can move on this, I think. Otherwise you are going to have to return all the projects with that code and get the user to pick the right one - messy.

 

[KeithG]

Dlookup([project_job_number],"projects","[project_job_number]=" & [forms]![projects_frm].[project_job_number])

Try the above Dlookup. Is [project_job_number] a text or numeric field?

 

[Fransdekock]

Thx for the comeback! [project_job_number] is a text field.

Should i use

If isnull(Dlookup([project_job_number],"projects","[project_job_number]=" & [forms]![projects_frm].[project_job_number])) then

as validation ?

 

[g-richardson]

I may be missing something, but I think you might just create a FIND DUPLICATES query (pre-designed in access). Select the table that contains your project job number.

look for project job number

show ALL OTHER FIELDS TOO

run query.

You will be left with a list of all project job numbers that are duplicates. You should be able to modify duplicates to make them unique...add a 1 or 2 after is all it takes.

Caution that if this data is involved in a relationship it will be changed throughout so if you identify with clients by this number, you will need to let them know of a change.

Hope this helps.