Error 3061: Too Fee Parameters

M

Manuel

I’m getting a 3061 error when opening a recordset on a query which uses a
form field as a parameter. Here’s the code:

Private Sub cmdCalcFees_Click()

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFeeTotalsByWeek")
rs.AddNew
rs!RptgMonthYr = Forms![frmCreatePODmdRpt]![txtRptgMo]
rs!WeekOf = WeekOf(Forms![frmCreatePODmdRpt]![txtBegDate],
Forms![frmCreatePODmdRpt]![txtEndDate])
For Each qdf In db.QueryDefs
If qdf.Name Like "*sum*" Then
Set rs2 = db.OpenRecordset(qdf.Name) 'THE ERROR OCCURS HERE
Select Case qdf.Name
Case Is = "qry_SumAllFees"
rs!SumAllFees = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumAllPrepays"
rs!SumAllPrepays = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumFeesWaived"
rs!SumFeesWaived = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumPrepaidWaivedEqNF"
rs!SumPrepaidWaivedEqNF = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumPrepayWaivedNENF"
rs!SumPrepayWaivedNENF = Nz(rs2!SumOfTran_Amt, 0)
End Select
rs2.Close
End If
Next qdf

rs.Update
rs.Close

Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set rs2 = Nothing

End Sub

Here the SQL for a query that the OpenRecordset method would try and open:

SELECT Sum(tblFees.TRAN_AMT) AS SumOfTRAN_AMT
FROM tblFees
WHERE (((tblFees.TRAN_CD) In ("121","222","522","503")) AND
((tblFees.WeekOf)=WeekOf([Forms]![frmCreatePODmdRpt]![txtBegDate],[Forms]![frmCreatePODmdRpt]![txtEndDate])))

As you can see, the query uses a function, WeekOf, which has for its
arguments two fields which are located on a form. The form is open when I
run the code, but I still get the error.

Here’s the code for the WeekOf function (pretty straightforward stuff):

Function WeekOf(BegDt As Date, EndDt As Date) As String

WeekOf = Format(Month(BegDt), "00") & "/" & Format(Day(BegDt), "00") & "/" &
Format(Right(Year(BegDt), 2), "00") & " - " & _
Format(Month(EndDt), "00") & "/" & Format(Day(EndDt), "00")
& "/" & Format(Right(Year(EndDt), 2), "00")

End Function

It appears that the OpenRecordset method has an issue with retrieving
recordsets for queries which use form fields as criteria. I do not get an
error when opening the query from the database window. Also, I removed the
WeekOf criteria and ran the above VBA code and did not receive the error.

Does anyone have any suggestions as to how I can resolve this issue? I
really didn’t want to have to convert from select group queries to make-table
queries.

Thanks,

Manuel
 
D

Dirk Goldgar

Manuel said:
I’m getting a 3061 error when opening a recordset on a query which uses a
form field as a parameter. Here’s the code:

Private Sub cmdCalcFees_Click()

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFeeTotalsByWeek")
rs.AddNew
rs!RptgMonthYr = Forms![frmCreatePODmdRpt]![txtRptgMo]
rs!WeekOf = WeekOf(Forms![frmCreatePODmdRpt]![txtBegDate],
Forms![frmCreatePODmdRpt]![txtEndDate])
For Each qdf In db.QueryDefs
If qdf.Name Like "*sum*" Then
Set rs2 = db.OpenRecordset(qdf.Name) 'THE ERROR OCCURS HERE
Select Case qdf.Name
Case Is = "qry_SumAllFees"
rs!SumAllFees = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumAllPrepays"
rs!SumAllPrepays = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumFeesWaived"
rs!SumFeesWaived = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumPrepaidWaivedEqNF"
rs!SumPrepaidWaivedEqNF = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumPrepayWaivedNENF"
rs!SumPrepayWaivedNENF = Nz(rs2!SumOfTran_Amt, 0)
End Select
rs2.Close
End If
Next qdf

rs.Update
rs.Close

Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set rs2 = Nothing

End Sub

Here the SQL for a query that the OpenRecordset method would try and open:

SELECT Sum(tblFees.TRAN_AMT) AS SumOfTRAN_AMT
FROM tblFees
WHERE (((tblFees.TRAN_CD) In ("121","222","522","503")) AND
((tblFees.WeekOf)=WeekOf([Forms]![frmCreatePODmdRpt]![txtBegDate],[Forms]![frmCreatePODmdRpt]![txtEndDate])))

As you can see, the query uses a function, WeekOf, which has for its
arguments two fields which are located on a form. The form is open when I
run the code, but I still get the error.

Here’s the code for the WeekOf function (pretty straightforward stuff):

Function WeekOf(BegDt As Date, EndDt As Date) As String

WeekOf = Format(Month(BegDt), "00") & "/" & Format(Day(BegDt), "00") & "/"
&
Format(Right(Year(BegDt), 2), "00") & " - " & _
Format(Month(EndDt), "00") & "/" & Format(Day(EndDt), "00")
& "/" & Format(Right(Year(EndDt), 2), "00")

End Function

It appears that the OpenRecordset method has an issue with retrieving
recordsets for queries which use form fields as criteria. I do not get an
error when opening the query from the database window. Also, I removed
the
WeekOf criteria and ran the above VBA code and did not receive the error.

Does anyone have any suggestions as to how I can resolve this issue? I
really didn’t want to have to convert from select group queries to
make-table
queries.

Thanks,

Manuel
Click to expand...



Add this declaration:

Dim prm As DAO.Parameter

And change these lines:
For Each qdf In db.QueryDefs
If qdf.Name Like "*sum*" Then
Set rs2 = db.OpenRecordset(qdf.Name) 'THE ERROR OCCURS HERE
Click to expand...

To these:

For Each qdf In db.QueryDefs
If qdf.Name Like "*sum*" Then
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs2 = qdf.OpenRecordset()


That ought to do it.
 
M

Manuel

Worked like a charm. Thanks a million!!

Dirk Goldgar said:
Manuel said:
I’m getting a 3061 error when opening a recordset on a query which uses a
form field as a parameter. Here’s the code:

Private Sub cmdCalcFees_Click()

Dim qdf As DAO.QueryDef
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblFeeTotalsByWeek")
rs.AddNew
rs!RptgMonthYr = Forms![frmCreatePODmdRpt]![txtRptgMo]
rs!WeekOf = WeekOf(Forms![frmCreatePODmdRpt]![txtBegDate],
Forms![frmCreatePODmdRpt]![txtEndDate])
For Each qdf In db.QueryDefs
If qdf.Name Like "*sum*" Then
Set rs2 = db.OpenRecordset(qdf.Name) 'THE ERROR OCCURS HERE
Select Case qdf.Name
Case Is = "qry_SumAllFees"
rs!SumAllFees = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumAllPrepays"
rs!SumAllPrepays = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumFeesWaived"
rs!SumFeesWaived = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumPrepaidWaivedEqNF"
rs!SumPrepaidWaivedEqNF = Nz(rs2!SumOfTran_Amt, 0)
Case Is = "qry_SumPrepayWaivedNENF"
rs!SumPrepayWaivedNENF = Nz(rs2!SumOfTran_Amt, 0)
End Select
rs2.Close
End If
Next qdf

rs.Update
rs.Close

Set qdf = Nothing
Set db = Nothing
Set rs = Nothing
Set rs2 = Nothing

End Sub

Here the SQL for a query that the OpenRecordset method would try and open:

SELECT Sum(tblFees.TRAN_AMT) AS SumOfTRAN_AMT
FROM tblFees
WHERE (((tblFees.TRAN_CD) In ("121","222","522","503")) AND
((tblFees.WeekOf)=WeekOf([Forms]![frmCreatePODmdRpt]![txtBegDate],[Forms]![frmCreatePODmdRpt]![txtEndDate])))

As you can see, the query uses a function, WeekOf, which has for its
arguments two fields which are located on a form. The form is open when I
run the code, but I still get the error.

Here’s the code for the WeekOf function (pretty straightforward stuff):

Function WeekOf(BegDt As Date, EndDt As Date) As String

WeekOf = Format(Month(BegDt), "00") & "/" & Format(Day(BegDt), "00") & "/"
&
Format(Right(Year(BegDt), 2), "00") & " - " & _
Format(Month(EndDt), "00") & "/" & Format(Day(EndDt), "00")
& "/" & Format(Right(Year(EndDt), 2), "00")

End Function

It appears that the OpenRecordset method has an issue with retrieving
recordsets for queries which use form fields as criteria. I do not get an
error when opening the query from the database window. Also, I removed
the
WeekOf criteria and ran the above VBA code and did not receive the error.

Does anyone have any suggestions as to how I can resolve this issue? I
really didn’t want to have to convert from select group queries to
make-table
queries.

Thanks,

Manuel
Click to expand...



Add this declaration:

Dim prm As DAO.Parameter

And change these lines:
For Each qdf In db.QueryDefs
If qdf.Name Like "*sum*" Then
Set rs2 = db.OpenRecordset(qdf.Name) 'THE ERROR OCCURS HERE
Click to expand...

To these:

For Each qdf In db.QueryDefs
If qdf.Name Like "*sum*" Then
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs2 = qdf.OpenRecordset()


That ought to do it.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time error in VBA Code 1
Data Type Conversion error 5
Drop Down doesn't populate form 0
Please help with this code 1
ERROR 3061 - Too few parameters 1
Please Help, RunTime 3061 Too few Parameters 7
"Object doesn't support this property or method" Error on Tree Con 0
Are Nulls Covered? 2

Top