Listing field names

If you want a list box or combo box to list the names of fields in a table or query, set the control's Row Source Type property to Field List and set its Row Source property to the name of the table or query that contains the fields whose names you want to list.

For example, Figure 10-3 shows a ComboBox control named FldNameCombo on a form. As you can see in the Properties sheet, its Row Source Type is set to Field List, and its Row Source is set to Customers. The names in the control's drop-down menu (CustID, FirstName, LastName, and so forth) are field names from a table named Customers.

From a VBA standpoint, if you want the FldNamesCombo control to show field names from a different table or query in response to some event, change the control's .RowSource property to the name of the table or query from which you want the control to get field names. For example, this statement sets the Row Source property of the control named FldNamesCombo to a table named Products (so the control shows field names from the Products table):

Me!FldNamesCombo.RowSource = "Products"

JttNG/

The Me! in these examples refers to the form to which the control is attached and works only from a class module. From a standard module, Me! would have to be replaced with the full identifier for the open form — for example

Forms![EzQueryFrm]![FldNamesCombo].RowSource = "Products"

if the control is on an open form named EZQueryFrm.

Figure 10-3:

Row Source properties for a Field List combo box.

FldNameCombo

FldNameCombo

Figure 10-3:

Row Source properties for a Field List combo box.

Row Source properties

In your code, you can take extra steps to make sure that the control's Row Source Type is set correctly to Field List prior to putting in the new table name. After the field receives its new list, you can use the statement

Me!FldNamesCombo.Value = Me!FldNamesCombo.ItemData(0)

to set the selected option in a combo box to the first item in the drop-down list.

'Make sure the control's Row Source Type is Field List. Me!FldNamesCombo.RowSourceType = "Field List"

'Change the Row Source table to Products table. Me!FldNamesCombo.RowSource = "Products"

'Set selected combo box item to first item in drop-down menu. Me!FldNamesCombo.Value = Me!FldNamesCombo.ItemData(0)

Using the keyword Me! in the preceding examples assumes that the code is in the class module for whatever form the FldNamesCombo control is on. To change the FldNamesCombo properties from a standard module or another form's class module, include the complete identifier for the open form. For example, if the FldNamesCombo control is on a form named EZQueryFrm, the complete identifier for the form is Forms![EzQueryFrm]! rather than Me! The complete identifier to the FldNamesCombo control is Forms![EzQueryFrm]! [FldNamesCombo].

In code, you could spell out the complete identifier in each line of code, as follows:

'Make sure the control's Row Source Type is Field List. Forms![EzQueryFrm]![FldNamesCombo].RowSourceType = "Field List"

'Change the Row Source table to Products table. Forms![EzQueryFrm]![FldNamesCombo].RowSource = "Products"

'Set selected combo box item to first item in drop-down menu. Forms![EzQueryFrm]![FldNamesCombo].Value _

= Forms![EzQueryFrm]![FldNamesCombo].ItemData(0)

To avoid typing Forms![EzQueryFrm]![FldNamesCombo] repeatedly in your code, define a Control object variable that refers to the control through a shorter name.

'Make short name MyControl refer to 'Forms![EZQueryFrm]![FldNamesCombo] Dim MyControl As Control

Set MyControl = Forms![EZQueryFrm]![FldNamesCombo]

'Make sure the control's Row Source Type is Field List. MyControl.RowSourceType = "Field List"

'Change the Row Source table to Products table. MyControl.RowSource = "Customers"

'Set selected combo box item to first item in drop-down menu. MyControl.Value = MyControl.ItemData(O)

For example, the first line of the preceding code (Dim MyControl As Control ) defines a new, empty Control object variable named MyControl . The second line

Set MyControl = Forms![EzQueryFrm]![FldNamesCombo]

makes the short name MyControl refer specifically to the control named Fld NamesCombo on the form named EZQueryFrm. The lines that follow those two below are the same lines as in the preceding example except that they use the shorter name MyControl to refer to Forms![EzQueryFrm]![FldNamesCombo] (which makes the code a little easier to read).

The main point here though is that if you have a combo box or list box on a form, you can programmatically change the contents of the list (or drop-down menu) to show the field names from any table or query in the database. Now turn your attention to the second type of list — one that gets its values from a Value List.

0 0

Post a comment