RecordSource, RecordSourceType

These two properties determine what table or cursor provides the data for a grid. RecordSourceType, which tells the grid what kind of object to get its value from, offers the expected choices plus one really strange one. RecordSource identifies the particular item that provides the grid data.

Usage

grdGrid.RecordSource = cSource
cSource = grdGrid.RecordSource
grdGrid.RecordSourceType = nSourceType
nSourceType = grdGrid.RecordSourceType

A grid can be based on a table, an alias, a query, a SQL statement or a “prompt.” The list should raise several questions. We’ll answer them before you can ask.

The difference between “Table” and “Alias” is that “Table” opens the table automatically, while “Alias” expects to find it open.

“Query” takes a QPR file (such as created by the Query Designer) and fills the grid with the results. The file’s extension must be QPR, regardless of whether you use the QD or write the code yourself. (This strikes us as poor design.) “SQL Statement,” like the similar choice for combos and lists, lets you type a query directly into the RecordSource. This option was added in VFP 5. With both query choices, make sure the query results are directed somewhere (the easiest is into a junk cursor) or the default Browse pops up before the grid gets filled. The query choice for grids is much less flexible than that for combos and lists because grids have no Requery method. If you want to re-execute the query to refill the grid, you have to reset RecordSource to the file or command, but that throws away any changes you’ve made to the grid columns, including their ControlSources. (See below for more on this.) A better choice than either “Query” or “SQL Statement” is to create a view with the data you want and base the grid on that.

The final choice, “Prompt,” is pretty odd. We suppose it’s meant to give you the functionality you get when you issue a BROWSE and no table is open in the current work area. Since we generally do that only by accident, we can’t see why grids need to do it. “Prompt” behaves differently depending on the situation. First, if there’s a table open in the current work area, it’s used as the grid’s RecordSource and no dialog appears at all. This is the same behavior you get if you leave RecordSourceType set to 1-Alias and RecordSource blank.

With RecordSourceType set to 2-Prompt, if no table is open in the current work area you're prompted to choose one, but if the table you choose is already open in another work area, the grid doesn't get filled at all—it consists of just a thin black outline. We can't think of any situation in which that's the desired result.

If you get prompted and the table isn’t already open, it’s opened and used for the grid.

We were pleased by the addition of SQL Statement to the list, but we’re still hoping the list of RecordSourceTypes will be enhanced down the road. The one we’d really like to see there is Array, but we bet the creative folks who gave us 10 choices for list and combo RowSourceType can come up with some more, too.

Once you’ve chosen a RecordSourceType, filling in RecordSource is pretty simple.

If you do something that closes the RecordSource of a grid (like rerun the query that created it), the grid clears out. All you have is a rectangle in the grid's BackColor. If you re-create the cursor or reopen the table, you have to reset RecordSource to the cursor (or SQL statement) to refill the grid, but any column-level changes you've made (like header captions or column widths or ColumnCount) are lost for good. This is another case where a view is a better choice.

There is a work-around (read: trick) you can use to prevent all your changes from being lost. Instead of just resetting RecordSource, first set it to the empty string, then do whatever table manipulation you need to do (close and reopen a table, run a query, whatever), then reset RecordSource to its original value. However, if you’ve set the ControlSources of the individual columns to something other than the default (that is, the same-numbered field in the table), you have to save the ControlSources and restore them afterward, too.

Example

This.RecordSourceType = 0 && Table
This.RecordSource = "Employee.DBF"

See Also

Grid, RowSource, RowSourceType