Getting the fields caption using code

[gamisens]

I would like to loop through the field collection of a table and store the fields caption in an array variable. Can someone help me with the code to do this? I dont see the option field.caption available when I loop through the collection, I can only get field.name or field.value. Thank you.

 

[raskew]

Caption is part of the field's property collection. When looping through the fields, you'd use something like:

MyCaption = fld.properties("Caption&quot
Debug.print MyCaption

The problem that arises is, when there is no specified caption, it generates a 3270 error. Here's some code you can experiment with. From the debug window, call it using ? GetCaptions("MyTable&quot

Function GetCaptions(tName as string)
Dim db As Database, td As TableDef
Dim FieldName As String, fld As Field
Dim FieldCaption As String

Set db = CurrentDb
Set td = db.TableDefs(tName)

For Each fld In td.Fields
FieldName = fld.Name
On Error Resume Next
FieldCaption = fld.Properties("Caption&quot
If Err = 3270 Or FieldCaption = " " Then '3270 = object not found
FieldCaption = "No caption provided."
End If
Debug.Print FieldName & ": " & FieldCaption
Next fld

Set td = Nothing
Set db = Nothing
End Sub

 

[gamisens]

Thank you, this was just what I was looking for. It's so goog having somewhere to turn to for answers.