Select the first item in the Composite Key and make sure you set Unique to Yes.

I have done this

When you have four fields like that in the index, that means you can't have a duplicate value that matches all four of those fields. So, for it to be rejected, the first name, middle name, last name, and suffix would all have to be identical.

If you had Richard D. Rost Jr. and Richard D. Rost Sr. that would be allowed, because one of those fields is different. And yes, I'm a Jr. :)

That is what I want. But my database is allowing Mark Burns and Mark Burns, same name. I can't find out why. Is it something to do with the ignore nulls part?

OK, this brings up an interesting situation... and will make an excellent video. In a nutshell, here's what's going on:

If you're using FirstName, LastName, and Suffix as part of a composite key, you might notice that entering "Joe Smith" with no suffix allows duplicates. The issue is that two NULL values are not considered equal. NULL means "unknown," and in Access (and most databases), two unknown values are never treated as equal.

The "Ignore Nulls" setting only controls whether rows with NULL values in these fields are included in the index. If you leave "Ignore Nulls" off, those rows will still be indexed, but the comparison problem with NULL values remains.

The Fix: Make your fields required so they can't accept NULL values. Also, set the default value of the fields in the index to an empty string (""). Two empty strings are considered equal, so this will ensure your composite key works as expected. It's just a quirk of how Access handles NULL values.

Of course, if you've got data in the table already, you'll need to use an update query to change all the NULL values to "" but that's not too hard. I'll put this on my list for a video. Great question.

This works Fine except I notice that capitalization does make a difference. Large case and small case are not equal.

Not as far as I can tell, table data should be case insensitive. I know that, at the module level and in some functions, you can specify not being case sensitive, for example, using Option Compare Binary. But the data in the tables by itself should not be case sensitive when it comes to indexing. I looked, and I didn't see any properties in the file options menu to change that for the database as a whole, so that shouldn't be happening. As you can see from the following screenshot, I just tested it with a first name and last name, and if I set up an index that's unique, it still catches it even if I don't capitalize one of them.

I don't know why but mine is case sensitive see my screenshot

But didn't you also have MiddleName and Suffix as part of the Index?

Yes but I have set the default values for these to " ", as Richard has suggested above

It looks like there's a space in front of " richard"

The default value needs to be "" empty string not " " with a space.

I have run into another problem with this subject. Everything you have helped with so far works fine. But when editing a record, say removing a middle name. Now it seems I am missing the "" Default value how do i insert this into the record with out retyping it all over?

On editing a single record, you can type "" into the field for an empty string. What I would do is make an After Update event that says something like:

If IsNull(MiddleName) then MiddleName=""

Richard, I suppose that this would apply to the other name fields as well although the first name should always have data in it. The after update event goes in the form events I suppose.

Correct. After Update. You'll need one for each text box.

Thanks!