Query with Paramater

[Kenln]

I have a query with a parameter, i.e. qry_param

From there is goes to another query with SUM, i.e. qry_sum

I would like to call qry_sum in VBA and be able to answer the parameter from qry_param.

Does anyone know how I go about this?

Thank you in advance, you guys have been a great help,

 

[Pat Hartman]

Here's an example from one of my databases.

Private Sub cmdClearWinners_Click()
On Error GoTo Err_cmdClearWinners_Click

    Dim dbs As DAO.Database
    Dim qd As DAO.QueryDef
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qClearCount
    qd.Parameters![Enter Date] = Me.txtToday
    qd.Execute
    Set qd = Nothing
    
Exit_cmdClearWinners_Click:
    Exit Sub

Err_cmdClearWinners_Click:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_cmdClearWinners_Click

 

[Kenln]

I'm getting 3065 - Cannot excute a select query

I used you code almost exactly just changing the routine and query names.

 

[Kenln]

This is what I've done so far:

Private Sub MyTest()
On Error GoTo Err_MyTest

    Dim dbs As Database
    Dim rs As Recordset
    Dim x As Integer
        x = 0

    Set dbs = CurrentDb
    Set rs = dbs.OpenRecordset("qry_Param", dbOpenDynaset)
    rs.MoveFirst

    Do While Not rs.EOF And x < 25
        Debug.Print rs.Fields("Description")
        rs.MoveNext
        x = x + 1
    Loop

    rs.Close
    Set rs = Nothing

Exit_MyTest:
    Exit Sub

Err_MyTest:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_MyTest

End Sub

Where do I put the parameter = statement?
Will this work on a query that is using the parameter query as it's source?

Thank you for your help,

 

[Vbark]

Like this, you need to start with a query then open recordset from there

Private Sub MyTest()
On Error GoTo Err_MyTest

    Dim dbs As Database
    Dim rs As Recordset
    Dim x As Integer
        x = 0

    Set dbs = CurrentDb
    

'--new code
    Dim qdf As QueryDef
    Set qdf = dbs.QueryDefs("qry_Param")
        qdf("ParamaterName") = "ParamaterValue"
    Set rs = qdf.OpenRecordset(dbOpenDynaset)
'--new code

    rs.MoveFirst

    Do While Not rs.EOF And x < 25
        Debug.Print rs.Fields("Description")
        rs.MoveNext
        x = x + 1
    Loop

    rs.Close
    Set rs = Nothing

Exit_MyTest:
    Exit Sub

Err_MyTest:
    MsgBox Err.Number & "-" & Err.Description
    Resume Exit_MyTest

End Sub

Set qdf = dbs.QueryDefs("qry_sum") should also work....

 

[Kenln]

This is awesome.

Many, many thanks!