By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,292 Members | 1,758 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,292 IT Pros & Developers. It's quick & easy.

before update - validating potential new data?

P: n/a
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.Connection
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, adOpenForwardOnly, adLockReadOnly
If Not Rst.EOF Then
response = MsgBox("Duplicate 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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
never mind
i got it
here is the correct version, maybe somebody can use it

On Error GoTo ErrorHandler
Dim Cnn As ADODB.Connection
Dim Rst As ADODB.Recordset
Set Cnn = CurrentProject.Connection
Set Rst = New ADODB.Recordset
Dim response As Integer
If (Me.LeBayNUM <> Me.LeBayNUM.OldValue) Or
IsNull(Me.LeBayNUM.OldValue) Then
Rst.Source = "select * from tbllisteditems where [LeBayNUM]= """ &
Me!LeBayNUM & """"
Rst.Open , Cnn, adOpenForwardOnly, adLockReadOnly, adCmdText
If Not Rst.EOF Then
response = MsgBox("Duplicate 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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.