check the value of a text box against all other records in a field

[Blitz]

I have a unbound textbox on a form. When a button is clicked I need it to check the value of that textbox against all of the records in a certain field and return an error(message box saying that value has already been used) if it matches and of the records. how can I do this?

 

[Eradic8or]

Private Sub checknumber()
Dim wrk As Workspace
Dim dbs As Database
Dim rst As Recordset
Set wrk = DBEngine(0)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(&quot;<tablename>&quot;, dbOpenTable)
wrk.BeginTrans

Do Until rst.EOF = True
If rst(&quot;<fieldname>&quot = textbox.Value Then
MsgBox &quot;This number has been used already. &quot;
Else
End If
rst.MoveNext
Loop
End Sub

Hope this helps.
Eradic8or

 

[Blitz]

Thanks, I had to delete the dbOpenTable cause I was getting an error (not sure why) but once deleted it worked fine.