Pass Query Parameter From Module

[Jeefgeorge]

I have a module which exports a query to excel. The query is most often run
from the click of a button on a form - in this case the _click runs the code
which in turn runs the query. The query's criteria is set to
Forms!GenerateEstimate!ProjNo. Have added a button to the switchboard which
I would like to run the same code. I have it set up and it works properly
but the user is promped for the Forms!GenerateEstimate!ProjNo. I would like
this to appear as "Enter a Project Number".

Also, the project number is in the format ######-####, in other areas of my
database the user can input the entire number or just the last four digits to
return a value, i.e. Right(GenerateEstimate!ProjNo,4).

 

[Arvin Meyer [MVP]]

There should be no prompt if the [Forms]![GenerateEstimate]![ProjNo] is
recognized. Make sure the names are correct and the Square Brackets are in
the query.

 

[Dirk Goldgar]

(re-posting, as my original reply hasn't appeared)

I have a module which exports a query to excel. The query is most often
run
from the click of a button on a form - in this case the _click runs the
code
which in turn runs the query. The query's criteria is set to
Forms!GenerateEstimate!ProjNo. Have added a button to the switchboard
which
I would like to run the same code. I have it set up and it works properly
but the user is promped for the Forms!GenerateEstimate!ProjNo. I would
like
this to appear as "Enter a Project Number".

There are several ways to go about this. If you don't want to create two
separate queries, one for exporting from the form, and one for exporting
without the form, then the approach that seems simplest to me is to prompt
the user for the parameter value, then use code to open the form hidden,
stuff the supplied value into the control on the form, then export the
query. Something like this:

'----- start of air code -----
Dim strProjNo As String

strProjNo = InputBox("Enter a Project Number")

if Len(strProjNo) > 0 Then

DoCmd.OpenForm "GenerateEstimate", WindowMode:=acHidden

Forms!GenerateEstimate!ProjNo = strProjNo

DoCmd.TransferSpreadsheet _
acExport, acSpreadsheetTypeExcel9, _
"YourQueryName", _
"YourOutput.xls"

DoCmd.Close acForm, "GenerateEstimate", acSaveNo
End If
'----- end of air code -----