[rkselby98]

I have searched MS Office 2000 User Specialist Study Guide by SYBEX, Napier and Judd Comprehensice and to no avail not able to find an answer. Also searched MS Web for Access Help.

The problem is I am setting up a data base with names and addresses. I eant it to reject duplicate entries. I can set it so a field won't accept duplicates but I need to set it so the row of fields will not accept the entry if all fields are the same. If one of the fields is different I want it to accept the entry.

IE:

Name, Address, Phone
John, 1 Main, 555-555-1212
John, 2 Main, 555-555-1212
James, 1 Main, 555-555-1212
James, 1 Main, 212-555-1212

I realize the above is imposibe to have happen but this is just an example. The basics are the same.

[RandyG]

You can try http://www.access-programmers.co.uk/ for some assistance, or try to flag down YSB, who usually hangs out on the Development forum. Access is his forte!

[Anne Troy]

I order to do this properly, then, what you REALLY need to do is open up your table in design view.

Highlight ALL the fields. Click on the primary key toolbar button. There's a key on it (of course).

[rkselby98]

I'm still here. Thanks for the response.

[THoey]

I thought you didn't know MS Access? That is the answer I would have given...

Good Call... ;-)

[Anne Troy]

...coming from a master!

[rkselby98]

Sorry but that did not work. Maybe you didn't understand me. I want to be able to enter two records "ALMOST" identical but I do not want it to accept two records that "ARE IDENTICAL".

Thank both of you for your responses.

[rkselby98]

Sorry my mistake. I tried a new table any your way works great. Somewhere along the line I must have messed up the file I was working on because it didn't work with it.

I just decided to for the heck of it to try a newly created database and it works great.

OK found out what I did wrong. I had a field with auto number. When I changed that then the Unique Value worked on the table.

Thanks to both of you.

[Edited by rkselby98 on 04-11-2001 at 05:58 PM]

[YSB]

I've been rather busy (,lazy, or both )and havn't had much time to log on.

Dreamboat, that is an interesting solution. While it should work, there is a better way to do the same thing without losing the benefit of the PK and autonumber field. Access can create a multi-field unique index that is not a PK. To do this, open your table in design view. Click on View|Indexes. In the Index Name column, give the index any name you like. In the properties on the bottom set Unique to Yes. Starting from that row, enter all fields you want to include in the index, one on a line. As long as you don't enter anything in the Index Name comlumn, all subsequent rows will be included in the index. Just leave out the Autonumber field and any other fields that you don't want checked. Usually the PK should be just the autonumber field which sets that field to be the unique identifier for each record in the table.

If the DB is very large though, this method could have some side effects. Specifically it could greatly increase the size of the DB and cost you some speed. In that case I would prefer a validation method of some sort instead of the index. If this works for you though, I won't confuse the situation with other ways to accomplish the same thing. I would just make the above change so that you can still can a normal PK in the table.

Good Luck!