How do you get a field's caption and description from a table def? 1

[TCARPENTER]

I know I've done this at least once, but for the life of me I can't remember how to do it - or did I just dream I did it?

Thanks
Todd

 

[spitzmuller]

I haven't tried this code in access, i'm just typing it out of my head, but it should work:

tblDef.fields("NameOfTheField").caption = "A new caption"

'or

tblDef.fields(index).caption = "A new caption"

Hope that helps Simon

 

[CajunCenturion]

To get the description of a database field, you can use the ADOX objects. First include a reference to the MS ADOX Ext x.y for DDL and Security, and then you can use the following code. It will return whatever is typed into the description column in the Table Design form.

Dim lObj_Catalog        As ADOX.Catalog
   
Set lObj_Catalog = New ADOX.Catalog
lObj_Catalog.ActiveConnection = CurrentProject.Connection
MsgBox lObj_Catalog.Tables(<Table Name>).Columns(<Column Name>).Properties("Description")
Set lObj_Catalog = Nothing

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[TCARPENTER]

Thanks folks,

I did try the .caption option to no avail but I seem to vaguely remember the ADOX options and I'll give this a try.

Thanks

Todd

 

[TCARPENTER]

Oh yeah...

Sub EnumFields()
  Dim dbs As DAO.Database
  Dim tbl As DAO.TableDef
  Dim fld As DAO.Field
  Dim sTb As String
  Dim prp As DAO.Property
  
  sTb = InputBox("Enter table name")
  Set dbs = CurrentDb
  Set tbl = dbs.TableDefs(sTb)
  On Error Resume Next
  
  For Each fld In tbl.Fields
    Debug.Print fld.Name
    Debug.Print "  " & fld.Properties("Caption")
    Debug.Print "  " & fld.Properties("Description")
  Next fld

End Sub

The key being the On Error Resume Next statement because if the caption or description is missing from the table definition, the routine will generate an error.

Thanks again for the help
Todd