duplicate prevention in form

[chynewalker]

Hi,

I have a slight problem with my code, It is to prevent a number from being
entered multiple times, but occasionally i want a duplicate.
So i wrote this code to try to find the duplicate and prompt me to allow it
or not.
however it says every number exists already. what wrong?? help

thanks

john
Private Sub OEM___BeforeUpdate(Cancel As Integer)

If DCount("[OEM___]", _
"[drawings1]", _
"[OEM___] = '" & Me.OEM___ & "'") Then
If MsgBox("This OEM already exists!!!" & vbCrLf & "Add it anyway?", vbYesNo)
= vbNo Then
Cancel = True
Else 'Do nothing
End If
End If
End Sub

 

[Linq Adams via AccessMonster.com]

I can't reproduce your problem. When I first set it up, copying and pasting
your posted code, it did just the opposite; it didn't pop the messagebox
whether the number existed or not!

That was because your control is named

OEM___

OEM plus 3 underscores (a very strange name, BTW)

The problem is that a BeforeUpdate event name is in the format

ControlName_BeforeUpdate

Your sub header is

Private Sub OEM___BeforeUpdate(Cancel As Integer)

but should be

Private Sub OEM____BeforeUpdate(Cancel As Integer)

You were missing the fourth underscore. Once I corrected this your code ran
exactly as it should! Popping a message if and only if the number was a
duplicate.

 

[chynewalker]

i am still not able to get it to function using both methods.
I understand the names are kinda dumb but i didnt design this so I dont have
a say.

I think the problem stems from the fact that The control source is really
named OEM #

That is with a space between the oem and #. , Access didnt like that so i
replaced the space and # with the underscores.

 

[Daryl S]

Chynewalker -

If your control source is really named OEM #, then change that control name
to OEM___ to match what is in your code.