Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    New York, NY USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OpenRecordset with a Query Having a parameter (All versions)

    I have a select query that has a parameter, to limit the results (e.g., ctrID = 1627). I have defined the parameter type. The query works fine. However, In VBA code, I cannot open a recordset using that query (e.g., Set rst = currentdb.openrecordset("qry150SE"). I get run-time error message 3061 "Too few parameters. Expected 1." I can open a non-parameterized query using exactly the same syntax, and so I assume that the problem is with the parameter. Has anyone run into this problem? Does anyone have any suggestions? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OpenRecordset with a Query Having a parameter (All versions)

    You'll always get that error unless you instantiate the querydef object first, pass it the parameters, and then use the querydef object to open the recordset instead of using the currentdb. BTW, you don't want to open a recordset using CurrentDb anyhow because it will bite you. Create either a database or a querydef object and open the recordset from that.
    Charlotte

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    New York, NY USA
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordset with a Query Having a parameter (All versions)

    Thanks for your response. Although I am doing some reasonably sophisticated things with VBA, I don't know how to do what you described. My query is qry150_SEOneStudy, and the parameter in the query is Forms!frmStudyMain![lngStudyID]. The parameter is on the Criteria row for the field lngStudyID in qry150_SEOneStudy.

    Can you give me a sample of code that will "instantiate the querydef object, pass it the parameters, and then use the querydef object to open the recordset ....?"

    Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OpenRecordset with a Query Having a parameter (All versions)

    Here's some code I borrowed years ago from a guy named Graeme Wilson and modified for your query name and to allow for Access 2000 multiple object references. The Eval function causes it to prompt you for each of the query's parameters just as it does when you run the query directly.

    <pre> Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter

    Set db = CurrentDb
    Set qdf = db.QueryDefs("qry150_SEOneStudy")
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset(dbOpenDynaset)

    rst.Close
    db.Close

    Set db = Nothing
    Set rst = Nothing
    Set qdf = Nothing
    Set prm = Nothing</pre>

    Charlotte

  5. #5
    New Lounger
    Join Date
    Oct 2001
    Location
    Perth, Western Australia
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordset with a Query Having a parameter (All versions)

    Thanks

    As we say in Australia, "You're a dead set legend!"

    Dean

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OpenRecordset with a Query Having a parameter (All versions)

    My goodness! I hope that's good. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Charlotte

Posting Permissions

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