Probably simple query issue

[poporacer]

I have a form to enter new names into a table (tblInmates). I want to check if it has already been entered into the table. The data is input into a textbox. I have tried several different methods. I have a similiar one that works but for some reason it does not work on this form. Here is the code:

Private Sub txtCDCNum_AfterUpdate()
Dim reply As String

' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone

txtCDCNum = StrConv(txtCDCNum, vbUpperCase)

rs.FindFirst "[ID] = " & Str(Nz(Me![txtCDCNum], 0))
If rs.EOF Then

reply = MsgBox("This CDC Number already exists", vbOKOnly, "Duplicate CDC numbers")
txtCDCNum.SetFocus

End If

I get a Type mismatch on the rs.findfirst line...I have the exact same code in another form that works...I am sure I am not seeing something.

 

[DCrake]

Simple Software Solutions

Strange your test for duplicates seems to be transposed....

If rs.EOF Then

reply = MsgBox("This CDC Number already exists", vbOKOnly, "Duplicate CDC numbers")
txtCDCNum.SetFocus

End If

surely if it is EOF then it has not found one

You may actually be better doing a DLookup to check

CodeMaster:

 

[poporacer]

Yes it was late, I did transpose the message box (or cold have used NOT rs.EOF) any suggestions on why the Error....the SQL would be similiar for Dlookup. I will work on trying the Dlookup a little later.
Thanks