Storing user entered criteria in a variable

[Talonjpc]

Here is my problem:

I have a filter with the criteria [Enter Software Name:] that opens a report based on what the software name is. It then tell you which computers have this software on it. If the Software is not installed on any servers or does not exist, the report has no data, which is fine, because my OnNoData field prevents it from opening.

My problem is: I want a message box telling the person who entered the software that it does not exist if it doesn't, but if it does exist, but isn't installed on any systems, to tell them that. The problem is, I don't know how to store what they type in for criteria as variable. Does anyone know how to do this?

Thanks,
Vinay

 

[DaveNewman]

Vinay,
You can write a bit of code to accept the software name from the user and save it in a variable. Use the variable to fill the parameter from the query.
I assume you have a canned query or filter that runs when you open the report. You can put the code in the OnOpen event of the report and edit the SQL in the query/filter. To do that, it's easier if the query is saved as a query, but could also be coded in the data source of the report.

I'll give an example, but look in the VBA forum for how to get to the SQL and put it back into the query. The OnOpen event runs before the query/filter is run so it is ok the change it at that time.

a sample: (pulled from memory, I might be off a bit)
canned query "query1"
sql = "select * from programs where program_name = 'Excel'"
Note that the sql is there for saving purposes only.

dim db as database
dim qd as querydef
dim SQL$
dim rsp$

set db as currentdb()
set qd = db.openquerydef("query1&quot
SQL$ = "select * from programs where program_name = '"
rsp$ = inputbox("Enter Software Name:&quot
SQL$ = SQL$ & rsp$ & "'"
qd.sql = SQL$

Best of Luck!
Dave