Results 1 to 9 of 9
  1. #1
    Join Date
    May 2008
    Posts
    6

    Unanswered: OpenRecordset and Query

    Hi!
    I try to count the line number in a query.
    I write the VBA following code and i always receive an error code on instruction:
    Mareq = MaBD.OpenRecordset("GAMME_OPE")
    GAMME_OPE is a query name
    Why? How to count the line number in a query ???

    Public Function NBCODEOPE()

    Dim MaBD As Database
    Dim Mareq As Recordset
    Dim Enrg As Integer

    Set MaBD = CurrentDb()
    Mareq = MaBD.OpenRecordset("GAMME_OPE")
    Enrg = Mareq.RecordCount
    Mareq.Close
    End Function

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    I think you should change your open recordset code to this:
    set Mareq = MaBD.OpenRecordset("GAMME_OPE")
    and immediately after that add :
    MaBD.MoveLast
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  3. #3
    Join Date
    May 2008
    Posts
    6
    Thank's Aran.
    But unfortunatly i receive always on:
    Set Mareq = MaBD.OpenRecordset("GAMME_OPE")
    the error code: 3061 with comment: Too few parameters 5required (????)

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    This is what I would do:

    Code:
    Public Function NBCODEOPE() as Integer
       Dim Mareq As Recordset
       Mareq = CurrentDb.OpenRecordset("SELECT IDField FROM GAMME_OPE;")
       Mareq.MoveLast
       NBCODEOPE = Mareq.RecordCount
       Mareq.Close
    End Function
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is GAMME_OPE a query that is using parameters to show its information?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    I think there is something wrong with your query, I tested the code with a sample query and it works fine.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i always receive an error code
    could it be useful to mention the content of the error message?

    could it be useful to mention the Access version?
    for example your code will ALWAYS error in as-installed Access-2000 because the default "recordset" object is an ADO object and you are using a DAO method. you can't open an ADO recordset with a DAO method

    recommendation: given that ADO and DAO both have a "recordset" object and you are using DAO methods, pleeeeeease declare it as
    Dim mareq As DAO.Recordset

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    could it be useful to mention the content of the error message?
    He did Izy...

    the error code: 3061 with comment: Too few parameters 5required (????)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well spotted!
    sorry - didn't notice it there.

    you should definitely add the explicit DAO declare, but there is nothing in the code to produce the parameters error. you need to look elsewhere - does the query GAMME_OP work? does it require parameters?

    if this was my function, it would look something like this:

    Public Function NBCODEOPE() as Long
    on error goto err_NBCODEOPE
    Dim Mareq As DAO.Recordset
    Mareq = currentdb.OpenRecordset("GAMME_OPE", dbopenforwardonly)
    mareq.movelast
    NBCODEOPE = mareq.recordcount
    exit_NBCODEOPE:
    on error resume next
    Mareq.Close
    set mareq = nothing
    exit function
    err_NBCODEOPE:
    msgbox err.number & ": " & err.description, vbexclamation, "Error in NBCODEOPE
    NBCODEOPE = -1 'impossible recordcount as error return
    resume exit_NBCODEOPE
    End Function

    but that does NOT address a parameters issue elsewhere.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •