By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,632 Members | 1,499 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,632 IT Pros & Developers. It's quick & easy.

Excel macro - error - too few parameters

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1. Sub Template_Provider_Retail(Spinner As String)
  2.  
  3. Dim rs As Recordset
  4. Dim strSQL As String
  5. Dim i As Integer
  6. Dim IsSmall As Boolean
  7. Dim IsInsuf As Boolean
  8.  
  9. wbMaster.Worksheets("Provider_Retail").Copy After:=wbReport.Worksheets(wbReport.Worksheets.Count) 'copies the pages
  10.  
  11. strSQL = "Select Name, Retail_Credit_Index " & _
  12.             "FROM Retail_Credit_2007_Totals " & _
  13.             "WHERE Active = True " & _
  14.             "AND Name_2 = 'Total' " & _
  15.             "ORDER by Retail_Credit_Index Desc, Name Asc"
  16.  
  17. Set rs = db.OpenRecordset(strSQL)
  18.  
  19.  
  20.  With rs
  21.         If Not .BOF Then
  22.           .MoveLast   'Must do the movelast command to get the recordcount
  23.           .MoveFirst
  24.           Cells(14, 17).CopyFromRecordset rs, .RecordCount
  25.         End If
  26.  
  27.     End With
  28.  
  29. strSQL = "Select Name, Retail_Credit_Index " & _
  30.             "FROM Retail_Credit_2007 " & _
  31.             "WHERE Active = True " & _
  32.             "AND Name_2 = 'Total' " & _
  33.             "ORDER by Retail_Credit_Index Desc, Name Asc"
  34.  
  35. Set rs = db.OpenRecordset(strSQL)
  36.  
  37.  
  38.  With rs
  39.         If Not .BOF Then
  40.           .MoveLast   'Must do the movelast command to get the recordcount
  41.           .MoveFirst
  42.           Cells(14, 22).CopyFromRecordset rs, .RecordCount
  43.         End If
  44.  
  45.     End With
  46.  
  47.  
  48. strSQL = "Select Name, Retail_Credit_Index " & _
  49.             "FROM Retail_Credit_2006_Totals " & _
  50.             "WHERE Active = True " & _
  51.             "AND Name_2 = 'Total' " & _
  52.             "ORDER by Retail_Credit_Index Desc, Name Asc"
  53.  
  54. Set rs = db.OpenRecordset(strSQL)
  55.  
  56.  
  57.  With rs
  58.         If Not .BOF Then
  59.           .MoveLast   'Must do the movelast command to get the recordcount
  60.           .MoveFirst
  61.           Cells(26, 17).CopyFromRecordset rs, .RecordCount
  62.         End If
  63.  
  64.     End With
  65.  
  66.  
  67. strSQL = "Select Name, Retail_Credit_Index " & _
  68.             "FROM Retail_Credit_2006 " & _
  69.             "WHERE Active = True " & _
  70.             "AND Name_2 = 'Total' " & _
  71.             "ORDER by Retail_Credit_Index Desc, Name Asc"
  72.  
  73.  
  74. Set rs = db.OpenRecordset(strSQL)
  75.  
  76.  
  77.  With rs
  78.         If Not .BOF Then
  79.           .MoveLast   'Must do the movelast command to get the recordcount
  80.           .MoveFirst
  81.           Cells(26, 22).CopyFromRecordset rs, .RecordCount
  82.         End If
  83.  
  84.     End With
  85.  
  86. rs.Close
  87. Set rs = Nothing
  88.  
  89. Cells.Copy 'COPY ALL THE CELLS IN A WORKBOOK
  90. Cells.PasteSpecial xlPasteValues 'so it breaks the links

Can someone please HELP!!
Mar 26 '07 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,892
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.Cou nt) '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!!
The Sub is expecting you to pass Spinner (a string variable) I don't think you are passing any values.
Mar 27 '07 #2

Expert 5K+
P: 8,434
Yes, AricC is probably right. If not, please provide more specific information about where the error occurs.
Mar 27 '07 #3

P: 4
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.OpenRecordset(strSQL), however before each of those statements the database is return the values that it pulled from the database.......

I don't get it
Mar 27 '07 #4

SammyB
Expert 100+
P: 807
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?
Mar 27 '07 #5

P: 4
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.OpenRecordset(strSQL)
Mar 27 '07 #6

SammyB
Expert 100+
P: 807
When I compile this code the run time error appears when I click debug it hightlight in yellow the first Set rs = db.OpenRecordset(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
Mar 27 '07 #7

Expert 5K+
P: 8,434
When I compile this code the run time error appears when I click debug it hightlight in yellow the first Set rs = db.OpenRecordset(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.
Mar 27 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.