Macro to Export Query Results deletes query itself

[Becca]

I am having trouble with a macro which is intended to
export the results of the query. It simply uses
an "Export To" function to export to excel.

The first time the macro runs, it works fine. The problem
is, it (sometimes) seems to delete the criteria for the
query itself when it runs. So that the second time I try
to run the macro, it gives me an error message that
the "Query must have at least one destination field." And
when I go to view the query in design or SQL, it is, in
fact, emptied out.

Any thoughts at all? I'm not sure how to fix this.

Thanks,

Becca

 

[Steve Schapel]

Becca,

Could you please post back with full details of the macro you are
using, and also which version of Access. I am not familiar with an
Export To command, so could you please check this.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Steve,

Sorry, I'm working in Access 2000...Here are the full
details:

The macro is an OutputTo macro, with the following
parameters:

Object Type - Query
Object Name - QRY_EXPORT
Output Format - Microsoft Excel
Output File - NONE
Auto Start - Yes
Template File - NONE

The query runs well, and the macro works. The problem is
that sometimes (not consistently), the query seems to
empty itself of parameters, so that I get that "query must
have at least one destination field" error message, and I
have to rewrite the query. The query is as follows (in
SQL):

SELECT TBL_STAGE*
FROM TBL_STAGE
WHERE (((TBL_STAGE.[SELECTED?])=Yes));

The function of the macro is to take only data from a form
where a check box is selected, and export to an excel file.

Any help would be greatly appreciated. Thanks!

--Becca

 

[Steve Schapel]

Becca,

I am sorry, I can't really spot what might be causing the problem here.
So do you mean that when this problem occurs, if you go back to try
and open QRY_EXPORT in design view, it is just completely blank... the
table is gone from the top panel and the grid is empty? But anyway,
just try these changes. First of all, put all the fields from the table
into your query. It is not a good idea to use TBL_STAGE.* (I presume
this is what you have, rather than TBL_STAGE* as you put it in your
post? ... just a hint for the future, if you are going to quote SQL or
code in a newsgroup post, copy/paste from your database Second,
change your criteria in the SELECTED? field from Yes to -1. Third, if
this macro is running from a form where you are editing the SELECTED?
field, try explicitly saving the data, using a RunCommand, SaveRecord
action in your macro prior to the OutputTo action.

None of the above is to correct anything that is technically 'wrong' in
what you did, just tidy up.

As an aside, I should also mention that it is not a good idea to use a ?
as part of a the name of a field or control or database object.

- Steve Schapel, Microsoft Access MVP

 

[alfcee]

I think there is no problem with the macro. I have reproduced the error just using the export button in the menu.

 

[aliciawangwu]

The way to work around this error is if you run the macro or export your queries while your queries are opened in Access.

If you export your queries into excel files while the queries are closed, somehow access will empty the queries.