How to: Use lookup columns

Show the name of a related record instead of its ID, or a symbol with text instead of a status number, and let users pick the value from a list.

Requires WKL Forms Object Library 1.23 or later.

Editor = wedLookup with RowSource (a table, query, SQL statement, or value list), BoundColumn, and DisplayColumn works like an Access combo box. Editor = wedImageLookup with AddImageItem maps stored values to an icon and text.

With Me!List1.Object
    With .Columns.Add("WarehouseID", "Warehouse")
        .Editor = wedLookup
        .RowSource = "SELECT WarehouseID, WarehouseName FROM tblWarehouses ORDER BY WarehouseName"
        .BoundColumn = 1          ' stored value: WarehouseID
        .DisplayColumn = 2        ' shown: WarehouseName
    End With
    With .Columns.Add("Priority", "Priority")
        .Editor = wedLookup
        .RowSource = "'low';'normal';'high'"   ' value list
    End With
    With .Columns.Add("Status", "Status")
        .Editor = wedImageLookup
        .AddImageItem 0, "Info", "open"
        .AddImageItem 1, "Settings", "in progress"
        .AddImageItem 2, "Shield", "done"
    End With
End With
 
Debug.Print Me!List1.Object.CellValue(7, "WarehouseID"), Me!List1.Object.CellText(7, "WarehouseID")

• CellValue returns the stored value, and CellText returns the displayed text.
• The Excel export writes the displayed text; RawValues:=True writes the stored values.
• Icon names are the same as for ImageField: images from the database's image gallery, built-in names, or file paths.
• DisplayColumn = 0 shows the bound column itself.

• API reference: WklColumn.RowSource, AddImageItem
• How to: Define columns and bands
• WklTreeList overview