ACC: Exporting a Parameter Query to a Spreadsheet or Text File

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

NOTE: The problem described below does not occur in Microsoft Access version 7.0. In version 7.0, exporting a parameter query causes the query to prompt for input in the same way it normally would.

This article describes how to use the TransferText or TransferSpreadsheet action in a macro to export a parameter query. (A parameter query is a query that requires input in order to run.) You can export queries without parameters using the TransferText or TransferSpreadsheet macro action using the query's name for the action's Table Name argument. If you try to do this with a parameter query, however, you receive the following error message:

   1 parameters were expected, but only 0 were supplied.

MORE INFORMATION

The following example demonstrates how to export a parameter query:

1. Open the sample database NWIND.MDB. Create a new query based on the Customers table.

2. Drag the Company Name and the City fields from the field list to the query grid.

3. In the Criteria row for the City column, type "Find_City()" (without quotations marks). Save the query as Query1 and then close it.

4. Create a new module and then enter the following sample code in the module:

         Function Find_City()
   

         Find_City = Inputbox("Enter city name")
         ' You could also use "= [Forms]![Customers]![City]" with the
         ' assumption that the Customers form is open and that the City
         ' field contains a value.
         End Function
   

5. Save the module as Module1 and then close it.

6. Create the following new macro:

         Macro Name   Action
         --------------------------------
         Macro1       TransferSpreadsheet
   
         Macro1 Action
         --------------------------------
         TransferSpreadsheet
            Transfer Type: Export
            Table Name: Query1
            File Name: C:\access\test.xls
   
   

7. Save the macro as Macro1 and then close it.

8. Select the Macro1 macro in the Database window and then choose the Run button.

9. When you are prompted "Enter city name," enter "London" (without quotation marks) and then choose the OK button. When you choose OK, a spreadsheet file called TEST.XLS is created in the ACCESS directory on drive C. It contains only records with a city of London.

REFERENCES

For more information about the TransferText macro action, search for "TransferText," and then "TransferText Action" using the Microsoft Access Help menu.