Error Stopping Duplicate Entries

[afr0ninja]

Hello! I'm still rather new to using code in access. I've come across
a problem that I'm having difficulty troubleshooting. Any assistance
would be greatly appreciated!

I've set up a form where an order number is entered. I have entered
code where if an order number is entered that is a duplicate, a message
box will pop up and tell you so that you can change the number. The
code works perfect for telling you if there's a duplicate or not.

The problem I'm having is that if someone mistakenly types in
information they didn't mean to go to that field and try to delete it
out it bombs. I get an error that says Run-time error '-2147217900
(90040e14)' Syntax error (missing operator) in query expression
'OrderNo='. Then asks me to end or debug. I'll paste the code below.
If anyone has any suggestions or ideas I'd appreciate it!

Private Sub txtOrderNo_BeforeUpdate(Cancel As Integer)

Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM tblInbDiscrepancyLog WHERE OrderNo=" &
txtOrderNo, CurrentProject.Connection, adOpenStatic, adLockReadOnly

If rs.RecordCount 0 Then

MsgBox "Warning Order Number " _
& Me.txtOrderNo.Value & " has already been entered." _
& vbCr & vbCr & "Please enter a different order
number.", vbInformation _
, "Duplicate Information"

End If

End Sub

[RoyVidar]

"afr0ninja" <mo**************@gmail.comwrote in message
<11********************@m73g2000cwd.googlegroups.c om>:

Hello! I'm still rather new to using code in access. I've come
across a problem that I'm having difficulty troubleshooting. Any
assistance would be greatly appreciated!

I've set up a form where an order number is entered. I have entered
code where if an order number is entered that is a duplicate, a
message box will pop up and tell you so that you can change the
number. The code works perfect for telling you if there's a
duplicate or not.

The problem I'm having is that if someone mistakenly types in
information they didn't mean to go to that field and try to delete it
out it bombs. I get an error that says Run-time error '-2147217900
(90040e14)' Syntax error (missing operator) in query expression
'OrderNo='. Then asks me to end or debug. I'll paste the code
below. If anyone has any suggestions or ideas I'd appreciate it!

Private Sub txtOrderNo_BeforeUpdate(Cancel As Integer)

Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM tblInbDiscrepancyLog WHERE OrderNo=" &
txtOrderNo, CurrentProject.Connection, adOpenStatic, adLockReadOnly

If rs.RecordCount 0 Then

MsgBox "Warning Order Number " _
& Me.txtOrderNo.Value & " has already been entered." _
& vbCr & vbCr & "Please enter a different order
number.", vbInformation _
, "Duplicate Information"

End If

End Sub

I think I'd try to test for content first, and perhaps if it's numeric

if len(me!txtOrderNo.Value & vbNullString) then
if me!txtOrderNo.Value like _
string(len(me!txtOrderNo.Value), "#") then
' do your testing here
else
' not numeric
end if
else
' no contents, no testing?
end if

I'm usually more inclined to do tests in (or also in) the before
update event of the form, which is triggered whenever an attempt
to save is made.

Around these NGs you'll probably be adviced to use DAO on Jet tables,
but if you're using ADO, I'm very reluctant to rely upon the
..RecordCount property, and would rather use the following construct
to test wether the recordset contained records or not

if ((not rs.bof) and (not rs.eof)) then
' the recordset contains records...
end if

--
Roy-Vidar

[afr0ninja]

Hello! Thank you for your assistance!

I am having some issues with what you've suggested. If I use the code
you've sugested it processes the information without error, however it
no longer alerts you when you have a duplicate entry as my origional
code did.

The text number is numeric. And the code I origionally had works
almost flawlessly. The only issue I was having with it is that if
someone starts to type in an order number and they didn't mean to enter
it into that form, once they delete the mistaken order number out it
bombs.

Any other suggestions or advice would be greatly appreciated.

[RoyVidar]

"afr0ninja" <mo**************@gmail.comwrote in message
<11**********************@f16g2000cwb.googlegroups .com>:

Hello! Thank you for your assistance!

I am having some issues with what you've suggested. If I use the
code you've sugested it processes the information without error,
however it no longer alerts you when you have a duplicate entry as my
origional code did.

The text number is numeric. And the code I origionally had works
almost flawlessly. The only issue I was having with it is that if
someone starts to type in an order number and they didn't mean to
enter it into that form, once they delete the mistaken order number
out it bombs.

Any other suggestions or advice would be greatly appreciated.

If you post your current code for the event, someone might assist.

--
Roy-Vidar

[afr0ninja]

Well.. Since reposting I've done some more messing around and I think I
got it figured out. Below is what worked. Thank you very much for
your help!

Private Sub txtOrderNo_BeforeUpdate(Cancel As Integer)

If Len(Me!txtOrderNo.Value & vbNullString) Then
If Me!txtOrderNo.Value Like _
String(Len(Me!txtOrderNo.Value), "#") Then

Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM tblInbTracking WHERE OrderNo=" & txtOrderNo,
CurrentProject.Connection, adOpenStatic, adLockReadOnly

If rs.RecordCount 0 Then

MsgBox "Warning Order Number " _
& Me.txtOrderNo.Value & " has already been entered." _
& vbCr & vbCr & "Please enter a different order
number.", vbInformation _
, "Duplicate Information"

End If

Else

' not numeric

End If

Else

' no contents, no testing?

End If

End Sub