HOWTO: Create a Control to Select Fields from Different Tables

ID: Q147657

The information in this article applies to:

Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 6.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 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.

Additional query words:

Keywords          : kbcode kbOOP kbVFp300 kbVFp500 kbVFp600 
Issue type        : kbhowto

Last Reviewed: October 31, 1998