Read fields caption and description in VBA

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

I'm trying to read a fields caption and description via VBA.

When I iterate through the fields of a table that I know some of the fields
contain descriptions and captions, refering to the property always returns
an error (3270, Property not found).

Do I have to set the property before I can refer to it, even if there is
already text in the property, as seen in the table design view? Or am I
missing something?

Dale
 
If I recall correctly, YES the property has to be set otherwise you'll get an
error. You can either trap the error if you're referencing the property by
name or you could loop through the properties collection of the field and
check for the property name.
 
But if I set the property, won't that overwrite whatever is already in that
property?

I'm confused.
 
Yes. I posted the article to provide you with an example of how to read the
properties and how to trap the error if it occurrs.
 
Your original post states "I know some of the fields contain descriptions
and captions". Yes, you'll get an error on those fields that don't contain
descriptions.

What you can do is set your error handling to ignore the error:

On Error GoTo EH

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field
Dim prpCurr As DAO.Property
Dim varDescription As Variant

Set dbCurr = CurrentDb()
For Each tdfCurr In dbCurr.TableDefs
Debug.Print "Fields in Table " & tdfCurr.Name & ":"
For Each fldCurr In tdfCurr.Fields
strDescription = fldCurr.Properties("Description")
Debug.Print " " & fldCurr.Name & (" (" + varDescription & ")")
Next fldCurr
Next tdfCurr

EndIt:
Set prpCurr = Nothing
Set fldCurr = Nothing
Set tdfCurr = Nothing
Set dbCurr = Nothing
Exit Sub

EH:
Select Case Err.Number
Case 3270 ' Property Not Found
varDescription = Null
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume EndIt
End Select
 
Thanks, Doug.

I was using similar code, but was doing my error trapping in line.

On Error Resume Next
For Each fld In tdf.Fields
varDesc = fld.Properties("Description")
If Err.Number <> 0 Then
varDesc = "not available"
' Err.Clear
End If
varCaption = fld.Properties("Caption")
If Err.Number <> 0 Then
varCaption = "not available"
' Err.Clear
End If
Debug.Print fld.Name, varDesc, varCaption
Next

What is interesting is that if I failed to clear the error, then it was
never resetting the Err and was always entering the If/End If sequences. As
soon as I added the Err.Clear lines, it worked the way I was expecting. I
don't use in-line error handling very often, but this will be a reminder to
clear the error as soon as I test for it.

Dale
 
Back
Top