Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

VBA Displaying Matching Recordset

Discussion in 'Information Technology' started by pechoi@syr.edu, Jul 28, 2006.

  1. pechoi@syr.edu

    pechoi@syr.edu
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Heritage Canadian art gallery displaying Sikh Maharaja Ranjit Singh’s throne History of Sikhism Nov 27, 2010

  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    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
    __________________________________________

    "pechoi@syr.edu" wrote:

    > 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
     
  4. pechoi@syr.edu

    pechoi@syr.edu
    Expand Collapse
    Guest

    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?)
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

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

    "pechoi@syr.edu" wrote:

    > 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?)
     
  6. pechoi@syr.edu

    pechoi@syr.edu
    Expand Collapse
    Guest

    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?
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    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
    __________________________________________


    "pechoi@syr.edu" wrote:

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

Share This Page