ACC: Exporting a Parameter Query to a Spreadsheet or Text File (124514)

SUMMARY

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.

To export a parameter query, you can eliminate the parameter from the query and instead pass it using an Access Basic function called in the query's Criteria row.

MORE INFORMATION

The following example demonstrates how to export a parameter query:

Open the sample database NWIND.MDB. Create a new query based on the Customers table.
Drag the Company Name and the City fields from the field list to the query grid.
In the Criteria row for the City column, type Find_City(). Save the query as Query1 and then close it.

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

Save the module as Module1 and then close it.

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

Save the macro as Macro1 and then close it.
Select the Macro1 macro in the Database window and then choose the Run button.
When you are prompted "Enter city name," enter London 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.

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