Partager via

Collecting User Input with Queries

  • Article

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

Code Comment 
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


Use THISFORM as a shortcut reference for the currently active form and substitute control names for ControlName1 and ControlName2.

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

Code Comment 
cValue = THISFORM.ControlName.Value
 Define the variable. 
SELECT * ;
   FROM tastrade!customer ;
   WHERE customer.country = cValue ;
   GROUP BY customer.postal_code ;
   ORDER BY customer.postal_code, ;
     customer.company_name
 Use the variable you defined in the SELECT - SQL statement.

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