.OpenRecordset problem

[SamL]

Hi, I have the following code:

Private Sub GroupFooter1_Print(Cancel As Integer, PrintCount As Integer)

    Dim rstClientGroup As DAO.Recordset, qryCount As QueryDef, strSQL As String
    
    Set qryCount = CurrentDB.CreateQueryDef("")
    With qryCount
    strSQL = "SELECT Program, Count(ClientNum) AS CountOfClientNum FROM qryClientGroupSub " _
        & "WHERE (((Program) = [rptAnnualTotalContactsByProg].[Program])) " _
        & "GROUP BY Program"
        .SQL = strSQL
        Set rstClientGroup = .OpenRecordset(dbOpenDynaset)
        With rstClientGroup
            .MoveFirst
            Me.CountOfClientNum = !CountOfClientNum
            .Close
        End With
        .Close
    End With


End Sub

It breaks with a run-time error on this line:

        Set rstClientGroup = .OpenRecordset(dbOpenDynaset)

The code is supposed to run during the report, where the code is in a group footer (not the report footer).

The Access error message is "Too few parameters. Expected 2." What's wrong with the code?
Thanks for the help,
Sam

[Gicu]

Probably the qryClientGroupSub query has parameters and also you are not isolating the [rptAnnualTotalContactsByProg].[Program] variable in your string:

'WHERE (((Program) = [rptAnnualTotalContactsByProg].[Program])) "
WHERE (((Program) = '" & Me.[Program] & "')) " 'assume Program is a text type not numeric

But probably you don't need all that when a simple dCount would do.

Cheers,

[Micron]

Pretty sure you can RunSql written like that (same as you can from an actual query), but you cannot do so from vba using CurrentDb.Execute or .Openrecordset. Not suggesting you use RunSql because that is only for action queries. To stay more or less with your current approach, pretty sure you would have to concatenate as shown if using vba, or don't use a temporary querydef, or use a stored query but modify the querydef sql. That's all for info purposes only because I agree that a domain aggregate function is probably all you need - possibly just in the control itself.

[SamL]

Thanks to both of you for answering. Unfortunately, I never learned how to use the domain functions, so I have to limp around them, I'm afraid.

Vlad, you are correct in both of your observations. I glossed over isolating the [Program] value, and I didn't even think that the error message was referring to the missing parameters. I added a line

.Parameters("Enter Year of Service") = Me.ServiceYear

and isolated the [Program] variable with judicious use of double-quotes. It works now. Thanks again.

The SQL is now

    strSQL = "SELECT Program, Count(ClientNum) AS CountOfClientNum FROM qryClientGroupSub " _
        & "WHERE (((Program) = """ & Me.Program & """)) " _
        & "GROUP BY Program"
        .SQL = strSQL
        .Parameters("Enter Year of Service") = Me.ServiceYear
        Set rstClientGroup = .OpenRecordset(dbOpenDynaset)