Enter Parameter Value when exporting to Excel

The following problem is quite common, I believe, but without a simple solution.

I've create a query which depends on a parameter from the end user (being entered via a form). It runs beautifully.

But on "Export to Excel" (a command that does DoCmd.TransferSpreadsheet) it asks for the parameter value again.

The solution:

dictate the parameter to the query.

That means that instead of placing the parameter in the query, and placing the parameter there (Let's say, SELECT quantity, ..., FROM orders WHERE quantity > Forms!msg_quantity! txt_quantity)

You would place it in the VBA code like that:

Private Sub CreateQuery()

Dim qdf As DAO.QueryDef

Dim sSQL As String

sSQL = "SELECT quantity, ..., FROM orders WHERE quantity > " & Forms!msg_quantity!txt_quantity

Set qdf = CurrentDb.QueryDefs(Me.Form.Caption)

qdf.SQL = sSQL 'This saves the query

qdf.Close

End Sub

It is slower, of course, but if the client really needs exporting to Excel then the client can get it without the unusable "enter parameter value" text box. Trying to hide the text box didn't work in this case.

This way the parameter value is known to the query on export time.