Access 2007: Controlling field names in a list or combo box

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi the wrong field names (no column heads) are appearing in the list box. The box is populated with the code:

Private Sub Form_Load()

Set rs = CurrentDb.OpenRecordset("select * from tblStudyDescription")

For Each Field In rs.Fields
lstFields.AddItem Field.Name
Next

End Sub

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

Jeffrey Coachman

What I do is simply turn off the column headings and actually put a label (or multiple Labels) across the top of the list/combobox.
Simple and effective...

JeffCoachman
untitled.JPG

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

ASKER

Hi everyone, thanks for your responses! I've tried each one and am still not getting there yet.
I've also made sure I have assigned a caption to each field in the record source.

Currently the code looks like this:

Private Sub Form_Load()

Set rs = CurrentDb.OpenRecordset("select * from qryAll")

For Each Field In rs.Fields
lstFields.AddItem fld.Properties("Caption")
Next

End Sub

And I get the following error when loading the form: 'Run-time error '424': Object required'. and when I debug I am pointed to: 'lstFields.AddItem fld.Properties("Caption")'.

If I go into the form, as you would expect the listbox is unpopulated.

Any more suggestions would be very welcome :) thanks so much everyone,
Bevo S.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Hi Fyed and THTC, thanks for the great advice! I tried out your code fyed and it doesn't work as it (it says property not found). I think I need to do some more research about TableDef from the resource THTC gave me to figure out whats going on. I'll let you both know if I figure something out :)

Thanks again,
Bevo S.

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Jeffrey Coachman

<Any more suggestions would be very welcome?>
I posted a suggestion:
https://www.experts-exchange.com/questions/26967548/Access-2007-Controlling-field-names-in-a-list-or-combo-box.html?cid=1572&anchorAnswerId=35445798#a35445798

...Yet you seemed to have ignored it...