Searching for a matching record using .EOF, ADO

[rhinomac]

Please help,
Im new to this but im trying hard.
Ive looked at numerous resources but couldn't seem to get it to work. I have an Access database where Im trying to search a table(servicecenterproblem) for a matching !me form entry(number) in the field (number).
If theres a match then msg "dup entry", if no match then add.number to field.
Here is my sub code.


Private Sub runtest_Click()
If IsNull(Me!number) Then
MsgBox "Service Number is a Required Entry.", 48
Me!number.SetFocus
Else
'**** add a new record ****
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic


rst.Open "ServiceCenterProblem",CurConn, , ,adCmdTableDirect

rst.Find ("number = '[ME!number]'&quot, MoveFirst, adSearchForward


If Not rst.EOF Then
MsgBox "Service Number is a Duplicate Entry.", 48
Me!number.SetFocus

Else
With rst
.AddNew
![number] = (Me!number)
.Update
MsgBox Me!number & " has been added to the Service Center Table."
End With
End If
Set rst = Nothing
Set cnn = Nothing

Exit Sub

End If

End Sub

 

[vbajock]

Is "number" a key field in the table?
What is the line of code that errors out and what is the error?

 

[rhinomac]

Thanks for the quick reply vbajock
-number is a key field
-it doesnt error, it just always goes to

Else
With rst
.AddNew
![number] = (Me!number)
.Update
MsgBox Me!number & " has been added to the Service Center Table."
even if theres a duplicate entry - then promting the error that the entry cant be added because its a duplicate entry.
i want to trap that error and have my own message box
(MsgBox "Service Number is a Duplicate Entry.", 48)
Thanks a lot,

p.s ive also tried to use the seek command with the primary key and check value of .eof as well and would do the same thing.

 

[vbajock]

With ADO, you really should be using the Seek method:

dim somenumber

'this is all on one line
rst.Open "ServiceCenterProblem", CurConn, adOpenDynamic,adLockOptimistic, adCmdTableDirect

rst.Index = "PrimaryKey" ' or the name of the index if it 'is not primary

somenumber = ME!number

rst.MoveFirst
rst.Seek somenumber

If Not rst.EOF Then
'number exists, send msg
else
'do something else
end if

 

[vbajock]

BTW - the simple way to do this would be:

if isnull(Dlookup("number","ServiceCenterProblem","number = '"+[ME!number]+"'&quot) then


'dlookup returned null, the number is not in the file


else

'it is not null, I have the number on file already

end if
I am assuming your field "number" is a text type field. If it is numerical, leave out the single quotes in the where clause

 

[rhinomac]

if isnull(Dlookup("number","ServiceCenterProblem","number = '"+[ME!number]+"'&quot)
This line is returning a runtime error 2465
hotswap database cant find the field '|' reffered to in your expression

 

[vbajock]

Dlookup("[number]","ServiceCenterProblem","[number] = '"+[ME!number]+"'&quot)
Put number in brackets - sorry

 

[vbajock]

Also - [Me!number] should not be in brackets

 

[rhinomac]

You the man, works like a charm. ive been spending days on this. Thanks once again
-Rhino

 

[vbajock]

Let this be the lesson, grasshopper:

FIND is for dweebs
SEEK is cool
Dlookup will save you a lot of coding on forms if you now how to use it. Another function like it is IIF, you might want to look at it too.

 

[scking]

Irregardless of whether you should be using seek you should also be properly specifying the search strings.

Change: rst.Find ("number = '[ME!number]'&quot, MoveFirst, adSearchForward

To: rst.Find ("number = '" & [ME!number] & "'&quot, MoveFirst, adSearchForward
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------