Problem with TempVars

Question

I am attempting to create the recordset. But I get a Runtime error 3061, Too few parameters. Expected 3

If I replace the tempVars in my query, with a regular parameter, say replace [tempVars]![CalendarID] with 1 and replace the tempVars fort year start and end with dates, everything works as expected.

Any guidance would be greatly appreciated.

This is the recordset:

    strSQL = "SELECT RequestDate, AvailableSlots " & _

             "FROM qry_FilteredSlots GROUP BY RequestDate, AvailableSlots " & _

             "HAVING Month([RequestDate]) = " & intMonth

        Set db = CurrentDb

        Set rst = db.OpenRecordset(strSQL)

        If rst.RecordCount > 0 Then rst.MoveFirst

This is the query:

SELECT qry_Quota_Assignments_ByDate.Dates AS RequestDate, qry_Filtered_RequestCount.CountOfRequestID, qry_Quota_Assignments_ByDate.Quota AS Slots, qry_Quota_Assignments_ByDate.CalendarID, IIf([CountOfRequestID]>=[Slots],0,1) AS AvailableSlots

FROM qry_Filtered_RequestCount INNER JOIN qry_Quota_Assignments_ByDate ON qry_Filtered_RequestCount.RequestDate = qry_Quota_Assignments_ByDate.Dates

GROUP BY qry_Quota_Assignments_ByDate.Dates, qry_Filtered_RequestCount.CountOfRequestID, qry_Quota_Assignments_ByDate.Quota, qry_Quota_Assignments_ByDate.CalendarID

HAVING (((qry_Quota_Assignments_ByDate.Dates) Between [tempVars]![YearStart] And [tempVars]![YearEnd]) AND ((qry_Quota_Assignments_ByDate.CalendarID)=[tempVars]![CalendarID]));

Answer 1

Good. Then modify your code like this:

    strSQL = "SELECT RequestDate, AvailableSlots " & _

             "FROM qry_FilteredSlots GROUP BY RequestDate, AvailableSlots " & _

             "HAVING Month([RequestDate]) = " & intMonth

    Set db = CurrentDb

    Set qd = db.CreateQueryDef("", strSQL)

    For Each p In qd.Parameters

        p.Value = Eval(p.Name)

    Next p

    Set rst = qd.OpenRecordset(dbOpenSnapshot)

    If rst.RecordCount > 0 Then rst.MoveFirst

Answer 2

You are correct

Answer 3

I always debug this the same way, along these lines. In my DebugUtilities module I have this procedure:

Public Sub dumpParameters(ByVal strQueryName As String)

    Dim qd As DAO.QueryDef

    Dim p As DAO.Parameter

    Set qd = CurrentDb.QueryDefs(strQueryName)

    For Each p In qd.Parameters

        Debug.Print p.Name

    Next p

    Set qd = Nothing

End Sub

Then in the Immediate window I enter:

dumpParameters "myQuery"

Chances are you will get output like this:

[tempVars]![YearStart]

[tempVars]![YearEnd]

Can you confirm?

Answer 4

The query is referenced in the FROM statement

strSQL = "SELECT RequestDate, AvailableSlots " & _

             "FROM qry_FilteredSlots GROUP BY RequestDate, AvailableSlots " & _

             "HAVING Month([RequestDate]) = " & intMonth

Answer 5

How exactly is the query involved in the RecordSet?  The rst is using the strSQL, not the query.  Please explain further.

You should probably read: http://www.devhut.net/2011/11/07/ms-access-vba-run-parameter-query-in-vba/ to learn how to evaluate query parameters when executing queries through VBA.