Duplicates in a column

[Tikafyre]

I have a column that needs to be set up so that there are no duplicates in the column. How would I do that without using a primary key?

Tika

 

[Jack Cowley]

I would put code in the Before Update event of the control to see that the input is not a duplicate. If it is then cancel the update and let the user try again.

hth,
Jack

 

[Tikafyre]

that sounds like a good idea, I'm not sure how to set that up though... Could you give a hint? LOL

Tika

 

[Jack Cowley]

As long as you don't have a bazillion records then this should do the trick (DLookup() is slow if there are a bazillion records):

If Not IsNull(DLookup("LastName", "tblCustomers", "[LastName] = '" & Me.[LastName] & "'")) Then
MsgBox "You have entered a duplicate value."
Cancel = True
Me.LastName.Undo
End If

Change the table, field and control names as necessary. And the syntax is for text so if the value is a date or a number then you will need to make the necessary changes to the Criteria part of the DLookup() function.

Jack

 

[directormac]

Jack -

Out of curiousity, why not just set the AllowDuplicates property to No in the table's design?

--Curious Mac

 

[Jack Cowley]

The reason that I do not use that approach is that Access pops up an error message that can be confusing to a user. If I can catch the error then I can put up a message that can be much more explicit and, hopefully, helpful. And I have more control over what happens after the 'error' occurs.

hth,
Jack

 

[directormac]

Excellent. Thanks!

--Always-Learning Mac

 

[Jack Cowley]

You're welcome!

Jack

 

[Pat Hartman]

It is really best to allow Jet to enforce the uniqueness rule. That way it doesn't matter how the table gets updated, the rule is always enforced. You can use the DLookup() in your form to check before a new row is appended to determine if an error exists or you can trap the error message raised if you insert a duplicate. I never check ahead of time because it is wasteful. I know it is only one table access but the vast majority of the time, there will be no duplicate. I prefer instead to trap the duplicate error that Jet returns.

 

[directormac]

So Pat, would you set the Index property to "Yes - NoDuplicates" in the table design and then trap the error at the FORM level? Would you do that in the control (for that field), in AfterUpdate event or it's ValidationRule? Or somewhere else?

-- Greenhorn Mac

 

[Pat Hartman]

You have to trap the error at the form level. Either in the on Error event or the BeforeUpdate event. I don't remember which. To get the error number you may have to temporarily set the form to Break on all errors.

 

[directormac]

Thanks Pat!

--Appreciative Mac

 

[nadoona]

Hi,
I've the same problem that "Tikafyre" was facing. I tried to use the code that "Jack Cowley" posted but the problem is that I'm a beginner for using MS access so I don't know how to edit the code to make it useful for me.

In the initial code, it checks that there is no duplicates in the "LastName" in tblCustomers.

In my table I want to do the same but for the OrderNo. But, I want to avoid the duplication in the OrderNo AND the OrderType together. I mean that the OrderNo can be repeated in the table, but not for the same OrderType.

I hope somebody can advise.
Thanks.