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

Why Type Mismatch

P: n/a
I am using Access 2000
..
I have a table called tblMembers with two fields
MemID (Primary key indexed no duplicates)
MemName (Thirty character text field)

and I have a text box (txtLookFor)

I want to find the record in the table that matches the number entered in
the text box.
If there is no match then a simple msgbox warning is sufficient.

I get a "Type Mismatch Error" on the Set rs = Me.Recordset statement.
Can anyone tell me what is wrong.
Here is the entire code.

Thanks

Private Sub txtLookFor_AfterUpdate(Cancel As Integer)
Dim rs As Recordset
Set rs = Me.Recordset
rs.Seek txtLookFor, adSeekFirstEQ
If rs.EOF Then
MsgBox ("Not Found")
Else
MsgBox ("found")
End If
End Sub


Jul 23 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Sun, 23 Jul 2006 15:52:50 GMT, "RICHARD BROMBERG" <no*****@att.netwrote:
>I am using Access 2000
.
I have a table called tblMembers with two fields
MemID (Primary key indexed no duplicates)
MemName (Thirty character text field)

and I have a text box (txtLookFor)

I want to find the record in the table that matches the number entered in
the text box.
If there is no match then a simple msgbox warning is sufficient.

I get a "Type Mismatch Error" on the Set rs = Me.Recordset statement.
Can anyone tell me what is wrong.
Here is the entire code.

Thanks
Try declaring yoru recordset explicitly:

Dim rs As DAO.Recordset

You may need to make a reference to the Microsoft DAO Library 3.6 - press Alt + F11 to open the VB Editor, then click
Tools - References, then find and Check the correct library.
>
Private Sub txtLookFor_AfterUpdate(Cancel As Integer)
Dim rs As Recordset
Set rs = Me.Recordset
rs.Seek txtLookFor, adSeekFirstEQ
If rs.EOF Then
MsgBox ("Not Found")
Else
MsgBox ("found")
End If
End Sub


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
Jul 23 '06 #2

P: n/a
Thanks for the fast reply

I made the changes you suggested, specifically,
I changed Dim rs As Recordset to Dim rs As DAO.Recordset
and added the reference Microsoft DAO 3.51 Object Library

Whan I ran the code I got this error :
"Operation is not supported for this type of object"

on the following statement:
rs.Seek txtLookFor, adSeekFirstEQ

Still puzzled
"Scott McDaniel" <scott@NoSpam_Infotrakker.comwrote in message
news:kq********************************@4ax.com...
On Sun, 23 Jul 2006 15:52:50 GMT, "RICHARD BROMBERG" <no*****@att.net>
wrote:
>
I am using Access 2000
.
I have a table called tblMembers with two fields
MemID (Primary key indexed no duplicates)
MemName (Thirty character text field)

and I have a text box (txtLookFor)

I want to find the record in the table that matches the number entered in
the text box.
If there is no match then a simple msgbox warning is sufficient.

I get a "Type Mismatch Error" on the Set rs = Me.Recordset
statement.
Can anyone tell me what is wrong.
Here is the entire code.

Thanks

Try declaring yoru recordset explicitly:

Dim rs As DAO.Recordset

You may need to make a reference to the Microsoft DAO Library 3.6 - press
Alt + F11 to open the VB Editor, then click
Tools - References, then find and Check the correct library.

Private Sub txtLookFor_AfterUpdate(Cancel As Integer)
Dim rs As Recordset
Set rs = Me.Recordset
rs.Seek txtLookFor, adSeekFirstEQ
If rs.EOF Then
MsgBox ("Not Found")
Else
MsgBox ("found")
End If
End Sub


Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

Jul 23 '06 #3

P: n/a
RICHARD BROMBERG wrote:
Thanks for the fast reply

I made the changes you suggested, specifically,
I changed Dim rs As Recordset to Dim rs As DAO.Recordset
and added the reference Microsoft DAO 3.51 Object Library

Whan I ran the code I got this error :
"Operation is not supported for this type of object"

on the following statement:
rs.Seek txtLookFor, adSeekFirstEQ
adSeekFirstEQ is a constant in the ADODB library and used for ADO recordsets.
You are using a DAO recordset now so your line need to look more like this:

rs.Seek "=", txtLookFor

DAO uses different parameters for the .Seek method.

Don't forget to set the current index with
rs.Index = <your index for the txtLookFor's bound field>

See the online help for .Seek for DAO Recordsets.

--
'---------------
'John Mishefske
'---------------
Jul 24 '06 #4

P: n/a
John Mishefske <jm**********@SPAMyahoo.comwrote in
news:au*****************@tornado.rdc-kc.rr.com:
RICHARD BROMBERG wrote:
>Thanks for the fast reply

I made the changes you suggested, specifically,
I changed Dim rs As Recordset to Dim rs As DAO.Recordset
and added the reference Microsoft DAO 3.51 Object Library

Whan I ran the code I got this error :
"Operation is not supported for this type of object"

on the following statement:
rs.Seek txtLookFor, adSeekFirstEQ

adSeekFirstEQ is a constant in the ADODB library and used for ADO
recordsets. You are using a DAO recordset now so your line need to
look more like this:

rs.Seek "=", txtLookFor

DAO uses different parameters for the .Seek method.

Don't forget to set the current index with
rs.Index = <your index for the txtLookFor's bound field>

See the online help for .Seek for DAO Recordsets.
And let me add the response I always make when someone brings up
SEEK:

If you're just looking for a single record, then for heaven's sake,
just use a WHERE clause to return the single record. Seek only
improves performance when you're jumping around a large recordset
collecting data for some purpose.

While Seek is many times faster than Findfirst, Findfirst can be
optimized if you're searching on the field that your recordset is
sorted by.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Jul 24 '06 #5

P: n/a
Thanks David

It looks like I may have been trying to shoot a mouse with an elephant gun.

I will try again.

I am somewhat of a novice with Access oand VB.
In an earlier life I was a programmer back when bits were flat. Can you say
machine language, asm, masm, Fortran, Cobol, dBase III ??

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
John Mishefske <jm**********@SPAMyahoo.comwrote in
news:au*****************@tornado.rdc-kc.rr.com:
RICHARD BROMBERG wrote:
Thanks for the fast reply

I made the changes you suggested, specifically,
I changed Dim rs As Recordset to Dim rs As DAO.Recordset
and added the reference Microsoft DAO 3.51 Object Library

Whan I ran the code I got this error :
"Operation is not supported for this type of object"

on the following statement:
rs.Seek txtLookFor, adSeekFirstEQ
adSeekFirstEQ is a constant in the ADODB library and used for ADO
recordsets. You are using a DAO recordset now so your line need to
look more like this:

rs.Seek "=", txtLookFor

DAO uses different parameters for the .Seek method.

Don't forget to set the current index with
rs.Index = <your index for the txtLookFor's bound field>

See the online help for .Seek for DAO Recordsets.

And let me add the response I always make when someone brings up
SEEK:

If you're just looking for a single record, then for heaven's sake,
just use a WHERE clause to return the single record. Seek only
improves performance when you're jumping around a large recordset
collecting data for some purpose.

While Seek is many times faster than Findfirst, Findfirst can be
optimized if you're searching on the field that your recordset is
sorted by.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Jul 24 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.