Collecting User Input with Queries

If you want to collect values from a form, you can use variables in a SELECT – SQL statement and then use them immediately in the statement, or execute the statement later.

To collect values for immediate use, you can either explicitly name the form or use a shortcut reference for the form in your SELECT – SQL statement. In this example, shortcut reference is in the WHERE clause.

Collecting Values Using Shortcut References in a SELECT - SQL Statement

SELECT * ;
   FROM tastrade!customer ;
   WHERE customer.country = ;
    THISFORM.ControlName1.Value ;
   AND customer.region = 
THISFORM.ControlName2.Value ;
   GROUP BY customer.postal_code ;
   ORDER BY customer.postal_code, 
customer.company_name

If you don't want to use references to the control, you can define variables in the code. Use code variables if you want to store the values from a form but don't necessarily expect to use them while the form is active.

Collecting values for later use

cValue = THISFORM.ControlName.Value

SELECT * ;
   FROM tastrade!customer ;
   WHERE customer.country = cValue ;
   GROUP BY customer.postal_code ;
   ORDER BY customer.postal_code, ;
     customer.company_name

If you don't define the variable before you run the query, an error message appears stating that the variable couldn't be found. If the variable isn't defined in code, Visual FoxPro assumes that the variable is pre-initialized.

See Also

Integrating Queries and Reports | Saving a Report as HTML | Adding Reports and Labels | SELECT - SQL