Why Type Mismatch

[RICHARD BROMBERG]

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_Afte rUpdate(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]

Re: Why Type Mismatch

On Sun, 23 Jul 2006 15:52:50 GMT, "RICHARD BROMBERG" <noreply@att.ne twrote:

>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_Afte rUpdate(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.co m

https://www.infotrakker.com

[RICHARD BROMBERG]

Re: Why Type Mismatch

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_I nfotrakker.comw rote in message
news:kq97c2t65e 5if9msi1air6m7g mbhhnj6vl@4ax.c om...

On Sun, 23 Jul 2006 15:52:50 GMT, "RICHARD BROMBERG" <noreply@att.ne t>

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_Afte rUpdate(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.co m
www.infotrakker.com

[John Mishefske]

Re: Why Type Mismatch

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
'---------------

[David W. Fenton]

Re: Why Type Mismatch

John Mishefske <jmishefskeNO@S PAMyahoo.comwro te in
news:auVwg.6338 $D%4.4844@torna do.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/

[RICHARD BROMBERG]

Re: Why Type Mismatch

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" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns9809E48 BC1909f99a49ed1 d0c49c5bbb2@127 .0.0.1...

John Mishefske <jmishefskeNO@S PAMyahoo.comwro te in
news:auVwg.6338 $D%4.4844@torna do.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/