Preventing duplicate entries...

[graccess]

I have a form where the field, [txtTicker] would be the primary method of searching within my form as well as editing a record (not to be confused with primary key). I've tried using the DLookup function to take the entered field in my form, txtTicker, and compare it to my table, [Companytable] with the respective field, [Ticker], but my syntax and design are probably both incorrect. I'm placing the code in my form within a sub procedure that is taking all my data entered and inserting it into my table. I'd like to prompt myself prior to executing the SQL code that the primary entry txtTicker already exists and if so do I wish to overwrite the data or cancel the action. It's possible I should be writing a query or two to perform these steps but I'm getting bogged down a bit. Any suggestions would be most helpful.



'prevent dupes by checking my table and alert


If IsNull(DLookup("[Ticker]", _
"[Companytable]", _
"[Ticker]='" & Me.txtTicker & "'")) Then
MsgBox "Duplicate!"


End If

So, this is returning "Duplicate" only when it's an original. Not what i'm looking for naturally. I tried using NotIsNull but that had errors. Thanks in advance!

[June7]

Need a space between Not and IsNull: Not IsNull(...

[graccess]

Ah Thanks. Silly error.

Another question on this. This code will be in the beginning of a subroutine which will add the inputted field information. For now, this code fires a msgbox, but I also want it to abort the add data procedure. How would you code for that?

[June7]

Could test for the duplication and only continue procedure if not duplicate

Sub something()
If Not IsNull(...) Then
MsgBox "Duplicate. Procedure terminated."
Else
'other code
End If
End Sub

[graccess]

Sweet, that worked great. thanks again for your help!