How To Create a Control to Select Fields from Different Tables

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0

SUMMARY

This article shows by example how to create a new control that will select fields from a variety of different tables. Within a grid-like environment, this control allows you to select a table from a pre-defined directory and select a field from that table. Multiple tables can be selected and entries can be deleted. The user can also enter their own field value, which will be added to that table's field list.

MORE INFORMATION

The control takes its list of .dbf tables from an array property of the form. The user's selection of tables and fields are held in a cursor named crChosen, which can be used after the selection process.

Steps to Create New Class

1. Type the following command in the Command window to create a container class and modify it in the Visual Class Designer:

   CREATE CLASS fieldSelect AS container OF fselect.vcx

2. In Properties window, set the following properties:

   Width: 305 Height: 95 BackColor: 192,192,192 BorderWidth: 0

3. Add the following properties. On the Class menu, click New Property, and then add the properties.

   ncurrentrow nlines

4. Add a grid to the container and set its properties to:

   ColumnCount: 2 BackColor: 192,192,192 DeleteMark: .F. Height: 97 Left: 53 RecordMark: .F. RecordSource: crChosen ScrollBars: 2 Top: -1 Width: 251

   Select the Column1 object from the properties sheet, and set the following properties:

   FontName: Microsoft Sans Serif FontSize: 8

   Select the Colummn2 object from the properties sheet, and set the following properties:

   FontName: Microsoft Sans Serif FontSize: 8

5. Select the Column1 object in the properties sheet, and then select the ComboBox tool from the toolbox. Click the first column of the grid, and drag out a small box to add the combo box to the column. Set the following properties of the combo box:

   Name: TableCombo FontName: Microsoft Sans Serif FontSize: 8 BackColor: 192,192,192 RowSourceType: 5 RowSource: THISFORM.aTableList

6. Select the ComboBox tool from the toolbox again, and drag out a small box in the second column of the grid. Set the following properties of this combo box:

   Name: FieldCombo FontName: Microsoft Sans Serif FontSize: 8 BackColor: 192,192,192 RowSourceType: 1

7. Add a command button to the container, and set its properties to these values:

   Top: -1 Left: 2 Height: 19 Width: 49 Caption: Add Enabled: .F.

8. Add another command button to the containe,r and set its properties to these values:

   Top: 22 Left: 2 Height: 19 Width: 49 Caption: Delete Enabled: .F.

9. On the Class menu, click New Method. Enter checkchange as the name. From the properties sheet, select the fieldselect object. Now, near the bottom of the sheet you will see your new method. Double-click it, and add the following code:

   IF THIS.Grid1.ActiveRow <> THIS.nCurrentRow

        THIS.Grid1.Tag='Row Changed'
        THIS.nCurrentRow=THIS.Grid1.ActiveRow
   

   ENDIF

   IF ! EMPTY(Key_Field)

        THIS.Command1.Enabled=.T.
   

   ENDIF

10. In the Init event of fieldselect, enter this code:

    CREATE CURSOR crChosen (Table_Name C(12),Key_Field C(100),;

          UserExpr L(1),Index N(3))
    

    APPEND BLANK SELECT crChosen REPLACE UserExpr WITH .F. THIS.nCurrentRow=1 THIS.nLines=1

11. On the Class menu, click New Method. Enter reeval as the name, and add

        this code:
    

        THIS.Grid1.RecordSource='crChosen'
        THIS.Grid1.Column1.TableCombo.Requery
    

12. In the Init event of the grid, enter this code:

        THIS.Tag='Same Row'
    

        THIS.RowHeight=25
        THIS.Column1.Width=120
        THIS.Column2.Width=110
    

        THIS.Column1.CurrentControl='TableCombo'
        THIS.Column1.TableCombo.Visible=.T.
        THIS.Column1.Header1.Caption='Table Name'
        THIS.Column2.CurrentControl='FieldCombo'
        THIS.Column2.FieldCombo.Visible=.T.
        THIS.Column2.Header1.Caption='Expression'
    

13. In the GotFocus event of the TableCombo object, enter this code:

        THIS.PARENT.PARENT.PARENT.CheckChange
    

        IF EMPTY(crChosen.Table_name)
          REPLACE crChosen.Table_name WITH THIS.ListItem(1)
          THIS.DisplayValue=THIS.ListItem(1)
        ENDIF
    

14. In the GotFocus event of the FieldCombo object,enter this code:

        THIS.PARENT.PARENT.PARENT.CheckChange
    

        IF ! EMPTY(THIS.Value)
           IF THIS.PARENT.PARENT.Tag='Same Row'
             m.Remember=THIS.ListIndex
           ENDIF
        ENDIF
    
        * Keep current selection
        IF ! EMPTY(THIS.DisplayValue)
           THIS.Value = THIS.DisplayValue
        ENDIF
    
        m.cChildTab=crChosen.table_name
        USE (m.cChildTab) ALIAS ChildTab IN 0
        SELECT ChildTab
        nCFieldCount=AFIELDS(aDummy)
    
        * Remove existing combo elements
        m.nCurCount=THIS.ListCount
        FOR i = m.nCurCount TO 1 STEP -1
           THIS.RemoveItem(i)
        NEXT i
    
        * Add new elements
        FOR i = 1 TO nCFieldCount
           THIS.AddItem(aDummy[i,1])
        NEXT i
        * If User-defined expression - add to combo field list
        IF crChosen.UserExpr
           THIS.AddItem(crChosen.Key_Field)
        ENDIF
    
        USE
        SELECT crChosen
    
        IF ! EMPTY(THIS.Value)
           IF THIS.PARENT.PARENT.Tag='Same Row'
             THIS.ListIndex=m.Remember
           ELSE
             THIS.ListIndex=crChosen.Index
           ENDIF
        ELSE
           THIS.ListIndex=1
           REPLACE crChosen.Key_Field WITH THIS.ListItem(THIS.ListIndex)
        ENDIF
    
    

15. In the Valid event of the FieldCombo object, enter this code:

        * Changing Key_Field from User-defined to one off the list
        IF THIS.DisplayValue <> crChosen.Key_Field .AND. ;
           crChosen.UserExpr = .T.
            REPLACE crChosen.UserExpr WITH .F.
        ENDIF
    
        REPLACE crChosen.Key_Field WITH THIS.DisplayValue
    
        * User-defined expression ?
        IF THIS.ListIndex = 0
          THIS.AddItem(THIS.DisplayValue)
          REPLACE crChosen.Index WITH THIS.ListCount
          REPLACE crChosen.UserExpr WITH .T.
        ELSE
          IF ! crChosen.UserExpr
            REPLACE crChosen.UserExpr WITH .F.
          ENDIF
          REPLACE crChosen.Index WITH THIS.ListIndex
        ENDIF
    
        THIS.PARENT.PARENT.Tag='Same Row'
    
    

16. In the Click event of the Command1 object, enter this code:

        THIS.PARENT.grid1.ActivateCell(recno(),1)
        THIS.PARENT.grid1.column1.tablecombo.setfocus
        THISFORM.Refresh
        APPEND BLANK
        THIS.PARENT.grid1.ActivateCell(reccount(),1)
    

        THIS.PARENT.Grid1.Tag="Row Changed"
    

        THIS.PARENT.Command2.Enabled=.T.
        THIS.PARENT.nLines = THIS.PARENT.nLines + 1
    

17. In the Click event of the Command2 object, enter this code:

        DELETE
        THIS.PARENT.nLines = THIS.PARENT.nLines - 1
        * Disable minus button if 1 line left
        IF THIS.PARENT.nLines=1
          THIS.Enabled=.F.
        ENDIF
    

        THIS.PARENT.grid1.ActivateCell(IIF(recno()>1,recno()-1,1),1)
        THISFORM.Refresh
        THIS.PARENT.grid1.column1.tablecombo.setfocus
        THIS.PARENT.Grid1.Tag="Row Changed"
    

18. Save the class.

Steps to Use New Class

1. Create a new form. Set the BackColor property to 192,192,192. From the Form menu, click New Property, and type:

   aTableList[2]

2. In the Load event of the form, enter this code:

   SET DELETED ON SET DEFAU TO sys(2004)+"samples\data" THISFORM.aTableList[1]="customer.dbf" THISFORM.aTableList[2]="orders.dbf"

3. In the Activate event of the form, enter this code:

   THIS.FieldSelect1.reeval

4. Using the form controls toolbar, click the View classes button, and select Add. Add fselect.vcx. Select the fieldselect tool, and drop it on the form.

5. Run the form.