Databases - UIPickList

PickList aka ComboBox

Basically, a PickList is an Edit control with a down arrow. When the arrow is clicked a List of options is displayed.

MS Access 97

Normally, a Combo Box is used to enter data in a field. This page discusses placing one of these on a form.

When a table is defined, it is possible to associate a pick list or a lookup table with individual fields. Then, when one of those fields is placed on a form, a correctly configured Combo Box is automatically configured.

Some Combo Boxes are used for other purposes (and, therefore, not associated with fields). For instance, it is possible to Use a ComboBox to Pick A Record.

Adding a Combo Box via the Wizard

You can use the wizard to add combo boxes. (I suggest doing this the first few times.)

Make sure that the wand icon on the Toolbox is pressed in.
Place a Combo Box on your form - the Wizard starts automatically
Select "get values from a table or query"
Select a table
Select the fields you want - Normally, you want to select and ID field (usually a number) and a field which contains what you want to display.
Hide the ID field (Key Column)
Give it a name that indicates that it is a Combo Box (eg, Country_UICombo)

At this point the control is not associated with a field. Be sure to set Control Source to the field.

Tab Property Value
Data Control source blank - the field is "unbound"
Data Row Source Type Table/Query
Data Row Source SELECT DISTINCTROW [TableName].[id], [TableName].[Country] FROM [TableName];
Data Bound Column 1
Format Column Count 2
Format Column Widths 0";1"

Tab	Property	Value
Data	Control source	blank - the field is "unbound"
Data	Row Source Type	Table/Query
Data	Row Source	SELECT DISTINCTROW [TableName].[id], [TableName].[Country] FROM [TableName];
Data	Bound Column	1
Format	Column Count	2
Format	Column Widths	0";1"

General Notes

When a combo box contains more than one column, the following will populate 2 fields when the user selects one record.

Private Sub Combo0_Click() ' AfterUpdate may be better than Click
    Me.Text2 = Me.Combo0.Column(1)
    Me.Text4 = Me.Combo0.Column(2)
End Sub

It is possible to change a Combo Box to a List box, and vice versa. (Either right click the control and select Change To or select Format / Change To from the main menu.) Each control provides several ways to read the currect selection.

Combo Box

Note that Text and Value do not always have the same value.

  xx = xyz_UIComboBox.Value       'Returns the data in the bound column

  i = xyz_UIComboBox.ListIndex
  xx = xyz_UIComboBox.ItemData(i) 'Returns the data in the bound column

  xyz_UIComboBox.SetFocus         ' SetFocus is required if this code is in a button
  xx = xyz_UIComboBox.Text        ' and you want to read the Text property
                                  ' Returns the data displayed on the form


   ' When using the Column property
   '   The Columns are numbered from zero
   '      Be careful, the BoundColumn property numbers the columns from one
   '   The index is the SECOND parameter, 
   '      When typing in the development environment, 
   '        the popup help has the parameters in the wrong order
   '      The help file has them in the correct order
  xx = xyz_UIComboBox.Column(column)
  xx = xyz_UIComboBox.Column(column, index) ' This works
  xx = xyz_UIComboBox.Column(index, column) ' This FAILS - order in the popup help

List Box

  xx = xyz_UIListBox.Value       'Returns the data in the bound column

  i = xyz_UIListBox.ListIndex
  xx = xyz_UIListBox.ItemData(i) 'Returns the data in the bound column

ListIndex, Value, Text and are in the help (just search for them), but they are not listed as properties under either "Combo Box Control Properties" or "List Box Control Properties". Value is mentioned in the write up for Text. I have no idea how I found ListIndex. (Actually, there are lots of unlisted properties. I wonder how MS expects people to learn how to use this product.)

You can use the Column property to return data from a specified row and column.

Problems Accessing List Elements

Supposedly, in VisualBasic and VBA, the list(index) syntax can be used to both read and write list elements. In fact, the MS Access help explicitly states that the list elements can be read via

xx = combo0.list(2)

However, when I try to run the code with MS Access 97 SR2, I get

Compile error:

Method or data member not found

I get the same error with

Display_UICombo.Selected(0) = True

However, this works

 ' Initialize an unattached combobox to the first element in its list
Private Sub Form_Open(Cancel As Integer)
  Display_UICombo = Display_UICombo.ItemData(0)
End Sub

Another way to accomplish this is to set the ComboBox's Default property to

=[Display_UICombo].[ItemData](0)

(The initial equals sign - = - is optional.)

As far as writing values to a list, I get the values from a lookup table (named with an _LU). Tips for Access Database Users by Allen Browne shows how to Fill a ListBox or Combo using code. Wow, what a mess. For more details, in the MS Access help, find combo boxes, setting values / Create a ListBox ... that gets it values from a function, then click the button in step 1.

Now you see why I use lookup tables.

Filters

When defining filters, you can use either the value stored in the field or the value displayed. This example uses the value displayed. (In the picklist, the Row Source points to a table and FieldName is one of the fields returned. The form object is named ProductID_UIPickList, with only one underscore.)

  ((Lookup_ProductID__UIPickList.[FieldName]="String actually displayed"))

Content Problem

I frequently use lists that are populated via a query which gets criteria from fields on the form. What I want is to requery the list each time it is opened (such as when the down arrow is pressed). Unfortunately, I have not found a reliable way to do this.

Scenario

The row source is based on a field in the form
Change the value of the controlling field
Click the ComboBox list arrow
OnEnter requeries the row source
The list is displayed
From the menu, Edit / Undo Current Field/Record is executed
Since the ComboBox is still selected, the next time the list arrow is clicked, the old data is displayed
Using OnMouseDown won't fix this because the keyboard can also be used to display the list
(Perhaps AfterUpdate will work)

Sorting by the Contents of a Field

I have a form with a field/comboBox named LocationTypeID.

In order to sort the data based on the contents of that field, you have to set

Form.OrderBy = Lookup_LocationTypeID.Type

I discovered this by using the Order buttons on the tool bar. There is no help at all on where Lookup_LocationTypeID.Type came from. I had assumed that LocationTypeID should have worked - wrong again.

On the same form, I have a pick list (comboBox) which is used to limit the displayed records. However, the following code

Form.Filter = "LocationTypeID = '" & _
                 LocationType_Filter_UIPicklist & "'"

produced

Run-time error '3079'.
The specified field 'LocationTypeID' could refer to more than one table listed in the FROM clause of your SQL statement.

I can't figure out why.

When I deleted the OrderBy property, the code worked perfectly. Perhaps you can't OrderBy and Filter on the same field.

However, the following works

Form.OrderBy = Locations.LocationTypeID

Go figure :)

In the final code, this seems to work

  ' In the property box
Form.OrderBy = Lookup_LocationTypeID.SortOrder

  ' On the pick list Change event
Form.Filter = "Locations.LocationTypeID = '" & _
               LocationType_Filter_UIPicklist & "'"
Form.FilterOn = True

  ' For the LocationTypeID pick list, the Row Source is, 
  '   where SortOrder is not displayed
SELECT DISTINCTROW 
LocationType_LU.LocationTypeID, 
LocationType_LU.Type, 
LocationType_LU.SortOrder

FROM LocationType_LU 
ORDER BY LocationType_LU.SortOrder;

Apparently, MS Access can not tell which query I am referring to.

Delphi 5.0

The items in a PickList (TComboBox) are stored in a string list (TStrings - an array of strings) which supports Reading, Writing, Adding, Removing and more.

Based on the Delphi 5 help, the following are equivalent

ComboBox1.Items[0]         := 'Some string';
ComboBox1.Items.Strings[0] := 'Some string';

For database access use

DBComboBox1         Must manually set the values of the list

DBLookupComboBox1   Automatically sets the list values
  ListSource          Datasource for the list values, Select from the list
  KeyField            The value of this field is saved in DataSource/DataField
  ListField           This is the value displayed - 
                        multiple fields are separated by semicolons

  DataSource          This is the datasource where the result is stored
  DataField           This is the   field    where the result is stored

Multiple fields are tricky - there is only one DropDownWidth parameter (it's in pixels and defines to total list width) - there is no way to set the widths of the individual fields (which appear to be about 140 pixels each). The Delphi 5 help does not contain any examples.

This example generates a "view" (based on MS Access and KADAO tables)

KADaoTable_Users_View.SQL

     select UserID, Last_Name + ',  ' + First_Name as Name
       from users_tbl
   order by Last_Name;

The "view" allows me to concatenate several fields, with puctuation, and preferred sorting, for use in DBLookupComboBox1 without the problems of wierd column widths and only one field being shown in the edit window. (True Views are created in the database definitions ... however, MS Access does not support these. You can use a MS Access Query as a read only view.)

DBLookupComboBox does not have an event that detects when the KeyValue is changed. Therefore, I use both

OnCloseUp - called when the pick list closes
OnKeyUp - called when the cursor keys change the selection

DBLookupListBox

Almost everything that applies to a DBLookupComboBox also applies to a DBLookupListBox.

There is a problem when a DBLookupListBox is used to select a record - when the program begins, none of the list items is highlighted. The following code sets the highlight to the first item in the list.

procedure TForm1.Site_TabSheetShow(Sender: TObject);
begin
  if Sites_DBLookupListBox.SelectedItem <> '' then begin
    Sites_DBLookupListBox.KeyValue := Sites_DBLookupListBox.SelectedItem;
    Sites_DBLookupListBoxClick(self);
  end;
end;

procedure TForm1.Sites_DBLookupListBoxClick(Sender: TObject);
begin
  Sites_DBCtrlGrid.DataSource.DataSet.Filter :=
     'Purpose=''' + Sites_DBLookupListBox.SelectedItem + '''';
  Sites_DBCtrlGrid.DataSource.DataSet.Filtered := true;
end;

Notice that the OnClick event is called when

The user selects an item in a grid, outline, list, or combo box by pressing an arrow key.

Author: Robert Clemenzi - clemenzi@cpcug.org