KB130707 - How to display records that meet a given criteria in a grid on a form in Visual FoxPro

Article ID: 130707

Article Last Modified on 2/12/2007

APPLIES TO

Microsoft Visual FoxPro 3.0 Standard Edition
Microsoft Visual FoxPro 5.0 Standard Edition
Microsoft Visual FoxPro 6.0 Professional Edition
Microsoft Visual FoxPro 3.0b for Macintosh
Microsoft Visual FoxPro 7.0 Professional Edition
Microsoft Visual FoxPro 8.0 Professional Edition
Microsoft Visual FoxPro 9.0 Professional Edition

This article was previously published under Q130707

SUMMARY

This article shows by example how to display only those records that meet a given criteria in a grid on a form.

MORE INFORMATION

To display only those records that meet a given criteria, use one of the following three methods described below.

Method One

If you want to restrict the output to records where status='Y', select the table being displayed in the grid.

Next, use the SET FILTER TO <expression> command, as in this example:

      SELECT <TableName>
      SET FILTER TO status = 'Y'

Method Two

Create a query that selects the required records, and save the query.
Set the grid's RecordSourceType property to 3 - Query and the RecordSource property to the .QPR file for your query.

Method Three

Use a parameterized view. To create a parameterized view, choose View Parameters from the Query menu in the View Designer, or use the CREATE SQL VIEW command with a "?" symbol and a parameter name, as in this example:

   CREATE SQL VIEW customer_data;
      AS SELECT * FROM customer WHERE customer.country = ?cCountry

This example includes all records where the Country field in the Customer table match the value in the cCountry memory variable. If cCountry did not exist at the time the form was created, a dialog box would appear asking for the value of cCountry. To use a parameterized query as the source of data for the grid, set the RecordSource property to 1 - Alias and the RecordSource property to the name of the view.

Method Four

Use a CursorAdapter object. In Visual FoxPro 8.0 and 9.0, use the CursorAdapter Builder or write a CursorAdapter class to limit the grid to the records that you want. Create a form, add a grid that is named grdcursor1, paste the following code example in the Init event of the form, and then run the form.

WITH thisform
.Caption="CursorAdapter and Grid Example"
.left=26
.width=582
.height=375
.autocenter=.t.
endwith
SET EXCLUSIVE OFF
SET MULTILOCKS ON
open database HOME()+"Samples\Data\"+"testdata"
PUBLIC ocursor as Cursoradapter, errorarray
ocursor=CREATEOBJECT('cursoradapter')
WITH ocursor
.alias="testcursor"
.datasourcetype = 'Native'
.Selectcmd="select * from products where unit_cost>20.00"
.tables="products"
IF .cursorfill()=.t.
thisform.grdCursor1.columncount=-1
thisform.grdCursor1.RecordSourcetype=1
thisform.grdCursor1.RecordSource="testcursor"
thisform.grdcursor1.Refresh 
else
AERROR(errorarray)
MESSAGEBOX(errorarray[2])
ENDIF .cursorfill()
endwith

The grid displays all the records where the unit_cost is larger than $20. To change the SELECT-SQL command that returns the records, modify the Selectcmd property of the CursorAdapter object.

For additional information about CursorAdapter objects, click the following article number814184 to view the article814184 in the Microsoft Knowledge Base:

814184 How to update data by using the TableUpdate function with the CursorAdapter object

Additional query words: browse key

Keywords: kbdesigner kbhowto KB130707