lookupSQL

Topic group Related topics Example

An SQL SELECT statement describing a rowset that contains lookup values for a field.

Property of

Field

Description

Use lookupSQL or lookupRowset to implement automatic lookups for a field. When a control that supports lookups, like the ComboBox control, is dataLinked to a field with either lookupSQL or lookupRowset defined, the control will:

Populate itself with display values from the lookup rowset

Lookup the true value of the field in the lookup rowset

Display the corresponding lookup value in the control

Do the reverse lookup when the display value in the control is changed

Write the corresponding true value back to the field

If the display lookup fails, a blank is displayed in the control. If the reverse lookup fails, a null is written to the field.

The same automatic lookups are applied when accessing the value property of the field. The value of the field will appear to be the lookup value. Assigning to the value will perform the reverse lookup.

Setting the lookupSQL property is the simpler way of implementing automatic lookups. lookupSQL contains an SQL statement of the form:

SELECT <lookup field>, <display field> [,...] FROM <lookup table> [<options>]

The first two fields must be the lookup field and the display field, respectively. The display field may be a calculated field. You may include other fields so that you can get information about the chosen row. The SQL SELECT statement may include the usual options; in particular, you may want the table to be ordered on the lookup field (or use a table where such an index is available) for faster lookups. The SQL statement is executed in the same database as the query (or stored procedure) that contains field’s rowset.

When an SQL statement is assigned to lookupSQL, the lookupRowset property will contain a reference to the generated rowset. You may refer to the fields in the matched lookup row through this reference. For advanced applications, you may assign your own rowset to lookupRowset. This releases the generated rowset.

Note

When a field's lookupSQL property is set, and that field is referenced in the rowset's filter property, the value being compared by the filter is the field's true value, not the lookup value.