before update - validating potential new data?

[gbb0330]

Hi all

i have a textbox [LeBayNum] on a Sub Form [frmllisteditems subform]
bound to a table [tblListedItems]

i want to make sure there are no duplicate eBay numbers entered in the
textbox

so here is my code in the before update event procedure of the textbox
LeBayNUM

On Error GoTo errorhandler
Dim Cnn As ADODB.Connectio n
Dim Rst As ADODB.Recordset

Set Rst = New ADODB.Recordset
Dim response As Integer

If (Me![LeBayNUM] <> Me![LeBayNUM].OldValue) Or
IsNull(Me![LeBayNUM].OldValue)_ Then
Cnn.Open CurrentProject. Connection
Rst.Open "SELECT * from TblListedItems where [LeBayNUM]= """ &
Me!LeBayNUM &_ """", Cnn, adOpenForwardOn ly, adLockReadOnly
If Not Rst.EOF Then
response = MsgBox("Duplica te eBay Number!" & vbCrLf & "Do You want to
Cancel_ the Entry?", vbYesNo + vbCritical, "Duplicate eBay Number")
If response = vbYes Then
[LeBayNUM].Undo
End If
End If
Rst.Close
Set Rst = Nothing
End If
GoTo done
Set Rst = Nothing
GoTo done
errorhandler:
MsgBox Err.Description
done:
End Sub
the error message that i get every time the code is executed is
Object variable or With block variable not set
any help will be appreciated
thank you in advance

[gbb0330]

never mind
i got it
here is the correct version, maybe somebody can use it

On Error GoTo ErrorHandler
Dim Cnn As ADODB.Connectio n
Dim Rst As ADODB.Recordset
Set Cnn = CurrentProject. Connection
Set Rst = New ADODB.Recordset
Dim response As Integer
If (Me.LeBayNUM <> Me.LeBayNUM.Old Value) Or
IsNull(Me.LeBay NUM.OldValue) Then
Rst.Source = "select * from tbllisteditems where [LeBayNUM]= """ &
Me!LeBayNUM & """"
Rst.Open , Cnn, adOpenForwardOn ly, adLockReadOnly, adCmdText
If Not Rst.EOF Then
response = MsgBox("Duplica te eBay Number!" & vbCrLf & "Do you Want to
Cancel the Entry?" & vbCrLf & "If you Click Yes You Will Have to enter
a Different eBay Number" & vbCrLf & "If You Click No, a Duplicate eBay
Number Will be Saved", vbYesNo)
If response = vbYes Then
cancel = True
End If
End If
Rst.Close
End If

Set Rst = Nothing
GoTo done

ErrorHandler:
MsgBox Err.Description
done:
End Sub