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
Version :
Platform :
Issue type : kbhowto


Last Reviewed: August 16, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.