Preventing duplicate records

[gharden]

The title sounds deceptively easy to solve, but I can't figure out how to
make a table refuse to allow a new record that doesn't duplicate a
combination of fields. I already have a primary key (autonumber) and a date
and a location field. The ID field can't be duplicated, but it's ok for the
date OR for the location to be duplicated. However, it is NOT ok for the
combination of the date and the location to be entered.

Any ideas how to implement that constraint?

Thanks, Gina

 

[Allen Browne]

Use a unique index on the combination of the date + location.

1. Open the table in design view.

2. Open the Indexes box (toolbar/ribbon.)

3. On a blank line of the Indexes box, enter a name for the index, and the
first field.

4. In the lower pane, set Yes for Unique.

5. On the next line of the box, leave the index name blank (indicating this
line is part of the previous name), and enter the next field. It will now
look something like this:
Index Name Field Name Sort Order
================================
PrimaryKey ID Ascending
DateLocation TheDate Ascending
LocationID Ascending

Hopefully your date field is not really named "Date", as that's a reserved
word.

 

[gharden]

Thanks, that's very helpful. But um, reserved? Reserved for what? (of course
my field name is Date...never knew it was reserved for anything...what
problem will that cause?)

 

[Allen Browne]

In a query, you may get a "Too complex" error message since Access doesn't
understand the word. You can probably fix that by placing square brackets
around the field name, and adding the table name as prefix, e.g.
[Table1].[Date]

In other contexts (form/report), Access my misunderstand it as a reference
to the system date instead of the field named Date, and so the wrong data is
shown/filtered/not sorted, etc.

There are actually quite a number of these reserved words and problem names:
http://allenbrowne.com/AppIssueBadWord.html

The other one that's really common and problematic is Name, since Access
mistakes it for the Name of the form/report instead of the contents of the
field named Name.

 

[cw]

Hi gharden,
Did you ever get this resolved? I have a much easier way of doing this if
you still need a solution.