Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Valentine, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Opening parameter queries in code (Access97/SP2)

    How do you open parameter query as a recordset?

    I am trying to set a query which gets its parameters from a search form where users can type in required values. When I either open the query manually or by using DoCmd.OpenQuery ("qryTestingCriteria") it opens fine. However, when I try to open a recordset based on this query by using OpenRecordSet statement it gives me the following error:

    ===========================
    Attached Files Attached Files

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

    Re: Opening parameter queries in code (Access97/SP2)

    You can't do it using DoCmd.OpenQuery. One way to do it using DAO (your second example) is like this:

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

    Set db = CurrentDb
    Set qdf = dbs.QueryDefs("qryTestingCriteria")
    Set prm = qdf.Parameters(0)
    prm = InputBox(strName, "Enter parameter value")
    Set rst = qdf.OpenRecordset

    ... DO STUFF

    rst.Close
    Set rst = Nothing
    Set prm = Nothing
    Set qdf = Nothing
    set db = nothing


    This is simplistic, of course, and assumes that you have one parameter that is not a string or a date, but it will give you the general approach.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Opening parameter queries in code (Access97/SP2)

    You can fill in all your parameters using the Eval() function before opening the recordset. See code snippet below.

    Dim dbs As Database
    Dim qdf As QueryDef
    Dim prm As Parameter
    Dim rstGraphData As Recordset
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open dynaset-type Recordset object.
    Set qdf = dbs.QueryDefs("qryGraph")
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm

    Set rstGraphData = qdf.OpenRecordset(dbOpenDynaset)


    HTH

    Peter

Posting Permissions

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