No Duplicates Allowed

[pandaw1 via AccessMonster.com]

Hi,

I'm sure there is a really simple answer to this... (at least I hope so)

I have some fields in a form that must be unique. I have done this on the
table -no duplicates allowed, so that means it doesn't save if you try to
enter a duplicate in the form.

But I want an error message to come up for the user so that they know it
won't be saved because it's a duplicate.

How do I do this?

Many Thanks.

 

[Jeanette Cunningham]

Hi pandaw1,
here is an answer by Allen Browne some time ago. It covers 2 fields which
could be duplicates, you could modify this to check more fields.

Prevent the error by looking for a dupe in Form_BeforeUpdate.

Example, assuming 2 numeric fields "Field1" and "Field2", plus a
primary key named "ID":


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant


If IsNull(Me.Field1) Or IsNull(Me.Field2) Then
Cancel = True
MsgBox "Field1 and Field2 are required."
Else
strWhere = "(Field1 = " & Me.Field1 & _
") AND (Field2 = " & Me.Field2 & ")"
'Existing record isn't a dupe of itself.
If Not Me.NewRecord Then
strWhere = strWhere & " AND (ID <> " & Me.ID & ")"
End If
varResult = DLookup("ID", "MyTable", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "ID " & varResult & " has this combination."
End If
End If
End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia