Help with OUTPUTTO

[SF]

Hi,

I have a search form with a few searc criteria, mostly combo boxes, which
working OK. The search form is able to provide result to the listbox on the
form.
The listbox rowsource is derived from the query string (eg SELECT
rptTrainingInvemtoryDatasheet.* FROM rptTrainingInvemtoryDatasheet WHERE
[Province] Like '*Battambang*' and [Topic] Like '*Conflict Resolution*')


Now user want to export the search result to Excel and I add a button on the
form to do this task. I got error from line below as my queryname is
actually a text

DoCmd.OutputTo acOutputQuery, MySQL, acFormatXLS, "TRAINING INVENTORY.XLS",
True

Do I have to create query first in order to export the result?

SF

 

[Douglas J. Steele]

Yes, you need to create a query first: neither the OutputTo nor the
TransferSpreadsheet methods will support just using SQL statements.

Fortunately, it's very straight-forward to create a new query in code,
assuming you've got a reference set to DAO:

Dim qdfNew As DAO.QueryDef

Set qdfNew = CurrentDb.CreateQueryDef("TempQuery", MySQL)
DoCmd.OutputTo acOutputQuery, "TempQuery, acFormatXLS, _
"TRAINING INVENTORY.XLS", True

(although I'd use the TransferSpreadsheet method rather than the OutputTo)

 

[SF]

This would give me error saying that TempQuery already exist. Is there a way
to check and delete existing query first before proceeding the
DOCMD.OUTPUTTO

SF

Yes, you need to create a query first: neither the OutputTo nor the
TransferSpreadsheet methods will support just using SQL statements.

Fortunately, it's very straight-forward to create a new query in code,
assuming you've got a reference set to DAO:

Dim qdfNew As DAO.QueryDef

Set qdfNew = CurrentDb.CreateQueryDef("TempQuery", MySQL)
DoCmd.OutputTo acOutputQuery, "TempQuery, acFormatXLS, _
"TRAINING INVENTORY.XLS", True

(although I'd use the TransferSpreadsheet method rather than the OutputTo)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I have a search form with a few searc criteria, mostly combo boxes, which
working OK. The search form is able to provide result to the listbox on
the form.
The listbox rowsource is derived from the query string (eg SELECT
rptTrainingInvemtoryDatasheet.* FROM rptTrainingInvemtoryDatasheet WHERE
[Province] Like '*Battambang*' and [Topic] Like '*Conflict Resolution*')


Now user want to export the search result to Excel and I add a button on
the form to do this task. I got error from line below as my queryname is
actually a text

DoCmd.OutputTo acOutputQuery, MySQL, acFormatXLS, "TRAINING
INVENTORY.XLS", True

Do I have to create query first in order to export the result?

SF

 

[Douglas J. Steele]

Two options.

One is simply to try and delete the query, and trap the error that gets
raised if the query doesn't exist:

On Error Resume Next

CurrentDb.QueryDefs.Delete "TempQuery"

The other is to write a function that determines whether or not the query
exists, and only try the deletion if it does.

You can use something like:

If DCount("*", " MSysObjects", "[Name] = 'TempQuery' AND Type = -32768")

0 Then

CurrentDb.QueryDefs.Delete "TempQuery"
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This would give me error saying that TempQuery already exist. Is there a
way
to check and delete existing query first before proceeding the
DOCMD.OUTPUTTO

SF

Yes, you need to create a query first: neither the OutputTo nor the
TransferSpreadsheet methods will support just using SQL statements.

Fortunately, it's very straight-forward to create a new query in code,
assuming you've got a reference set to DAO:

Dim qdfNew As DAO.QueryDef

Set qdfNew = CurrentDb.CreateQueryDef("TempQuery", MySQL)
DoCmd.OutputTo acOutputQuery, "TempQuery, acFormatXLS, _
"TRAINING INVENTORY.XLS", True

(although I'd use the TransferSpreadsheet method rather than the
OutputTo)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I have a search form with a few searc criteria, mostly combo boxes, which
working OK. The search form is able to provide result to the listbox on
the form.
The listbox rowsource is derived from the query string (eg SELECT
rptTrainingInvemtoryDatasheet.* FROM rptTrainingInvemtoryDatasheet
WHERE
[Province] Like '*Battambang*' and [Topic] Like '*Conflict
Resolution*')


Now user want to export the search result to Excel and I add a button
on
the form to do this task. I got error from line below as my queryname
is
actually a text

DoCmd.OutputTo acOutputQuery, MySQL, acFormatXLS, "TRAINING
INVENTORY.XLS", True

Do I have to create query first in order to export the result?

SF

 

[SF]

Thank you.

SF

Two options.

One is simply to try and delete the query, and trap the error that gets
raised if the query doesn't exist:

On Error Resume Next

CurrentDb.QueryDefs.Delete "TempQuery"

The other is to write a function that determines whether or not the query
exists, and only try the deletion if it does.

You can use something like:

If DCount("*", " MSysObjects", "[Name] = 'TempQuery' AND Type = -32768")

0 Then

CurrentDb.QueryDefs.Delete "TempQuery"
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

This would give me error saying that TempQuery already exist. Is there a
way
to check and delete existing query first before proceeding the
DOCMD.OUTPUTTO

SF

Yes, you need to create a query first: neither the OutputTo nor the
TransferSpreadsheet methods will support just using SQL statements.

Fortunately, it's very straight-forward to create a new query in code,
assuming you've got a reference set to DAO:

Dim qdfNew As DAO.QueryDef

Set qdfNew = CurrentDb.CreateQueryDef("TempQuery", MySQL)
DoCmd.OutputTo acOutputQuery, "TempQuery, acFormatXLS, _
"TRAINING INVENTORY.XLS", True

(although I'd use the TransferSpreadsheet method rather than the
OutputTo)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

I have a search form with a few searc criteria, mostly combo boxes, which
working OK. The search form is able to provide result to the listbox
on
the form.
The listbox rowsource is derived from the query string (eg SELECT
rptTrainingInvemtoryDatasheet.* FROM rptTrainingInvemtoryDatasheet
WHERE
[Province] Like '*Battambang*' and [Topic] Like '*Conflict
Resolution*')


Now user want to export the search result to Excel and I add a button
on
the form to do this task. I got error from line below as my queryname
is
actually a text

DoCmd.OutputTo acOutputQuery, MySQL, acFormatXLS, "TRAINING
INVENTORY.XLS", True

Do I have to create query first in order to export the result?

SF

 

[Tom Wickerath]

Instead of creating the query each time, another option is to set the
querydef to an existing saved query:


Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strPath As String

Set db = CurrentDB()
Set qdf = db.QueryDefs("YourSavedQueryName")
strPath = CurrentProject.Path
:
:
qdf.sql = MySQL

DoCmd.TransferSpreadsheet TransferType:=acExport, _
TableName:="YourSavedQueryName", _
FileName:=strPath & "\TRAINING INVENTORY.XLS", _
HasFieldNames:=True

MsgBox "The selected records have been exported " _
& "to the file TRAINING INVENTORY.XLS" & vbCrLf _
& "in the folder:" & vbCrLf & strPath, _
vbInformation, "Export Complete..."


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

This would give me error saying that TempQuery already exist. Is there a way
to check and delete existing query first before proceeding the
DOCMD.OUTPUTTO

SF

Yes, you need to create a query first: neither the OutputTo nor the
TransferSpreadsheet methods will support just using SQL statements.

Fortunately, it's very straight-forward to create a new query in code,
assuming you've got a reference set to DAO:

Dim qdfNew As DAO.QueryDef

Set qdfNew = CurrentDb.CreateQueryDef("TempQuery", MySQL)
DoCmd.OutputTo acOutputQuery, "TempQuery, acFormatXLS, _
"TRAINING INVENTORY.XLS", True

(although I'd use the TransferSpreadsheet method rather than the OutputTo)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I have a search form with a few searc criteria, mostly combo boxes, which
working OK. The search form is able to provide result to the listbox on
the form.
The listbox rowsource is derived from the query string (eg SELECT
rptTrainingInvemtoryDatasheet.* FROM rptTrainingInvemtoryDatasheet WHERE
[Province] Like '*Battambang*' and [Topic] Like '*Conflict Resolution*')


Now user want to export the search result to Excel and I add a button on
the form to do this task. I got error from line below as my queryname is
actually a text

DoCmd.OutputTo acOutputQuery, MySQL, acFormatXLS, "TRAINING
INVENTORY.XLS", True

Do I have to create query first in order to export the result?

SF