Automation (query for Exporting to Excel) Error

[ShanVel]

Hi folks, I need your help.

Did any one get a error message like the one below:
“
Run-time error ‘3061’:
Too few parameters. Expected 1.
“
Basically I have a form with a command button and a combo box. The combo box's row source property is set to the following sql:

SELECT DISTINCTROW tblProjts1.intProjectID, tblProjts1.chrProjectName FROM tblProjts1 ORDER BY tblProjts1.chrProjectName;

I have a command button which runs the following procedure (Thanks, credit goes to madrav72):
Private Sub cmdSendToExcel_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb

'Set rs = db.OpenRecordset("qryOne", dbOpenSnapshot)
Set rs = db.OpenRecordset("qryTwo", dbOpenSnapshot)

'Start a new workbook in Excel
Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

Set oBook = oApp.Workbooks.Add
Set oSheet = oBook.Worksheets(1)

'Add the field names in row 1
Dim i As Integer
Dim iNumCols As Integer
iNumCols = rs.Fields.Count
For i = 1 To iNumCols
oSheet.Cells(1, i).Value = rs.Fields(i - 1).Name
Next

'Add the data starting at cell A2
oSheet.Range("A2").CopyFromRecordset rs

'Format the header row as bold and autofit the columns
With oSheet.Range("a1").Resize(1, iNumCols)
.Font.Bold = True
.EntireColumn.AutoFit
End With

oApp.Visible = True
oApp.UserControl = True

'Close the Database and Recordset
rs.Close
db.Close

End Sub
qryOne:
SELECT tblProjts1.intProjectId, tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblMaxLoad.*
FROM tblProjts1 INNER JOIN tblMaxLoad ON tblProjts1.intProjectId = tblMaxLoad.intProjectId;

qryTwo:
SELECT tblProjts1.intProjectId, tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblMaxLoad.*
FROM tblProjts1 INNER JOIN tblMaxLoad ON tblProjts1.intProjectId=tblMaxLoad.intProjectId
WHERE (((tblMaxLoad.intProjectId)=Forms!frmReprtSelen!cboProj));

My problem: When I run cmdSendToExcel_Click procedure with “ Set rs = db.OpenRecordset("qryOne", dbOpenSnapshot) “ it loads the excel and export the data to Excel without any problem. The Excel spreadsheet will show the rows as follows:
ProjtId ProjName Column3 Column4 Column5, etc
8 Bechtel xxx yyy
9 WOPS aaa bbb
11 Spring zzz ttt
12 AgP mmm nnn

But when I run with Set rs = db.OpenRecordset("qryTwo", dbOpenSnapshot), I get the error
“Run-time error ‘3061’:
Too few parameters. Expected 1.”

I even tested the qryTwo using a separate command button with the following code on the click event:
stDocName = "qryTwo"
DoCmd.OpenQuery stDocName, acNormal, acEdit
and it produces the single row based on the projectId selected on combo box (as shonw below in datasheet view):
ProjtId ProjName Column3 Column4 Column5, etc
8 Bechtel xxx yyy

Does any one have any clue what this error 3061 is? Please help. I am struggling almost a week to figure this out, NO LUCK.

Thanks for your time and help in advance.

Shan.

 

[ShanVel]

Automation (query for Exporting to Excel) Error - Got it!!

I figured it out the "Run-time error ‘3061’:Too few parameters. Expected 1." error.

Solution:
Do not pass parameter in qryTwo. instead you pass it through VBA via SQL string. Hence, the revised qryTwo should like this:
SELECT tblProjts1.intProjectId, tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, tblMaxLoad.*
FROM tblProjts1 INNER JOIN tblMaxLoad ON tblProjts1.intProjectId=tblMaxLoad.intProjectId;

Note, now the qryTwo is same as qryOne.

Now make the following changes in cmdSendToExcel_Click() procedure:
Add the following lines:
Dim strSQL As String
strSQL = "select * from qryTwo WHERE [tblMaxLoad.intProjectId] = " & Forms. frmReprtSelen!cboProj
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
Delete the following lines:
'Set rs = db.OpenRecordset("qryOne", dbOpenSnapshot)
Set rs = db.OpenRecordset("qryTwo", dbOpenSnapshot)

That is it. Your Run-time error ‘3061 is gone and you pass the parameter via SQL string as shown above.

Thought may be useful to post this finding! Any further information, refer to Microsoft Knowledgebase Article # Q209203 under http://support.microsoft.com

Shan.