Microsoft KB Archive/147657

= How To Create a Control to Select Fields from Different Tables =

Article ID: 147657

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 6.0 Professional Edition

-



This article was previously published under Q147657



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
  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  In Properties window, set the following properties:

Width: 305

Height: 95

BackColor: 192,192,192

BorderWidth: 0

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

ncurrentrow

nlines

 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

 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

 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

 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.

</li> 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.

</li>  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 </li>  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 </li>  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 </li>  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' </li>  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 </li>  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 </li>  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' </li>  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 </li>  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" </li> Save the class.</li></ol>

Steps to Use New Class
<ol>  Create a new form. Set the BackColor property to 192,192,192. From the Form menu, click New Property, and type: aTableList[2] </li>  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" </li>  In the Activate event of the form, enter this code: THIS.FieldSelect1.reeval </li> 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.</li> <li>Run the form.</li></ol>

Keywords: kbhowto kboop kbcode KB147657

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.