Collecting User Input with Queries

If you want to collect values that the application user enters from a form, you can create variables to store those values and then use them immediately in the SQL SELECT statement or execute the statement later. For more information about customizing queries using SQL SELECT statements, see Customizing Queries Using SQL SELECT Statements and SELECT - SQL COMMAND.

To collect user input values for immediate use

• In the SQL SELECT statement, include the name of the form or a reference to the form where appropriate.

The following example illustrates how to collect user input values from a form using a shortcut reference that appears in the WHERE clause of the SQL SELECT statement. The shortcut reference, THISFORM, refers to the currently active form. You should substitute control names for ControlName1 and ControlName2.

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

To collect user input values for later use

To store user input values from a form when you do not need to use them while the form is active, use variables in code. For example, if you do not want to use references to a control, you can define variables in the code to store the values returned by the control.

1. Define the variable you want to use for storing the user input value.
2. In the SQL SELECT statement, include the name of the variable where appropriate.

The following example illustrates how to collect user input values from a form using the variable, cValue, to store the value returned from a control on the currently active form. The shortcut reference, THISFORM, refers to the currently active form. The variable appears in the WHERE clause of the SQL SELECT statement to select the country that matches cValue.

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

Working with Queries | Customizing Queries Using SQL SELECT Statements | Creating Queries | Integrating Queries and Reports | Saving a Report as HTML | Creating Reports and Labels