problems with code

[joeythelips]

Hi,

I received some code yesterday from GHOLDEN re exporting a crosstab query to excel.

However, when i run the code, i get an error: 'TOO FEW PARAMETERS, EXPECTING 2'. When i debug, it points to the following line:

Set rst = db.OpenRecordset("Wind_Speeds_Graph", dbOpenDynaset)

I really need to resolve this urgently, and would really appreciate any help.

Thanks,

Joe

This is the code:

Public Sub Command1_Click()
Dim ExcelApp As New Excel.Application
Dim intColIndex As Integer
Dim intRowIndex As Integer
Dim intCounter As Integer
Dim db As database
Dim rst As Recordset

'Open query as recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Wind_Speeds_Graph", dbOpenDynaset)

With ExcelApp
'open excel with file D:Test
.Workbooks.Open "C:\Documents and Settings\obrienj\Desktop\wind_speeds_graph.xls"
.Visible = True

'Set start column and row
intColIndex = 0
intRowIndex = 5

'loop through recordset and write fields to cells
Do While Not rst.EOF
.Cells(intRowIndex, intColIndex + 1) = rst!QuestionCode
.Cells(intRowIndex, intColIndex + 3) = rst!Question
.Cells(intRowIndex, intColIndex + 3) = rst!AnswerCode
.Cells(intRowIndex, intColIndex + 4) = rst!Answer
intRowIndex = intRowIndex + 1
rst.MoveNext
Loop
'Close Excel
.ActiveWorkbook.Close True
.Quit

End With
rst.Close
Set rst = Nothing
Set ExcelApp = Nothing
End Sub

 

[GHolden]

what version of access are you running, I've just tested again and it works fine on my machine.

 

[oharab]

If you are using any parameters in a crosstab query you MUST reference them explicitly if you want to use them as recordsets. In the query design view choose query->parameters from the menu and enter your parameters eg if your query uses
>=[enter date] and <=[forms]![frmmain]![txtDate]
as it's criteria then enter
[enter date]
and
[forms]![frmmain]![txtDate]

in the parameters box.

HTH

Ben ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[joeythelips]

Hi,

i'm running access 2000

 

[GHolden]

Good point oharab.
joeythelips, have you followed oharab's suggestion.

 

[joeythelips]

Hi,

yes, i had done that already with the 2 fields that have the paramaters. I gave both a data type of value.

Joe

 

[GHolden]

So...
Just to check
&quot;Wind_Speeds_Graph&quot; is the name of your query and it's in the current database.

I'm using access 97 for this example but can't see it should make any difference.

 

[joeythelips]

yes, thats the name of the query

 

[oharab]

does the query run ok if you open it manually? Does it ask you for parameters when you do that?
Perhaps you could paste the sql here so we could have a look at it.

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[GHolden]

Do you have a use any ADO recordsets in you db or have a reference to ADO. If yes, you need to put DAO infront of the the

Dim rst as DAO.Recordset

Otherwise microsoft knowledge base article Q209203 might be of some help.

Really sorry running out of ideas, like I say works fine for me.