OpenRecordset from query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all

I have a query which selects one record from the current record of a
continuous form. When run normally as a select query this works but I am
trying to use this query via code:

Set rs = db.OpenRecordset("qry CB Control Log Excel", dbOpenSnapshot)

When run I get the error 'Too few parameters...'.

The query selects data from more than one table and I only want certain
fields including to export the data to Excel.

Can anyone tell me a way round this?

Thanks in advance for any help.
Sue
 
hi Sue,
I have a query which selects one record from the current record of a
continuous form. When run normally as a select query this works but I am
trying to use this query via code:

Set rs = db.OpenRecordset("qry CB Control Log Excel", dbOpenSnapshot)
Click to expand...
Really spaces in the name? Then try:

Set rs = db.OpenRecordset("[qry CB Control Log Excel]", _
dbOpenSnapshot)


mfG
--> stefan <--
 
hughess7 said:
Hi all

I have a query which selects one record from the current record of a
continuous form. When run normally as a select query this works but I
am trying to use this query via code:

Set rs = db.OpenRecordset("qry CB Control Log Excel", dbOpenSnapshot)

When run I get the error 'Too few parameters...'.

The query selects data from more than one table and I only want
certain fields including to export the data to Excel.

Can anyone tell me a way round this?

Thanks in advance for any help.
Sue
Click to expand...

Does your query include any form-control references; e.g., a criterion
such as "[Forms]![MyForm]![SomeControl]"? If so, *Access* knows about
the form and control, and can fill in the value of the reference when
you run a query via the user interface, but DAO -- which you are using
to open the recordset -- does not know about them. As far as DAO is
concerned, these are parameters, for which you must supply values.

You can still get Access to fill in these values for you, using the DAO
QueryDef object and a little trick involving the Eval() function. Try
this:

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

Set qdf = db.QueryDefs("qry CB Control Log Excel")

For each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenSnapshot)
 
Hi

I got it to work by removing the parameter from the query and using the
query name in the openrecordset statement with a where clause. The thing that
is confusing me now is that the data exports to excel as date format when it
should be a general number? If I reformat cells to general number the data
displays ok but I want this to happen automatically. I've tried applying
formatting to the excel spreadsheet template and the query column properties
are set to general number but they still display as dates when I run my code.

Regards
Sue



Stefan Hoffmann said:
hi Sue,
I have a query which selects one record from the current record of a
continuous form. When run normally as a select query this works but I am
trying to use this query via code:

Set rs = db.OpenRecordset("qry CB Control Log Excel", dbOpenSnapshot)
Click to expand...
Really spaces in the name? Then try:

Set rs = db.OpenRecordset("[qry CB Control Log Excel]", _
dbOpenSnapshot)


mfG
--> stefan <--
Click to expand...
 
Thanks Dirk, this works well. The only issue I have now got is the fact that
when copied to Excel some of the data defaults to a Date format when it
should be a general number which I can't figure out why?

Sue


Dirk Goldgar said:
hughess7 said:
Hi all

I have a query which selects one record from the current record of a
continuous form. When run normally as a select query this works but I
am trying to use this query via code:

Set rs = db.OpenRecordset("qry CB Control Log Excel", dbOpenSnapshot)

When run I get the error 'Too few parameters...'.

The query selects data from more than one table and I only want
certain fields including to export the data to Excel.

Can anyone tell me a way round this?

Thanks in advance for any help.
Sue
Click to expand...

Does your query include any form-control references; e.g., a criterion
such as "[Forms]![MyForm]![SomeControl]"? If so, *Access* knows about
the form and control, and can fill in the value of the reference when
you run a query via the user interface, but DAO -- which you are using
to open the recordset -- does not know about them. As far as DAO is
concerned, these are parameters, for which you must supply values.

You can still get Access to fill in these values for you, using the DAO
QueryDef object and a little trick involving the Eval() function. Try
this:

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

Set qdf = db.QueryDefs("qry CB Control Log Excel")

For each prm in qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenSnapshot)


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
hughess7 said:
Thanks Dirk, this works well. The only issue I have now got is the
fact that when copied to Excel some of the data defaults to a Date
format when it should be a general number which I can't figure out
why?
Click to expand...

I'd think it would have something to do either with the Excel automation
or with something in the Excel worksheet (possibly pre-existing date
formatting applied to a column?). But what data type is your query
actually returning? Note that the data type is different from any
formatting that may be applied to the field. Still, if your query is
returning numbers, not dates, and they aren't formatted to look like
dates, the formatting Excel applies shouldn't be caused by anything in
the data itself.
 
Thanks. I think it is the Excel automation causing it. The data it converts
incorrectly to the date format in excel are number fields, the only data it
displays correctly is the text fields. This is the excel code:

With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With

Is there a way to specify the column types?

Thanks in advance for any help.
Sue
 
hughess7 said:
Thanks. I think it is the Excel automation causing it. The data it
converts incorrectly to the date format in excel are number fields,
the only data it displays correctly is the text fields. This is the
excel code:

With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
objSht.Range(conRANGE).CopyFromRecordset rs
End With
Click to expand...

Is this happening when the worksheet didn't previously exist in the
workbook, or when the worksheet already existed? If the former, I'm
puzzled, as I wouldn't expect any formatting to be automatically applied
to a new range in a new workbook. If the worksheet already existed, on
the other hand, maybe the column(s) in question was/were previously
formatted.
Is there a way to specify the column types?
Click to expand...

I imagine you can format the range as part of the Excel automation. I'm
not all that familiar with Excel programming, so I suggest that if the
Excel help doesn't tell you how, you post a question in one of the Excel
groups.
 
It happened in either scenarios, Excel did not know how to display the data
and defaulted to a date format. I looked up formatting in Excel help and
managed to find the property I needed to amend this back to number and all
works well now thanks.

Sue
 
Back
Top