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.
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
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.