We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Bevos
Bevos asked
on
Medium Priority
657 Views
Last Modified: 2012-05-11
Hello everyone, I have a question about how to control field names in list and combo boxes.  I have a table that I want to populate a list box and have defined captions for each of the fields.  How do I make it so that the list box displays the captions rather than the field names?

Thank you,
Bevo S.
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2014
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
<Any more suggestions would be very welcome?>
I posted a suggestion:
https://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26967548.html?cid=1572#a35445798

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

Author

Commented:
This worked perfectly Nick, thanks so much for the feedback.  Hi Boag, I'm sorry if you feel that I ignored your response.  It is a really cool trick and I'm sure I'll apply it in the future, but it wasn't addressing the question that I was interested in exactly (replacing the field names in a listbox with their captions) so I didn't respond right away.  Thanks for taking the time to answer.

Thanks again everyone for helping me with this,
Bevo S.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.