Using an SQL cursor in a VFP listbox or combobox

You might want a form with a combo box or a list box so that the user can easily select an entry from a table. Both these controls can be populated in a variety of ways but if you need them to show the live data from a table then an SQL cursor is the technique to use.

This SQL statement below:

creates a cursor named csrCustID holding the customer ID values from the customer table in alphabetical order of customer ID. Using this cursor as the source of the rows is just a matter of setting the properties of the combo or list. Go to the Properties Window for the control, select 2-Alias as the RowSourceType and enter the name of the cursor as the RowSource of the control.

Load and Init events

This technique is quick and simple and ensures that the combo box is always filled with the most up-to-date information. The only point to watch for is to make sure that you create the cursor in the Load event of the form rather than in the Init event. This is important because of the order in which FoxPro fires its form events. The Load happens first, then the Init methods of all the controls fire and only then does the Init of the form run. The RowSource has to exist before the control can initialise itself so it must be created in the Load event.

The cursor uses the Customer table as the source of its data so it's sensible to open the table in the Load event too. You can close the Customer table as soon as the cursor has been created unless it is going to be needed later. The cursor must exist for the life of the form so close it in the form Unload event. Use the Use command each time