execute code on query

[alex]

Hello all,

using Access '03...

Thanks to the help of this group, I have some VBA code to export the
results of a Query to Excel.

Dim Where As String
Where = InputBox("Path and File Name")
If Right(Where, 4) = ".xls" Then
Else
Where = Where & ".xls"
End If

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97,
"200805_Report", Where, True

Unlike a Form, however, which I coded before; i'm lost on how to code
a Query to spit its results to Excel upon execution; i.e., perform the
VBA code when I double click on the query without using a switchboard
or other command button.

Where can I place this code (or other code) to run when a query loads/
executes?

Thanks,
alex

 

[Mark]

Queries don't have events you can hook into in this way. You can reference
functions in queries but they will get executed for every row so normally
used for data manipulation.

You will need to trigger your code from somewhere and a button on a form or
toolbar seems appropriate.

The alternative is to hook into an event when the database starts (via
AutoExec macro calling your code) or when a certain form loads. *Something*
has to trigger your code.

 

[Rick Brandt]

Queries don't have events you can hook into in this way. You can
reference functions in queries but they will get executed for every
row so normally used for data manipulation.

Actually, as long as the function does not use a field from one of the
tables as an argument it will fire only once when the query is opened rather
than once per row.

I don't know if that behavior is 100% consistent in all circumstances and I
also don't know if it would work for what the OP is describing, but I tested
it to see if one could create a pseudo-event for a query. Initial testing
indicates that it can work.

 

[alex]

Actually, as long as the function does not use a field from one of the
tables as an argument it will fire only once when the query is opened rather
than once per row.

I don't know if that behavior is 100% consistent in all circumstances and I
also don't know if it would work for what the OP is describing, but I tested
it to see if one could create a pseudo-event for a query.  Initial testing
indicates that it can work.

Thank you Mark and Rick for your help...
In a nutshell: I need to create some kind of trigger to execute the
Access export to Excel!
That helps, and I should be able to create a toolbar macro (using the
code above) to accomplish this.

Thanks again,
alex