Runtime error 3061 - Too few parameters. Expected 1

[phill86]

Hi,

I am trying to run the following query in a recordset and i get the following error message

Runtime error 3061 - Too few parameters. Expected 1

i am using the following code

 
    Dim dbsCurrent As Database
    Dim rstQAssignedHrsSum As DAO.Recordset
 
 
    Set dbsCurrent = CurrentDb
 
    Set rstQAssignedHrsSum = _
      dbsCurrent.OpenRecordset("Q_SFormTotalHrs1", dbOpenDynaset)

The query is using the following SQL if i take out the reference to the form in the query criteria it works fine so it must be something to do with this any help is greatly appreciated

Regards Phill

SELECT T_ActiveSession.SessionID, T_ActiveSession.ProjID, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_ActiveSession.SessTypeID, DateDiff("n",[starttime],[endtime]-[break]-[downtime]) AS Expr1, T_ActiveSession.SessComplete, T_ActiveSession.Downtime, T_ActiveSession.Break
FROM T_ActiveSession
GROUP BY T_ActiveSession.SessionID, T_ActiveSession.ProjID, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_ActiveSession.SessTypeID, T_ActiveSession.SessComplete, T_ActiveSession.Downtime, T_ActiveSession.Break
HAVING (((T_ActiveSession.ProjID)=[Forms]![F_ClientDetails]![SF_Session].[Form]![ProjID]));

[DonRayner]

Hey Phill;

Does your query run correctly on it's own? Your error is looking like your query is expecting a parameter to be passed to it.

[Stewart Ross]

Hi Don. Phill86's problem is caused by the reference to the form control, which is fine when run from the Access Query Editor but is invalid when run from VBA code. This particular one is further complicated by the reference being to a subform control.

There are a number of ways to resolve this. The simplest solution is to use a bespoke function in place of the direct reference to the form control in the Having clause, as function calls will be treated correctly by the SQL interpreter whereas form control references are treated as invalid.

Place the following two functions in a global code module (one which is visible from the Modules tab of the database window, or create a new one if not)

Public Function FormFieldValue(byVal FormName As String, byVal FieldName As String)
    FormFieldValue = Forms(FormName).Controls(FieldName)
End Function
 
Public Function SubFormFieldValue(ByVal FormName As String, ByVal SubFormName As String, ByVal FieldName As String)
    SubFormFieldValue = Forms(FormName).Controls(SubFormName).Form.Controls(FieldName)
End Function

The first of these can be used when referring to a control value on a main form, the second for a subform. To use the second function in place of your direct form control reference in your query alter the SQL code for your existing query as shown below:

SELECT T_ActiveSession.SessionID, T_ActiveSession.ProjID, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_ActiveSession.SessTypeID, DateDiff("n",[starttime],[endtime]-[break]-[downtime]) AS Expr1, T_ActiveSession.SessComplete, T_ActiveSession.Downtime, T_ActiveSession.Break
FROM T_ActiveSession
GROUP BY T_ActiveSession.SessionID, T_ActiveSession.ProjID, T_ActiveSession.StartTime, T_ActiveSession.EndTime, T_ActiveSession.SessTypeID, T_ActiveSession.SessComplete, T_ActiveSession.Downtime, T_ActiveSession.Break
HAVING (((T_ActiveSession.ProjID)=SubFormFieldValue("F_ClientDetails", "SF_Session", "ProjID")));

Or, from the Access Query Editor with the query loaded you can simply replace the criteria referring to Forms![F_ClientDetails]... etc

with

SubFormFieldValue("F_ClientDetails", "SF_Session", "ProjID")

-Stewart

[phill86]

Hi Stuart/Don

Many thanks works a treat

Regards Phill