Check for duplicate number before saving record

[Broil]

How do I check a text field on a form against records in the table? I'm trying to determine as this first field is entered that it does NOT already exist in the table. If it DOES exist, I will use the msgbox "This number already exists. Try again...", clear the field and have the user enter another number.

 

[ide]

is it an Excel table or a database table (fe Access)?

if an excel table:

i have two textbox (TextBox1, TextBox2) and a label (Label1) on a userform. The BeforeUpdate event of the TextBox1:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim rngFndRange As Range, d As Object
Set rngFndRange = ThisWorkbook.ActiveSheet.Range("A:A&quot

Set d = rngFndRange.Find(What:=TextBox1, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False)
If Not (d Is Nothing) Then
Me.Label1 = "Value is in the column A, yet, pls. change the value"
Cancel = True
SendKeys "{ESC}"
Else
Me.Label1 = ""
End If
End Sub

if in the column A is the value that typed, the focus remains on the textbox1....