Excel macro - error - too few parameters

[rudevincy]

Hello I am new to VBA and I am trying to create this program however I get the run time error 3061 Too few parameter expected_1, what does this mean, my code is:

Sub Template_Provider_Retail(Spinner As String)

Dim rs As Recordset
Dim strSQL As String
Dim i As Integer
Dim IsSmall As Boolean
Dim IsInsuf As Boolean

wbMaster.Worksheets("Provider_Retail").Copy After:=wbReport.Worksheets(wbReport.Worksheets.Count) 'copies the pages

strSQL = "Select Name, Retail_Credit_Index " & _
            "FROM Retail_Credit_2007_Totals " & _
            "WHERE Active = True " & _
            "AND Name_2 = 'Total' " & _
            "ORDER by Retail_Credit_Index Desc, Name Asc"

Set rs = db.OpenRecordset(strSQL)

 
 With rs
        If Not .BOF Then
          .MoveLast   'Must do the movelast command to get the recordcount
          .MoveFirst
          Cells(14, 17).CopyFromRecordset rs, .RecordCount
        End If
        
    End With

strSQL = "Select Name, Retail_Credit_Index " & _
            "FROM Retail_Credit_2007 " & _
            "WHERE Active = True " & _
            "AND Name_2 = 'Total' " & _
            "ORDER by Retail_Credit_Index Desc, Name Asc"

Set rs = db.OpenRecordset(strSQL)

 
 With rs
        If Not .BOF Then
          .MoveLast   'Must do the movelast command to get the recordcount
          .MoveFirst
          Cells(14, 22).CopyFromRecordset rs, .RecordCount
        End If
        
    End With

 
strSQL = "Select Name, Retail_Credit_Index " & _
            "FROM Retail_Credit_2006_Totals " & _
            "WHERE Active = True " & _
            "AND Name_2 = 'Total' " & _
            "ORDER by Retail_Credit_Index Desc, Name Asc"

Set rs = db.OpenRecordset(strSQL)

 
 With rs
        If Not .BOF Then
          .MoveLast   'Must do the movelast command to get the recordcount
          .MoveFirst
          Cells(26, 17).CopyFromRecordset rs, .RecordCount
        End If
        
    End With


strSQL = "Select Name, Retail_Credit_Index " & _
            "FROM Retail_Credit_2006 " & _
            "WHERE Active = True " & _
            "AND Name_2 = 'Total' " & _
            "ORDER by Retail_Credit_Index Desc, Name Asc"

 
Set rs = db.OpenRecordset(strSQL)

 
 With rs
        If Not .BOF Then
          .MoveLast   'Must do the movelast command to get the recordcount
          .MoveFirst
          Cells(26, 22).CopyFromRecordset rs, .RecordCount
        End If
        
    End With

rs.Close
Set rs = Nothing

Cells.Copy 'COPY ALL THE CELLS IN A WORKBOOK
Cells.PasteSpecial xlPasteValues 'so it breaks the links

Can someone please HELP!!

[AricC]

Hello I am new to VBA and I am trying to create this program however I get the run time error 3061 Too few parameter expected_1, what does this mean, my code is:

Sub Template_Provid er_Retail(Spinn er As String)

Dim rs As Recordset
Dim strSQL As String
Dim i As Integer
Dim IsSmall As Boolean
Dim IsInsuf As Boolean

wbMaster.Worksh eets("Provider_ Retail").Copy After:=wbReport .Worksheets(wbR eport.Worksheet s.Count) 'copies the pages

strSQL = "Select Name, Retail_Credit_I ndex " & _
"FROM Retail_Credit_2 007_Totals " & _
"WHERE Active = True " & _
"AND Name_2 = 'Total' " & _
"ORDER by Retail_Credit_I ndex Desc, Name Asc"

Set rs = db.OpenRecordse t(strSQL)


With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(14, 17).CopyFromRec ordset rs, .RecordCount
End If

End With

strSQL = "Select Name, Retail_Credit_I ndex " & _
"FROM Retail_Credit_2 007 " & _
"WHERE Active = True " & _
"AND Name_2 = 'Total' " & _
"ORDER by Retail_Credit_I ndex Desc, Name Asc"

Set rs = db.OpenRecordse t(strSQL)


With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(14, 22).CopyFromRec ordset rs, .RecordCount
End If

End With


strSQL = "Select Name, Retail_Credit_I ndex " & _
"FROM Retail_Credit_2 006_Totals " & _
"WHERE Active = True " & _
"AND Name_2 = 'Total' " & _
"ORDER by Retail_Credit_I ndex Desc, Name Asc"

Set rs = db.OpenRecordse t(strSQL)


With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(26, 17).CopyFromRec ordset rs, .RecordCount
End If

End With


strSQL = "Select Name, Retail_Credit_I ndex " & _
"FROM Retail_Credit_2 006 " & _
"WHERE Active = True " & _
"AND Name_2 = 'Total' " & _
"ORDER by Retail_Credit_I ndex Desc, Name Asc"


Set rs = db.OpenRecordse t(strSQL)


With rs
If Not .BOF Then
.MoveLast 'Must do the movelast command to get the recordcount
.MoveFirst
Cells(26, 22).CopyFromRec ordset rs, .RecordCount
End If

End With

rs.Close
Set rs = Nothing

Cells.Copy 'COPY ALL THE CELLS IN A WORKBOOK
Cells.PasteSpec ial xlPasteValues 'so it breaks the links


Can someone please HELP!!

The Sub is expecting you to pass Spinner (a string variable) I don't think you are passing any values.

[Killer42]

Yes, AricC is probably right. If not, please provide more specific information about where the error occurs.

[rudevincy]

Yes, AricC is probably right. If not, please provide more specific information about where the error occurs.

I thought that the defined strSQL is passing a value each time it looks into the table and pulls the data that is requested and that is the value that is being passed the error occurs at the first Set rs = db.OpenRecordse t(strSQL), however before each of those statements the database is return the values that it pulled from the database.......

I don't get it

[SammyB]

What we need to know is what line of code gives the error message. When Excel gives an error message, it also hylights a line of code that caused the error. Which line is it?

[rudevincy]

What we need to know is what line of code gives the error message. When Excel gives an error message, it also hylights a line of code that caused the error. Which line is it?

When I compile this code the run time error appears when I click debug it hightlight in yellow the first Set rs = db.OpenRecordse t(strSQL)

[SammyB]

When I compile this code the run time error appears when I click debug it hightlight in yellow the first Set rs = db.OpenRecordse t(strSQL)

Well, I'm a little flummoxed. What reference did you add, so that objects like Recordset were defined?

Also, do this so we have more information:

1. In the VBE IDE, right-click on the menubar and choose Customize
2. Select the Commands Tab
3. Select the Debug Category
4. Drag the compile project button up to the toolbar and drop it next to the run
5. Close
6. Press the Compile Project Button
7. Do you get any error messages

Finally, db is not defined in your Sub, is it a global? How is it defined?
Thanks! --Sam

[Killer42]

When I compile this code the run time error appears when I click debug it hightlight in yellow the first Set rs = db.OpenRecordse t(strSQL)

Well, at a simple level, this is just telling you that you've left out one or more parameters of the OpenRecordset method. It shouldn't be hard to check the syntax and see what parameters are required.

The contents of strSQL are irrelevant at this point - it is a string, and that's all you need to know here. So you have provided one string parameter - what else is required? In fact, depending on your settings, as soon as you type the "(" VBA should show you the parameter list.