Check for Duplicate Records

[Blackkimba851]

Using Access 2007


I have 4 fields. Autonumber, Room, Task, Status. Using a button to enter the new record from a form created from a table how do i check for duplicate records with all 3 fields (not including Autonumber) before access enters the new record? I would like to have a message box open to warn of duplicates and then return to form at the same place, not removing the options from the fields (using combobox's) so the client can see the error and fix it. Is this possible using a macro or dlookup? I tried a macro with a Query, IF statement like this- (on button click)

(IF [Room]=Forms![Room Tasks Query]![Room] And [Task]=Forms![Room Tasks Query]![Task] And [Status]=Forms![Room Tasks Query]![Status]

but it errored for every record duplicated or not.

Names of Forms

Table-Room Tasks
Form-Room Tasks
Query-Room Tasks Query

I would snapshot the macro but haven't figured out how to do that yet.
Any help would be appreciated.

Blackkimba

[davegri]

Use the FORM name in the macro.

IF [Room]=Forms![Room Tasks]![Room] And [Task]=Forms![Room Tasks]![Task] And [Status]=Forms![Room Tasks]![Status]

[orange]

See this link re composite unique index for one approach.

[Blackkimba851]

would this also work using a split form

[davegri]

would this also work using a split form

Pretty easy to try it.

[Blackkimba851]

Like this? IF [Room Tasks]![Room]=[Forms]![Room Tasks Query]![Room] And [Room Tasks]![Task]=[Forms]![Room Tasks Query]![Task] And [Room Tasks]![Status]=[Forms]![Room Tasks Query]![Status]

[davegri]

Like this?

No. Look at post #2 again.

[Blackkimba851]

Ok I tried it but it didn't work. let's forget what I'm trying. How would you do it?

[Blackkimba851]

It didn't seem to work either

[orange]

@blackimba851

The issue is storage of duplicate data in the table, so to remove it and prevent the acceptance of duplicates in the fields you identified:

-make a new table (tblNew) with same fields as the table involved
-create a unique composite index on the fields you want to be unique in all records (a per link in post #3)
- copy your existing table to this new table and you duplicates will be identified and will not be saved
- the new table will now have only unique values for the fields involved for all records
- rename your old table with some suffix eg -OLD
- rename the new table with the name you have been using with form etc.

Good luck.

[Blackkimba851]

I'll giver a try

[Blackkimba851]

that didn't seem to work either. The problem it seems to have is that when I pick the same room twice it calls the error.

[Blackkimba851]

Using the index didn't work. it calls an error if any of the fields are a duplicate. not all 3

[orange]

Did you follow the sample at the link in post #3?
You could post a copy of the database (zip format).