check if duplicate OK

[janith]

Hi,

I'm looking for the before update VBA code which will ensure that when a value is entered into that field. It checks the table and returns message "this is a duplicate entry would you still like to continue" if no it should erase all entries. If yes it should continue updating from that field.. Any help!!!

 

[jdraw]

What are the conditions that would want you to store a duplicate?

You can find duplicates by means of the DCount() function.

eg If DCount( your criteria) >0 Then
'do your this is a duplicate routine
Elseif DCount( your criteria) = 0 Then
'not a duplicate routine goes here
End If


You can prevent the storage of duplicates by indexing with no duplicates/unique index.

You can also check for Err.number = 3022 which indicates an attempt to add a duplicate.

Good luck with your project

 

[janith]

What are the conditions that would want you to store a duplicate?

You can find duplicates by means of the DCount() function.

eg If DCount( your criteria) >0 Then
'do your this is a duplicate routine
Elseif DCount( your criteria) = 0 Then
'not a duplicate routine goes here
End If


You can prevent the storage of duplicates by indexing with no duplicates/unique index.

You can also check for Err.number = 3022 which indicates an attempt to add a duplicate.

Good luck with your project

There is only one condition that of the number entered in the field already exists in the table it should ask this is duplicate but by clicked do you want to continue it should take the new number.

 

[arnelgp]

Private sub yourcontrol_BeforeUpdate(Cancel as Integer)

Dim intCount as integer

intcount = Nz(DCount("*","theTableYouWantToCheck","theFieldYouWantToCheck = " & yourControl), 0)

If intCount <> 0 then
If MsgBox("This is a duplicate entry would you still like to continue?", vbQuestion + vbYesNo, "Duplicate Entry") = vbNo Then
'undo all changes made to all fields
Me.Undo
'undo only this control
'Me.yourControl.Undo
Cancel = True
End If
End if
End Sub

 

[janith]

Will it take care of vbyes also.. ie; to continue with update even if it's duplicate?

 

[arnelgp]

i only tested if the user select No on the Msgbox, so if you responded with a Yes, it will continue even with duplicate, not unless you have bound textbox control which have an index key that does not allow duplicate (Primary: Yes, Or Unique: Yes).

 

[janith]

Great thanks for this.. Will get back soon