Search code examples
vbams-accesslistbox

Reference List Box Column by Field Name

I have an MS Access form in which selection of a list box item determines how many other aspects of the application behave.

I have a table containing an ID and description and several other fields which correspond to settings for other aspects of the application; this table is the Row Source of the list box.

The list box is configured such that the first two columns (ID & Description) are visible, with the widths of the remaining columns set to 0 - the Column Widths property of the list box is therefore something like:

1cm;5cm;0cm;0cm;0cm;0cm;0cm;0cm;0cm;0cm;0cm;0cm;0cm

Within my application I then reference the remaining fields using expressions such as:

MyListBox.Column(n)

However, this relies heavily on the order of the fields in the table, and if I then decide to add another field to my table, I have to:

  • Increment the list box Column Count
  • Add another zero to the semi-colon delimited Column Widths
  • Potentially change the identifiers used in the various Column(n) statements, depending on where the new field was added in the table.

My original reason for using this approach was to avoid the need to repeatedly open a RecordSet to obtain the values of the additional fields associated with the item selected from the list box.

Is there a way to reference list box fields by field name rather than by column number?

(Or is there an entirely better way to approach this task?)

Solution

  • While I'm hearing a lot of No, not possible, this actually is possible for list boxes that have a row source type of Table/Query, by using the ListBox.RecordSet property.

    For combo boxes, this is easy, since the recordset moves along with the selected value. For list boxes, it doesn't, so you will have to know the name and position of the ID column (or, if the ID column is the bound column, just use the .Value property)

    Example:

    Dim rs As DAO.Recordset
Set rs = Me.List1.Recordset.Clone
rs.FindFirst "ID = " & Me.List1.Value
Debug.Print rs.Fields!Field1

    Add code to check if a value is selected, if the recordset property is set, and if a match is found if needed.