Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calling Parameter Queries (Access97)

    How do I call parameter queries from VBA? I created various parameter queries and stored then in the database. Now I want to open one or more queries from VBA depending on user selection. However, I cannot get it to work. Any help would be appreciated. Find included a small test database used to test opening parameter query. Every time I tried to open the query from the frmStoredProcedureTest by clicking on "Open Stored Query Test" command button, I get the following message " Too few parameters. Expected 1." It would be great if some one could get the calling procedure to work and send me the database or instructions back

    Thanks in advance
    <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>
    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: Calling Parameter Queries (Access97)

    I think I see your problem. When you call a query in code like that, you can't rely on the reference to a form being populated. Here's an alternative that works if you replace your parameter in the query with a simple parameter label (i.e., [ItemID] in both parameters and in the criteria for the ID field):

    <pre>Private Sub cmdOpenQuery_Click()
    Dim dbs As Database
    Dim rst As Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter

    Set dbs = CurrentDb

    Set qdf = dbs.CreateQueryDef("", dbs.QueryDefs("qryStoredProcedureTest").SQL)
    Set prm = qdf.Parameters(0)

    prm = Me!cmbStoredProcedureTest.Column(0)

    Set rst = qdf.OpenRecordset(dbOpenDynaset)

    MsgBox rst!TestName

    With rst
    .Edit
    !TestUpdate = "testing"
    .Update
    End With

    rst.Close
    dbs.Close
    Set rst = Nothing
    Set prm = Nothing
    Set qdf = Nothing
    Set dbs = Nothing
    End Sub</pre>

    Charlotte

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calling Parameter Queries (Access97)

    Thanks Charlotte,

    That worked like a charm.

    Your help is very much appreciated...

    John

Posting Permissions

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