Visual FoxPro: How to Have Two Related Comboboxes in a Grid Control

In this article I want to share my recent solution to a complex problem of having two related comboboxes in a grid control in a Visual ForPro application.

Task Description

A bit of background: I had a complex form with an ActiveX control on the left (custom TreeView control) and a pageframe with many pages on the right. One of the pages was to edit rsTSBlocks table in a grid control. I will also mention that we use SQL Server for back-end although it is not important for the problem I am describing. 

The rsTSBlocks table that is the RecordSource for the grid has two columns LevelId and SectionId. Each level can have many sections, so SectionID depends on selected LevelId.

Original Solution and its Problems

My original solution was using two comboboxes in both Level and Section columns. The sparse property was false for both columns, so the comboboxes were shown for each row in the grid. In order to have second combobox depend on the first I used DynamicStrikeThru property the following way:

I had a method called SetBlocksPage that was called when this page was activated. This method had the following lines:

with .colSections.cboSections
     .rowsource = ''
     .rowsourcetype = 3
     .rowsource = "select * from csrSections where LevelID = ?m.nLevelID into cursor csrSect"
     .parent.controlsource = 'rsTSBlocks.SectionID'
 
  endwith
 
.setall('DynamicFontStrikeThru','thisform.RefreshSeatBlockGrid()')

while RefreshSeatBlockGrid method was the following:

private nLevelID
nLevelID = rsTSBlocks.LevelId
with thisform.pageframe1.pagSeatBlocks.grdSeatBlocks.colSections.cboSections
   .requery()
endwith
return .f.

This solution almost worked. Almost, because I had troubles selecting Section by the mouse when adding a new row and also when I moved to next column and then back to the Section column, I had wrong choices to select from.

New Working Solution

As shown, I needed to find a different solution. I tried many different ideas and finally I found a very helpful message in the UniversalThread forum. In that message Marcia Akins explained how to have unbound column that will still allow to save current value in the row.

So, this is the solution I finally implemented:

The SetBlocksPage method now reads:

with .colSections.cboSections
      .rowsource = ''
      .rowsourcetype = 3
      .rowsource = "select * from csrSections into cursor csrSect where LevelID = ?thisform.currentLevelId nofilter"
*      .parent.controlsource = 'rsTSBlocks.SectionID'
      .parent.controlsource = [(IIF(seek(rsTSBlocks.SectionId, 'csrSections','SectionId'), csrSections.SDescrip,''))]   
   endwith

The column's sparse property is set to default true and the bound property is set to false (in design time in the property sheet).

And I also have the following code in AfterRowColChange method of the grid:

lparameters nColIndex
if this.columns[m.nColIndex].name = 'colSections'
 
    thisform.currentLevelId = rsTSBlocks.LevelId
    local lnSectionId
    lnSectionId = rsTSBlocks.SectionId
    this.colSections.cboSections.requery()
    this.colSections.cboSections.value = m.lnSectionId
endif

This is it. That's all what is needed. Now, when I navigate to the Section column, I only have combobox for the current cell and it opens with the right selections.

Form in Action

I will show how that page looks now:

As you see, the Sections column correctly shows information for all rows as a text and allows to select from correct list of sections that are only used for that particular level.