How To Create a Control to Select Fields from Different Tables

MORE INFORMATION

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 container 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.