Code for Stop Duplication

[Malik via AccessMonster.com]

Hi,
I have a Sale Id Field which is A primary key. I Know That a primary Key can
not be Duplicated But I want that If User enter a Duplicat value , then a
MsgBox Appears with some Text.

Thanks

 

[Jeanette Cunningham]

Hi Malik,
Is this an auto number primary key and some other field could have a
duplicate?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[malik via AccessMonster.com]

Hi Malik,
Is this an auto number primary key and some other field could have a
duplicate?

Hi
Its Numeric Value Field.

 

[Jeanette Cunningham]

Does the user type in the SaleID?

If that is the case, you can put code in the before update event of the
control.

Something like this--> (untested air code)

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
Dim lngCount as Long
If Len(Me.SaleID) >0 Then
If Me.NewRecord = True Or Me.SaleID.Text <> Me.SaleID.OldValue Then
lngCount = DCount("*", "NameOfTable", "[SaleID] = " & Me.SaleID.Text)
If lngCount >0 Then
MsgBox "Duplicate"
Cancel = True
End If
End If
End Sub

You may choose to put this code in the before update event for the form
instead of the control.
In this case change the code above by changing Me.SaleID.Text to Me.SaleID
in both places where it is used.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[malik via AccessMonster.com]

Does the user type in the SaleID?

If that is the case, you can put code in the before update event of the
control.

Something like this--> (untested air code)

Private Sub ControlName_BeforeUpdate(Cancel As Integer)
Dim lngCount as Long
If Len(Me.SaleID) >0 Then
If Me.NewRecord = True Or Me.SaleID.Text <> Me.SaleID.OldValue Then

This Code has Some Problem. It Show Error with Me.SaleId.OldValue ( Here
Old Value Is Highlited)

Thanks

 

[Jeanette Cunningham]

The error will show when there is a null for old value - when on a new
record.

Use Nz(Me.SaleID.OldValue ,0)

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia