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.



"Dale Fye" wrote:

> 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
>
>
>
>
>

But if I set the property, won't that overwrite whatever is already in that
property?

I'm confused.

"David H" <(E-Mail Removed)> wrote in message
news:7E8ACA3D-6775-4590-83C8-(E-Mail Removed)...
> 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.
>
>
>
> "Dale Fye" wrote:
>
>> 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
>>
>>
>>
>>
>>

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.

"Dale Fye" wrote:

> But if I set the property, won't that overwrite whatever is already in that
> property?
>
> I'm confused.
>
> "David H" <(E-Mail Removed)> wrote in message
> news:7E8ACA3D-6775-4590-83C8-(E-Mail Removed)...
> > 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.
> >
> >
> >
> > "Dale Fye" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
> >>
>
>
>

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




--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Dale Fye" <(E-Mail Removed)> wrote in message
news:O3oAWz$(E-Mail Removed)...
> But if I set the property, won't that overwrite whatever is already in
> that property?
>
> I'm confused.
>
> "David H" <(E-Mail Removed)> wrote in message
> news:7E8ACA3D-6775-4590-83C8-(E-Mail Removed)...
>> 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.
>>
>>
>>
>> "Dale Fye" wrote:
>>
>>> 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
>>>
>>>
>>>
>>>
>>>
>
>

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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Dale Fye" <(E-Mail Removed)> wrote in message
> news:O3oAWz$(E-Mail Removed)...
>> But if I set the property, won't that overwrite whatever is already in
>> that property?
>>
>> I'm confused.
>>
>> "David H" <(E-Mail Removed)> wrote in message
>> news:7E8ACA3D-6775-4590-83C8-(E-Mail Removed)...
>>> 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.
>>>
>>>
>>>
>>> "Dale Fye" wrote:
>>>
>>>> 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
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>
>
>