ListBox table field names

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stu - 21 May 2008 14:16 GMT

There must be a way to determine the field names of a ListBox whose record
source is Table/Query. For example I would like to know that
ME.LISTBOXNAME.COLUMN(0) corresponds to the field recordID in the table. How?

Reply to this Message

Douglas J. Steele - 21 May 2008 16:02 GMT

If the RowSource is strictly the tablename, change it to a query. You can
then open the query by clicking on the ellipsis (...) to the right of the
query name and run it, thus seeing the order in which the fields are
returned.

AFAIK, there's no property for a list box that will tell you that
information, since there are other ways of populating a list box for which
that property would be meaningless.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> There must be a way to determine the field names of a ListBox whose record
> source is Table/Query. For example I would like to know that
> ME.LISTBOXNAME.COLUMN(0) corresponds to the field recordID in the table.
> How?

Reply to this Message

Stu - 21 May 2008 17:26 GMT

Yes, I know I can eyeball and count. I'm looking for a way to get the field
names if/when the table/query changes. The following code is the best I can
come up with. List0 is the listbox name, the event (click) is not relevant.
The index of the array will correspond to the field name.

Private Sub List0_Click()
Dim rst As DAO.Recordset
Dim fld As Field
Dim i As Integer
Dim a(20) As String
Set rst = Me.List0.Recordset

For i = 0 To rst.Fields.Count - 1
a(i) = rst.Fields(i).Name
Next i
rst.Close
End Sub

> If the RowSource is strictly the tablename, change it to a query. You can
> then open the query by clicking on the ellipsis (...) to the right of the
[quoted text clipped - 9 lines]
> > ME.LISTBOXNAME.COLUMN(0) corresponds to the field recordID in the table.
> > How?

Reply to this Message