How to get the "Caption" value for a field in a table.

[David G.]

I need to list all the field names of a table in a listbox. Since the
field names are like "tblEmplFirstNm", I was hoping to display each
field's caption value instead of the fieldname.

I am using vba and loading the fieldnames into a multi-dimensional
array, I want to add the caption value as well. I'm sure this property
has to be available, but I can't find it.

Thx.
THANKS!
David G.

 

[Tom van Stiphout]

On Wed, 18 Nov 2009 19:34:49 -0500, David G. <[email protected]>
wrote:

Here is an example:
?currentdb.TableDefs("Customers").Fields("ID").Properties("Caption")
-> This is the Caption of the ID field.

-Tom.
Microsoft Access MVP

 

[Douglas J. Steele]

Just be aware, though, that the Caption property doesn't exist until it's
created. An error 3270 (Property not found) will be raised if you try to
refer to the Caption property and it doesn't exist. You'll have to put error
handling into your routine to handle that.

 

[David G.]

I was using the exact syntax you gave me and getting an error message.
I realized I hadn't entered any caption values for the table. Once I
entered a few caption values the code worked.

Thanks for your help!

Here is an example:
?currentdb.TableDefs("Customers").Fields("ID").Properties("Caption")
-> This is the Caption of the ID field.

-Tom.
Microsoft Access MVP

THANKS!
David G.

 

[David G.]

This was my exact problem. I had the right code and syntax, but I
hadn't set any of the captions for the table. I interpreted the
"Property not found" message to mean the "Caption" property was not
available for inspection. (Which it isn't until the Caption property
is set to something.)

What if I delete the Caption for a particular field? Hmmmm.

Just be aware, though, that the Caption property doesn't exist until it's
created. An error 3270 (Property not found) will be raised if you try to
refer to the Caption property and it doesn't exist. You'll have to put error
handling into your routine to handle that.

THANKS!
David G.

 

[Tom van Stiphout]

Yes, I know. I'm sure the Access team would consider this a
performance or storage optimization, but I say that in the age of 1 TB
devices for USD 125 it is not a problem worth worrying about.

Access 2010 Beta just came out:
http://www.microsoft.com/office/2010/en/download-office-professional-plus/default.aspx
Why don't you check it out and give MSFT a piece of your mind if it
doesn't work like you would want it to.

-Tom.
Microsoft Access MVP