Error 3061 Too Few Paramters

[JoeMicro]

Hi People,

i have got this code:

Dim ReportName As String
    Dim strNadvan As String
    Dim i As Integer
    
    Dim rsQry, Rs As Recordset
    Dim SQL As String
    Dim QDF As QueryDef
    Dim QRY As String
    
    With CurrentDb.QueryDefs("OpenBalance")
        .Parameters![What] = "P Or V"
        .Parameters![Date] = Me.txtFromDate
        Set rsQry = .OpenRecordset(dbOpenSnapshot, dbForwardOnly)
    End With
        
    For i = 0 To Me.lstNadvonim.ListCount - 1
        If Me.lstNadvonim.Selected(i) Then
            strNadvan = Me.lstNadvonim.Column(0, i)
            SQL = "SELECT * FROM OpenBalance WHERE [From] = '" & strNadvan & "'"
            Set Rs = CurrentDb.OpenRecordset(SQL)
            strNadvan = "[Records].[From]='" & strNadvan & "'"
            ReportName = "PrintStatment"
            DoCmd.OpenReport ReportName, acPreview, , strNadvan
            [Reports]![PrintStatment]![opBl] = Rs("SumOfAmount") 
            DoCmd.RunCommand acCmdPrint
            DoCmd.Close acReport, ReportName, acSaveYes
            Me.lstNadvonim.Selected(i) = False
        End If
    Next i

but i get the famous Error 3061 at this line

Set Rs = CurrentDb.OpenRecordset(SQL)

any idea?

Joe

 

[RoyVidar]

Seems you're opening the same query (OpenBalance) more than once, and in the For Loop, particularly if you're using multiselect = simple or extended, you might be opening it "umpteen" times (one time per iteration), without first closing it.

You're also encouraged to do explicit declarations, ie

[tt] Dim rsQry As DAO.Recordset
Dim Rs As DAO.Recordset
Dim QDF As DAO.QueryDef[/tt]

Your declaration made rsQry a variant, and Access might be confused as to whether the recordset would be DAO or ADO.

Those issues are probably not related to the issue, though, but either lack of value in the parameter, or a misspelling of one of the field names (From?).

The usual way of testing this, would be to do a

[tt]Debug.Print SQL[/tt]

in the immediate pane (ctrl+g), then copy/paste it into the QBE tools SQL view, and check it out, unless you're able to spot the error just by seing the SQL.

Roy-Vidar

 

[RoyVidar]

Ah - the "real" reason seems to be that you have the parameters What and Date in the query, but only specify it when opening the first time. You'd probably need to resolve it also when opening the query within the loop.

Roy-Vidar

 

[JoeMicro]

Hi Roy,

thanks so much for taking your time,

my Real issue is like this,
"OpenBalance" is a query, my initial code was

For i = 0 To Me.lstNadvonim.ListCount - 1
        If Me.lstNadvonim.Selected(i) Then
            strNadvan = Me.lstNadvonim.Column(0, i)
            SQL = "SELECT * FROM OpenBalance WHERE [From] = '" & strNadvan & "'"
            Set Rs = CurrentDb.OpenRecordset(SQL)
            strNadvan = "[Records].[From]='" & strNadvan & "'"
            ReportName = "PrintStatment"
            DoCmd.OpenReport ReportName, acPreview, , strNadvan
            [Reports]![PrintStatment]![opBl] = Rs("SumOfAmount") 
            DoCmd.RunCommand acCmdPrint
            DoCmd.Close acReport, ReportName, acSaveYes
            Me.lstNadvonim.Selected(i) = False
        End If
    Next i

and this worked great as long i had one Criteria in the query.

but then i needed to add a criteria which is based on another form (which is the form that runs this code)

so i did a criteria in the query "<=[Form]![PrintStatment]![txtFromDate]"

thats when the problem started.

what do you think is the bast way to solve this issue?


Thanks
Joe

 

[RoyVidar]

You are opening the same query (at least) twice (which I don't understand). The first time, you resolve two parameters ("What" and "Date"), the next time you open something based on that query, you do not resolve the parameters, this is most likely what gives 3601 - you've provided values for "Too Few Parameters".

So, you need to either resolve the parameters when you open the query the second time (like you do the first time), or do this criterion too dynamic, i e

[tt]SQL = "SELECT * FROM OpenBalance WHERE [From] = '" & strNadvan & "'" & _
" AND TheOtherField <= #" & format(Forms![PrintStatment]![txtFromDate], "yyyy-mm-dd") & "#"[/tt]

Roy-Vidar

 

[JoeMicro]

What i m trying to do in the SQL statement is, to extract a value from a Query, which is a sum query, what i don't know how to do is, run that query with 2 criterion's ("What" and "Date") so it should total for thous criterion's for a whole group, and then extract a value matching one record in the group.

the reason y i open twice the recordset is because i don't really know what i m doing,

as i said in my previous post, in the beginning i open the recordset once, with no parameters everything worked fine, until i needed do add the "Date" criteria, which takes a value from another form, and i started getting the problem.

so i don't think what you suggested could work, because first i need to run the query and then SELECT a record

how do i do that?

thank you tones for your help

Joe