Stopping duplicate entry in a field on a form

[coach32]

I have used the code below to try and bring up a message box when I enter a duplicate number in the 'TFN' field but it stops me at the 'If' line.

Private Sub TFN_AfterUpdate()
Dim NewTFN As String
Dim stLinkCriteria As String
NewTFN = Me.TFN.Value
stLinkCriteria = "[TFN] = " & "'" & NewTFN & "'"
If Me.TFN = DLookup("[TFN]", "tbl_SAlert", stLinkCriteria) Then
MsgBox "This TFN " & NewTFN & ", has already been entered in the database." _
& vbCr & vbCr & "Please check TFN again.", vbInformation, "Duplicate TFN entered"
Me.Undo
End If
End Sub

The table used with the form is titled "tbl_SAlert" and the field on the form is entitled "TFN".
Could someone please advise where I am going wrong?

[CJ_London]

if it stops at the if line it comes up with an error message - what is the error message?

And just to confirm (because I suspect this is where the problem is) what datatype is the field TFN in the table tbl_SAlert

[Micron]

I have to ask:
- why not just prevent this by setting table field properties to not allow dupes and forget the code?
- if sticking with the approach anyway, this sort of thing belongs in a BeforeUpdate event so it can be cancelled, not AfterUpdate. IMHO, it is better to not write to a table than write to it and undo the entry. Also, assigning the form control data to a variable (thereby having two string variables) then having the variable in the lookup function is a bit superfluous

Private Sub TFN_BeforeUpdate(Cancel As Integer)
If Me.TFN = DLookup("[TFN]", "tbl_SAlert", "[TFN]='" & [TFN] & "'" ) Then
  MsgBox "This TFN " & Me.TFN & ", has already been entered in the database." _
  & vbCr & vbCr & "Please check TFN again.", vbInformation, "Duplicate TFN entered"
  Cancel = True
End If

End Sub

It also appears that poster has controls and fields with the same name. This can cause issues when Access doesn't properly resolve which is being referred to. What usually causes this is building forms/reports with design wizards (which is fine) but not using conventional names to re-name these controls.

[coach32]

Sorry for the late response.
The error message is Run-time ERROR '3464': Data type mismatch in criteria expression

The datatype is 'Number'

I wanted the code because I don't want the operative entering in other data after the TFN field and then be told the number is a duplicate.

[CJ_London]

As I thought

this is the line casue the problem - you are trying to compare a text value to a numeric value

stLinkCriteria = "[TFN] = " & "'" & NewTFN & "'"

it should be

stLinkCriteria = "[TFN] = " & NewTFN

[coach32]

Thank you so much Ajax.

This has helped me so much.

[Micron]

I wanted the code because I don't want the operative entering in other data after the TFN field and then be told the number is a duplicate.

FYI: table or control level validation would accomplish the same thing without code.

[coach32]

Thank-you Micron. Appreciate the guidance.
That's why I ask you guys, you know your stuff.