Consultancy and Programming
Advice, tips, techniques and downloads for Visual Foxpro developers.
NOTE: For a general introduction to grids, see Understanding the Visual FoxPro grid control.
Let's suppose you want to create a FoxPro form like the one in Figure 1. As you can see, this uses a grid to dispay data from a Products table. Users can control the contents of the grid in the following ways:
After making these selections, the user presses the Refresh button. The data in the grid then changes to reflect the user's choices.
Sounds straightforward? So how would you go about creating this form?
Your first thought might be to access the data via a local view. That sounds reasonable, given that VFP lets you modify the contents of a view by parameterising it. You would do that in the view's WHERE clause, like so:
WHERE Products.Category = ?lcCat
Here, lcCat is a variable which holds the user's choice of category. If you then specify the view as the grid's RecordSource, the data will be filtered by the required category each time the view is requeried. The call to the REQUERY() function would go in the Click event of the form's Refresh button.
That's fine as far as filtering is concerned. But you can't parameterise the choice of fields to diplay in a given column or the view's sort order. It is possible to re-create the entire view programmatically each time the user presses the Refresh button, but that's not a particularly elegant solution. Is there a simpler way?
Using a SQL SELECT statement to create a cursor sounds more promising. With not too much difficulty, you can write a SELECT which respects the user's three choices, and which generates a cursor which can be used as the grid's RecordSource.
Let's assume that we have already set up the following variables:
The code in the Refresh button might then look something like this:
SELECT product_id,; IIF(llEnglish,eng_name,prod_name) AS descript,; unit_price, in_stock ; FROM Products ; WHERE ALLTRIM(Category) = ALLTRIM(lcCat) ; ORDER BY &lcOrder INTO CURSOR csrTemp THISFORM.refresh
The SELECT statement sends the required data to a cursor, csrTemp. This is the RecordSource for the grid, so after the form has been refreshed, the grid should show exactly the data that the user requires. Problem solved.
Unfortunatetly, it's not quite that simple. If you were to create this form and run it, the SELECT would produce the correct data, but the grid would appear as an empty rectangle. No data at all would be visible.
The reason for this behaviour is not hard to see. Whenever you use SELECT to create a cursor in this way, Visual Foxpro first destroys the existing cursor (if any), then builds an entirely new one. This is very upsetting for the grid, which doesn't like to have its RecordSource taken away from it - even though it's only gone for a moment. Because the controls within the grid are bound to the cursor, destroying the cursor will rob the grid of the controls, which is why you see the empty rectangle.
Would it make any difference if we used a physical table rather than a cursor for the output of the SELECT? No, it would make no difference at all.
However, once you understand what's going on, it's not hard to devise a solution. The trick is to create a second cursor to use as the RecordSource, and to move the data from the first cursor (the one created by the SELECT) to the new one whenever the user wants to update the grid.
We will put the following code in the form's Load event:
CREATE CURSOR csrProducts ; ( product_id C(6), descript C(40), ; unit_price N(6,2), in_stock N(6) )
This will create a cursor, called csrProducts, with the same structure as the one generated by the SELECT. Make this cursor the RecordSource for the grid.
In the Refresh button, keep the SELECT that you created earlier, but add some further code to copy the contents of the cursor which that SELECT generates (csrTemp) into the new cursor (csrProducts). That code will now look like this:
SELECT product_id, ; IIF(llEnglish,eng_name,prod_name) AS descript,; unit_price, in_stock ; FROM Products ; WHERE ALLTRIM(Category) = ALLTRIM(lcCat) ; ORDER BY &lcOrder INTO CURSOR csrTemp SELECT csrProducts ZAP APPEND FROM DBF("csrTemp") THISFORM.refresh
The effect of this is to copy the results of the SELECT into csrProducts. After the form is refreshed, the data will be correctly displayed in the grid.
Note that you cannot use the COPY TO command to transfer the data to csrProducts, as that command always creates a new file. Instead, you need to zap the existing contents of csrProducts, than append the new data. Note also the use of the DBF() function. This is needed because the APPEND FROM command can only copy data from a physical table. DBF() returns the path and name of the real file that holds the cursor.
One final detail needs attending to. You probably want the grid to display some initial data when the form first appears. This data would be based on the default values for the three user choices. To achieve that, simply add code to the form's Init event to do the SELECT and to append the results into csrProducts. Of course, you will also need some code to set up the three variables used by the select (lcCat, llEnglish and lcOrder), but we will leave that as an exercise for the reader.
The technique which we have described here is based in part on information from that very useful book, 1001 Things You Always Wanted to Know About Visual FoxPro, by Marcia Akins, Andy Kramek and Rick Schummer (Hentzenwerke, 2000). You can read a review of this book on our Books page, or find further information about it at Amazon.com.
Mike Lewis Consultants Ltd. September 2001
FoxStuff is maintained by Mike Lewis Consultants Ltd. as a service to the VFP community. Feel free to download and use any code or components, and to pass around copies of the articles (but please do not remove our copyright notices or disclaimers).
The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly in your applications.
© Copyright Mike Lewis Consultants Ltd.