OpenRecordset

[RedHeadedMonster via AccessMonster.com]

Im exporting a query to excel from a form. I've done this before, copied and
pasted the code from the previous time (works perfectly), this time Im
getting a bizarre error.

here are the first few lines of the code:

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSheetName As String

strQueryName = "rptPartsDetailsExcel"
strSheetName = Left(strQueryName, 11)
strSheetName = Trim(strSheetName)

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)

Last line now gets the following error: "Too few Parameters. Expected 4"

WTH? It works perfectly on the other form.

Help would be appreciated.

RHM

 

[Stefan Hoffmann]

hi,

WTH? It works perfectly on the other form.

Declare your DAO objects containing the full name:

Dim acQuery As DAO.QueryDef
Dim objRST As DAO.RecordSet

and take a look at the value of strQueryName. It looks like the name of
a report, not a query.


mfG
--> stefan <--

 

[Dirk Goldgar]

Im exporting a query to excel from a form. I've done this before, copied
and
pasted the code from the previous time (works perfectly), this time Im
getting a bizarre error.

here are the first few lines of the code:

Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook
Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSheetName As String

strQueryName = "rptPartsDetailsExcel"
strSheetName = Left(strQueryName, 11)
strSheetName = Trim(strSheetName)

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)

Last line now gets the following error: "Too few Parameters. Expected 4"

WTH? It works perfectly on the other form.

An object named "rptPartsDetailsExcel" is a query? Usually the "rpt" prefix
denotes a report. But that's probably not the issue here. Most likely your
query is taking values or criteria from fields on a form (which are treated
as parameters), or else it contains field names that are misspelled or not
present in the tables (in which case the query processor assumes they are
parameters).

Either way, the parameters can't be resolved automatically when you open a
recordset on the query; you have to provide the values for them, or use the
following trick to get Access to do it:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs(strQueryName)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set objRST = qdf.OpenRecordset()

If the "parameters" are references to form fields, that will get Access to
fill in their values.

 

[RedHeadedMonster via AccessMonster.com]

same error message, any other ideas?

hi,

Declare your DAO objects containing the full name:

Dim acQuery As DAO.QueryDef
Dim objRST As DAO.RecordSet

and take a look at the value of strQueryName. It looks like the name of
a report, not a query.

mfG
--> stefan <--

 

[RedHeadedMonster via AccessMonster.com]

still has the same error messsage
Any other ideas?

Im exporting a query to excel from a form. I've done this before, copied
and

[quoted text clipped - 24 lines]

WTH? It works perfectly on the other form.

An object named "rptPartsDetailsExcel" is a query? Usually the "rpt" prefix
denotes a report. But that's probably not the issue here. Most likely your
query is taking values or criteria from fields on a form (which are treated
as parameters), or else it contains field names that are misspelled or not
present in the tables (in which case the query processor assumes they are
parameters).

Either way, the parameters can't be resolved automatically when you open a
recordset on the query; you have to provide the values for them, or use the
following trick to get Access to do it:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs(strQueryName)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set objRST = qdf.OpenRecordset()

If the "parameters" are references to form fields, that will get Access to
fill in their values.

 

[Dirk Goldgar]

still has the same error messsage
Any other ideas?

Post the SQL of the query.