Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2011-04-20
14
Medium Priority
?
624 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.
0
Comment
Question by:Bevos
  • 4
  • 4
  • 2
  • +2
14 Comments
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 35433254
Access database fields have a Caption property, which will be displayed as the column header, in lieu of the actual column name if you use that value.  Unfortunately, if you set the list or combo boxes Row Source Type to "Field List" it still displays the column name.

What I have done in the past, is use a Value List and populate the list by concatenating the value of the Caption property to the RowSource, something like:

dim strValList as string
set tdf = currentdb.tabledefs("yourTableName")
for intLoop = 0 to tdf.fields.count - 1
    strValList = ";" & NZ(tdf.fields(intLoop).properties("Caption"), tdf.fields(intLoop).name)
Next
me.lst.RowSource = mid(strValList, 2)

0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 504 total points
ID: 35434397
I am not sure exactly what you are asking.
I just defined captions on a bunch of fields and built a combo box and list box.
Now--are the WRONG (ie fieldname) colums heads appearing or NO column heads.
Column heads are not turned on by default property window
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 35434618
I assumed the OP wanted to display these (alternate column headings) as items in the list, not as column headers.  But very good point.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Bevos
ID: 35435940
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
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 35436067
After you fill in the Caption property in you table design, then you could use:

lstFields.AddItem Field.properties("Caption")

However, you might also want to consider making this a multi-column list (where you hide the Field Name) and display the Caption.  I belive that would look like:

lstFields.AddItem Field.Name & ";" & NZ(Field.properties("Caption"), Field.Name)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35445798
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
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 496 total points
ID: 35449498
AFAIK to get the fields caption property you must read the TableDef collection. to the get field's "caption"
property

fld.Properties("Caption")

Open in new window


See: DAO Programming Code Examples
0
 

Author Comment

by:Bevos
ID: 35450202
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.
0
 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 496 total points
ID: 35450793
Just the piece of code:

fld.Properties("Caption")

Open in new window


will not work by itself.

I was not trying to post a complete solution, just a bread crumb and a link to the code that shows how to work with the TableDef() collection.

You will have to open the TableDef() collection for the desired table first. you will also have to assign fld to be the correct fled.

The way I have always hand; this is to create a look up table that has the field name and a User Friendly description for each field that I want to use. If your application is properly split then getting the TableDef() from the back end require more effort. I also find it has a negative impact on other users.  Also using a look up table makes it back end independent.

If you must use the caption property I can help you write the code. Is you r database split?
0
 
LVL 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 total points
ID: 35450836
Try this:

Private Sub Form_Load()

    Dim rs as DAO.Recordset
    Dim fld as DAO.Field

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

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

End Sub

Your AddItem method was calling fld.properties("Caption"), but your For Each/Next construct was using for each Field in rs.Fields.  I would not use Field as a object name, which is what you were doing in this case because, it is a reserved word.
0
 

Author Comment

by:Bevos
ID: 35451304
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.
0
 
LVL 26

Accepted Solution

by:
Nick67 earned 504 total points
ID: 35451550
Ok,

I've been here, done that.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26919237.html
Just what exact use a listbox that is filled with field captions only is beyond me.
Why would you want a listbox filled not with data but with the captions of the fields?
Here it is though.
Sample attached, adapted from the previous post to show only captions.
___________________________________________________________________________

That's an interesting question!
If the field has no caption then the routine throws error 3270.
So, that error is caught

This code assume DAO 3.6
Disambiguate if you have ADO on the go.
Call the function and pass in the desired table name from somewhere on the form in question.
The listbox needs to be set to Value List, and 2 columns

Again, that was a neat question!
Have a gander here, too
http://bytes.com/topic/access/answers/870485-update-tables-caption

Public Function BuildValueList(TableName As String)
On Error GoTo myerror
Dim FinalString As String
Dim db As Database
Dim rs As Recordset
Dim myfield As Field
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from " & TableName & " where 1 = 2;", dbOpenDynaset, dbSeeChanges)

For Each myfield In rs.Fields
    FinalString = FinalString & myfield.Name & ";" & Nz(myfield.Properties("Caption"), "no caption") & ";"
Next myfield

Me.List0.RowSource = FinalString  '<--------------------replace List0 with your listbox name

Exit Function

myerror:
If Err.Number = 3270 Then 'no existing caption
    FinalString = FinalString & myfield.Name & ";" & "no caption" & ";"
    Resume Next
End If

End Function

Bevos.mdb
0
 
LVL 74

Expert Comment

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

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

Author Comment

by:Bevos
ID: 35460988
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

575 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question