Too few parameters. Expected 1.

  • Thread starter Thread starter Bill Murphy
  • Start date Start date
B

Bill Murphy

I have a couple of complex queries that use a date parameter from a field on
a form. These two queries are included in a union query, and a summary
query (qryFeeSummaryAllTypesAsOfDate below) is produced using the union
query. The two detail queries are the only ones that contain the date
parameter.

I understand that when queries are run in VBA they need special coding to
avoid the "Too few parameters" error, and I am getting this error message.
In the code below I attempted to add the needed code regarding the form
parameter, but I'm getting "Error 3265 - Item not found in this collection."
on the following line:

qdfDealClosing!Forms!frmCBREFeeProjection.Form!txtAsOfDate =
Forms!frmCBREFeeProjection.Form!txtAsOfDate

I wonder why the query definition code below is not working? Also, should
the query definition be done on each of the two detail queries below (which
contain the parameter), or should the definition be done just on the final
summary query (which contains no parameters) in the last line below?

Bill
________________________________________________________________
Dim db As Database
Set db = CurrentDb
Dim rstProjection As Recordset
Dim rstSourceData As Recordset
Dim qdfDealClosing As QueryDef
Dim qdfOtherTypes As QueryDef

' the two following queries contain the parameter
forms!frmCBREFeeProjection.form!txtAsOfDate
Set qdfDealClosing =
db.QueryDefs("qryFeeInstallmentsOutstandingDealClosingAsOfDate")
Set qdfOtherTypes =
db.QueryDefs("qryFeeInstallmentsOutstandingOtherTypesAsOfDate")

' the next line gets the error "3265 - Item not found in this
collection"
qdfDealClosing!Forms!frmCBREFeeProjection.Form!txtAsOfDate = _
Forms!frmCBREFeeProjection.Form!txtAsOfDate

qdfOtherTypes!Forms!frmCBREFeeProjection!txtAsOfDate = _
Forms!frmCBREFeeProjection!txtAsOfDate

Set rstProjection = db.OpenRecordset("tblCBREFeeProjectionTemp",
dbOpenDynaset)

' this is the summary query which gets "3061 - Too few parameters.
Expected 1." if
' the form parameter is not defined ahead of time in the above code
Set rstSourceData = db.OpenRecordset("qryFeeSummaryAllTypesAsOfDate",
dbOpenDynaset)
 
Here's the code that eventually worked for me:

Dim db As DAO.Database

Set db = CurrentDb

Dim rstSourceData As DAO.Recordset

Dim qdfAllTypes As DAO.QueryDef

Dim prm As DAO.Parameter

Set qdfAllTypes = db.QueryDefs("qryFeeSummaryAllTypesAsOfDate")

For Each prm In qdfAllTypes.Parameters

prm.Value = Eval(prm.Name)

Next prm

Set rstSourceData = qdfAllTypes.OpenRecordset(dbOpenDynaset)
 
Back
Top