Error '3061'

[Ryan]

I get this error
Error '3061' Too few parameters. Expected 0
When I run this query.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from AllBalancesByFCandDB")
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1

Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub

Here is my select * from AllBalancesByFCandDB if it will help.

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

What am I doing wrong??

 

[Dirk Goldgar]

I get this error
Error '3061' Too few parameters. Expected 0
When I run this query.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from AllBalancesByFCandDB")
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1

Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub

Here is my select * from AllBalancesByFCandDB if it will help.

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

What am I doing wrong??

Your query, "AllBalancesByFCandDB", has parameters. When Access runs
queries with parameters, it resolves those parameters automatically. When
you run such a query via DAO, however, you have to resolve the parameters
yourself. Here's a handy way to do it:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()

' ... rest of code here

 

[Ryan]

Right on, exactly what I was looking for. I was reaseaching QueryDef when
you replied. Thank you for your valuable help.

I get this error
Error '3061' Too few parameters. Expected 0
When I run this query.

Private Sub Form_Load()
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim i As Integer
Dim j As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("select * from AllBalancesByFCandDB")
rst.MoveFirst
j = -1
i = 0
For i = 0 To rst.Fields.Count - 1
If rst.Fields(i).Name Like "*FinClass" Then GoTo skip_it
j = j + 1

Select Case j
Case 0
Me.lbl1.Caption = rst.Fields(i).Name
Case 1
Me.lbl2.Caption = rst.Fields(i).Name
End Select
skip_it:
Next i
rst.Close
Set rst = Nothing
End Sub

Here is my select * from AllBalancesByFCandDB if it will help.

PARAMETERS [Forms]![Parameters].[LocationFilter] Text ( 255 ),
[Forms]![Parameters].[StartDateFilter2] Text ( 255 ),
[Forms]![Parameters].[EndDateFilter2] Text ( 255 );
TRANSFORM Sum(AllCharges.BalDue) AS SumOfBalDue
SELECT AllCharges.FinClass, Sum(AllCharges.BalDue) AS [Total Of BalDue]
FROM AllCharges
WHERE (((InStr("," & Forms!Parameters.LocationFilter & ",","," & [db] &
","))>0))
GROUP BY AllCharges.FinClass
ORDER BY AllCharges.FinClass
PIVOT AllCharges.DB;

What am I doing wrong??

Your query, "AllBalancesByFCandDB", has parameters. When Access runs
queries with parameters, it resolves those parameters automatically. When
you run such a query via DAO, however, you have to resolve the parameters
yourself. Here's a handy way to do it:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Dim i As Integer
Dim j As Integer

Set db = CurrentDb
Set qdf = db.QueryDefs("AllBalancesByFCandDB")
For Each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset()

' ... rest of code here

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)