VBA Displaying Matching Recordset

  • Thread starter Thread starter pechoi
  • Start date Start date
P

pechoi

Hi all,
I am a new at this and not sure how to displaying matching recordset. I
got:
Function test2()

Dim loDB As Database
Dim loRSPlayer As Recordset
Dim lnIndex As Integer
Dim lnCount As Integer
Dim lsCard As String

lsCard = "K"
Set loDB = CurrentDb

Set loRSPlayer = loDB.OpenRecordset("HandGroup")
' add
With loRSPlayer

.FindFirst "Card1 = """ & lsCard & """"

End With
End Function

I am getting "Error 3251: The Operation is not supported for this type
of object". I can add rec with .AddNew and .Update. I don't know what I
am doing wrong.
What am I doing wrong?

Thx
 
It sounds to me like you may have a reference priority issue going on, which
shows up as a run-time error, even though your code compiles properly. Try
changing this line of code:

From
Dim loRSPlayer As Recordset

To
Dim loRSPlayer As DAO.Recordset

If that solves the problem (or even if it doesn't), check out this article:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html

Do you have a reference set to the "Microsoft DAO 3.6 Object Library"? You
need to have this, since you are using DAO code. You should also include code
to close your recordset and set it to nothing at the end of your procedure,
to help prevent DB bloat.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
It sounds to me like you may have a reference priority issue going on, which
shows up as a run-time error, even though your code compiles properly. Try
changing this line of code:

From
Dim loRSPlayer As Recordset

To
Dim loRSPlayer As DAO.Recordset

If that solves the problem (or even if it doesn't), check out this article:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html

Do you have a reference set to the "Microsoft DAO 3.6 Object Library"? You
need to have this, since you are using DAO code. You should also include code
to close your recordset and set it to nothing at the end of your procedure,
to help prevent DB bloat.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Thx for reply!
However, that didn't work. I changed to


Dim loDB As DAO.Database
Dim loRSPlayer As DAO.Recordset

Still getting same error. I checked reference and "Microsoft DAO 3.6
Object Library" is my third lib ( VBA object and Access object 11 is
before that. My DB does said Access 2000 format file do you know why?)
 
Thx for reply!
However, that didn't work. I changed to


Dim loDB As DAO.Database
Dim loRSPlayer As DAO.Recordset

Still getting same error. I checked reference and "Microsoft DAO 3.6
Object Library" is my third lib ( VBA object and Access object 11 is
before that. My DB does said Access 2000 format file do you know why?)
 
My DB does said Access 2000 format file do you know why?

Sure. The Access 2000 file format is the default file format for both Access
2002 and 2003. This should not be a problem, unless you specifically need to
use new functionality that has been introduced in these later versions. If
you want to eventually convert to the .mde file format, then you'll need to
do so either on a machine that has Access 2000 installed, or you'll need to
convert your database to the 2002-2003 file format. However, during
development, I recommend leaving it in the 2000 file format. Here's why I say
this:

Database bloat is not stopped by compacting database with Access 2002 format
http://support.microsoft.com/?id=810415

Upon further investigation, I believe you are getting Run-time error '3251'
because
"HandGroup" is a table, not a query. You have two possible solutions:

1.) Add the optional parameter to specify the type of recordset, ie.
Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenDynaset)
or Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenSnapshot)

2.) Create a query that is based on Handgroup, and then specify this query
in the Set statement:

Set loRSPlayer = loDB.OpenRecordset("qryHandGroup")

If you look in Access VBA Help, you will find the following clues:

For FindFirst, FindLast, FindNext, FindPrevious:
Locates the first, last, next, or previous record in a dynaset- or
snapshot-type Recordset object that satisfies the specified criteria and
makes that record the current record (Microsoft Jet workspaces only).

and for OpenRecordset Method:
If you open a Recordset in a Microsoft Jet workspace and you don't specify a
type, OpenRecordset creates a table-type Recordset, if possible. If you
specify a linked table or query, OpenRecordset creates a dynaset-type
Recordset.

You are apparently getting a table-type Recordset, by default, which does
not work with the Find methods.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
My DB does said Access 2000 format file do you know why?

Sure. The Access 2000 file format is the default file format for both Access
2002 and 2003. This should not be a problem, unless you specifically need to
use new functionality that has been introduced in these later versions. If
you want to eventually convert to the .mde file format, then you'll need to
do so either on a machine that has Access 2000 installed, or you'll need to
convert your database to the 2002-2003 file format. However, during
development, I recommend leaving it in the 2000 file format. Here's why I say
this:

Database bloat is not stopped by compacting database with Access 2002 format
http://support.microsoft.com/?id=810415

Upon further investigation, I believe you are getting Run-time error '3251'
because
"HandGroup" is a table, not a query. You have two possible solutions:

1.) Add the optional parameter to specify the type of recordset, ie.
Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenDynaset)
or Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenSnapshot)

2.) Create a query that is based on Handgroup, and then specify this query
in the Set statement:

Set loRSPlayer = loDB.OpenRecordset("qryHandGroup")

If you look in Access VBA Help, you will find the following clues:

For FindFirst, FindLast, FindNext, FindPrevious:
Locates the first, last, next, or previous record in a dynaset- or
snapshot-type Recordset object that satisfies the specified criteria and
makes that record the current record (Microsoft Jet workspaces only).

and for OpenRecordset Method:
If you open a Recordset in a Microsoft Jet workspace and you don't specify a
type, OpenRecordset creates a table-type Recordset, if possible. If you
specify a linked table or query, OpenRecordset creates a dynaset-type
Recordset.

You are apparently getting a table-type Recordset, by default, which does
not work with the Find methods.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Thx!
One more question:
Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenDynaset)
' add
With loRSPlayer
While Not .EOF
.FindNext "Card1 = """ & lsCard & """"
MsgBox !Group & " " & !Card2
Wend
End With

This loop never end. Looks like EOF is for the table not collected from
"FindNext". How can I do this?
 
Thx!
One more question:
Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenDynaset)
' add
With loRSPlayer
While Not .EOF
.FindNext "Card1 = """ & lsCard & """"
MsgBox !Group & " " & !Card2
Wend
End With

This loop never end. Looks like EOF is for the table not collected from
"FindNext". How can I do this?
 
Try this:


Function TestFindNext()
On Error GoTo ProcError

Dim loDB As DAO.Database
Dim loRSPlayer As DAO.Recordset
Dim lnIndex As Integer
Dim lnCount As Integer
Dim lsCard As String

Set loDB = CurrentDb()

lsCard = "K"

Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenDynaset)

With loRSPlayer

While Not (.BOF Or .EOF) = True
.FindNext "Card1 = """ & lsCard & """"
MsgBox !Group & " " & !Card2
.MoveNext
Wend

End With

ExitProc:
'Cleanup
loRSPlayer.Close: Set loRSPlayer = Nothing
loDB.Close: Set loDB = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure TestFindNext..."
Resume ExitProc

End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Try this:


Function TestFindNext()
On Error GoTo ProcError

Dim loDB As DAO.Database
Dim loRSPlayer As DAO.Recordset
Dim lnIndex As Integer
Dim lnCount As Integer
Dim lsCard As String

Set loDB = CurrentDb()

lsCard = "K"

Set loRSPlayer = loDB.OpenRecordset("HandGroup", dbOpenDynaset)

With loRSPlayer

While Not (.BOF Or .EOF) = True
.FindNext "Card1 = """ & lsCard & """"
MsgBox !Group & " " & !Card2
.MoveNext
Wend

End With

ExitProc:
'Cleanup
loRSPlayer.Close: Set loRSPlayer = Nothing
loDB.Close: Set loDB = Nothing
Exit Function
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure TestFindNext..."
Resume ExitProc

End Function



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Back
Top