On Forms, this is normally styled as a combo box (an edit box with a down arrow).

There are basically, 3 types of lookup tables

• You always display all the possible options
• You show a subset of the options based on some criteria
• You hard code the options at design time and don't use a "table"

• Store only an ID in the table. This is the most efficient of the 2. This allows the short and long descriptions to be modified without affecting the tables which link to them.
• Store the actual value from the list. Most people consider this to be a violation of the Relational Database design rules (normalization) - never store a piece of data in more than one place. However, this significantly simplifies some queries (because the ID codes are usually not known) and simplifies reports (fewer tables to link).

Here is a generic routine used to populate a pick list (ListUI) from LookUpTC (TCursors (TC) point to a table - opened in another routine).

method Init_List(const ListUI    UIObject,
                 const SearchStr String
                )
var 
  tempCounter  SmallInt
endvar

  tempCounter       = 0
  ListUI.List.Count = 0

scan LookUpTC for LookUpTC."Record Type" = SearchStr :
  tempCounter           = tempCounter + 1
  ListUI.List.Selection = tempCounter
  ListUI.List.Value     = LookUpTC."Display String"
endscan

  ListUI.List.Selection = 1
  ListUI.Container = ListUI.List.Value

endmethod

method Init_Box()

var 
  tempUI             UIObject
endvar
     ; open the lookup table of methods related to this document
  LookupTC.open(MainMenu_Dir_String+"\\Methods")

  tempUI.Attach(Report_Selection_UI_Field.List)
  Init_List(tempUI, "Report")

  tempUI.Attach(Form_Selection_UI_Field.List)
  Init_List(tempUI, "Form")

endmethod

Var
  LookUpTC         TCursor
  AssociationTC    TCursor
  globalParameters DynArray[] String
endVar

The help is kind of sketchy - and it always points you to using the wizard (available on the form and in table design).

Value List	You can type in a fixed list of allowed values, separated by semicolons. red;blue;green
Table/Query	This can be used to get all the values in a table. A query will get selected values, it is rerun using ObjectName.requery
Field List	No clue, this appears to be a subset of Table/Query

Be sure to set Limit to List according to your needs.

In order to use the form wizard to design a combo box, make sure that the Control Wizard's button is pressed in (on the toolbox pallet, it has a wand icon), and then place a combo box on the form.

Row Source Type	Row Source
Value List	"red";"blue";"green"
Table/Query	select fielda, fieldb from table;
Table/Query	tableName

MS Access provides a On Not in List event for combo boxes. You can use this to add new values to the lookup table.

SELECT DISTINCTROW Addresses.AddressID, 
                   Addresses.LastName, Addresses.FirstName 
  FROM Addresses 
 WHERE (((Addresses.LastName)=[Text4]));

Private Sub Combo14_GotFocus()
  Combo14.Requery
End Sub

• The query is from the Row Source property on the Data tab. Extra white space is ignored. Carriage Returns can be entered in the Zoom window by pressing Ctrl-Enter.
• Text4 is the name of a Text Box component.
• The code is from the On Got Focus event.

Using On Got Focus allows Text4 to be attached to a table. Then, as you use the Forward and Back buttons to step through the table, the contents of the pick list are updated each time the list is opened. The form's On Current event also works, but it re-runs the query for each record displayed, not each time the list is displayed.

I actually had a form where none of the data would display if the Requery was in the comboBox's On Got Focus event and the comboBox was attached to a field in the table. Moving it to the form's On Current event solved the problem. This appears to be related to the setting of the Column Widths property - if the first field has a display width of zero (typical with lookup tables), then the Requery must be in the form's On Current event. Unfortunately, in datasheet view, none of the data will display until you click on the field.

MS Access provides an example of using one combo box to control the contents of another combo box in

C:\Program Files\Microsoft Office\Office\Samples\Solutions.mdb

• The 2 combo boxes are not bound to table fields
• The query is stored separate from the form
• The Requery command is in the 1st combo box's After Update event. (After Update will not work as expected if the field is attached to a table - it is not called if you simply step through the table.)

• Using a comboBox to select a record

• TComboBox - displays a string list.

  Property	Description
  Items	The list values stored as a string list.
  Sorted	When true, forces the list to be sorted alphabetically.
  Style	csDropDownList forces the values to be limited to those in the list csDropDown allows any value to be entered

• TDBComboBox - displays a string list. The value is automatically stored in the specified Table/field. The properties are identical to those for TComboBox plus the following

  Property	Description
  DataSource DataField	The table (source) and field that this control is associated with.

• TDBLookupComboBox reads the list values from a table.

  Property	Description
  DataSource DataField	The table (source) and field that this control is associated with.
  ListSource ListField	One or more fields in the lookup table containing the data actually displayed in the list. Use semicolons to separate multiple fields.
  KeyField	Field in the lookup table that has the link values stored in both tables.
  ListFieldIndex	When ListField specifies more than one field, this specifies which field is used for incremental searches and which field is displayed in the combobox edit area.

  So far, I can't get this to work. You have to double click the TTable and define a new lookup field. But so far all I get are circular references.

Dataset1.Filter := 'State = ' + QuotedStr(Edit1.Text);

URL: http:// cpcug.org / user / clemenzi / technical / Databases / LookupTables.html