By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,661 Members | 1,917 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,661 IT Pros & Developers. It's quick & easy.

Runtime error 3061 - Too few parameters. Expected 1

100+
P: 121
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.     Dim dbsCurrent As Database
  3.     Dim rstQAssignedHrsSum As DAO.Recordset
  4.  
  5.  
  6.     Set dbsCurrent = CurrentDb
  7.  
  8.     Set rstQAssignedHrsSum = _
  9.       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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM T_ActiveSession
  3. 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
  4. HAVING (((T_ActiveSession.ProjID)=[Forms]![F_ClientDetails]![SF_Session].[Form]![ProjID]));
  5.  
  6.  
Jan 19 '09 #1
Share this Question
Share on Google+
3 Replies


DonRayner
Expert 100+
P: 489
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.
Jan 19 '09 #2

Expert Mod 2.5K+
P: 2,545
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)

Expand|Select|Wrap|Line Numbers
  1. Public Function FormFieldValue(byVal FormName As String, byVal FieldName As String)
  2.     FormFieldValue = Forms(FormName).Controls(FieldName)
  3. End Function
  4.  
  5. Public Function SubFormFieldValue(ByVal FormName As String, ByVal SubFormName As String, ByVal FieldName As String)
  6.     SubFormFieldValue = Forms(FormName).Controls(SubFormName).Form.Controls(FieldName)
  7. 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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM T_ActiveSession
  3. 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
  4. HAVING (((T_ActiveSession.ProjID)=SubFormFieldValue("F_ClientDetails", "SF_Session", "ProjID")));
  5.  
Or, from the Access Query Editor with the query loaded you can simply replace the criteria referring to Forms![F_ClientDetails]... etc

with

Expand|Select|Wrap|Line Numbers
  1. SubFormFieldValue("F_ClientDetails", "SF_Session", "ProjID")
-Stewart
Jan 19 '09 #3

100+
P: 121
Hi Stuart/Don

Many thanks works a treat

Regards Phill
Jan 20 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.