Editing Caption Property

I'm using the following code to try to change a caption property for a field
in a table.
Dim dbs As Database, fld As Field, fFormat As Property
Set dbs = OpenDatabase(CurrentDataFile)
Set fld = dbs.TableDefs(tblName).Fields(fldName)
Set fFormat = fld.CreateProperty("Caption", dbText, strCaption)
fld.Properties.Append fFormat

Each time it's generating error 3367 - "Cannot Append. An object with that
name already exists".
It doesn't matter what strCaption I enter, the same message appears.
I'm using Access 2002.
Any ideas welcome.

--
Bob Darlington
Brisbane

Re: Editing Caption Property

"Bob Darlington" wrote in message
news:472192c9$0$14825$afc38c87@news.optusnet.com.au...
> I'm using the following code to try to change a caption property for a
> field in a table.
> Dim dbs As Database, fld As Field, fFormat As Property
> Set dbs = OpenDatabase(CurrentDataFile)
> Set fld = dbs.TableDefs(tblName).Fields(fldName)
> Set fFormat = fld.CreateProperty("Caption", dbText, strCaption)
> fld.Properties.Append fFormat
>
> Each time it's generating error 3367 - "Cannot Append. An object with that
> name already exists".
> It doesn't matter what strCaption I enter, the same message appears.
> I'm using Access 2002.
> Any ideas welcome.
>
> --
> Bob Darlington
> Brisbane

This function first tries to set an already existing property, and if an
error 3270 (property not found) occurs, it appends the new property to the
collection. Paste it into a standard module:

''' BEGIN CODE '''
Sub SetObjProperty(pObject As Object, pProperty As String, pType As Integer,
pValue As Variant)
Const PROPERTY_NOT_FOUND As Long = 3270
Dim prp As Property
'
On Error GoTo SetObjProperty_Err
'
pObject.Properties(pProperty) = pValue
pObject.Properties.Refresh

SetObjProperty_Exit:
Set prp = Nothing
Exit Sub

SetObjProperty_Err:
If Err.Number = PROPERTY_NOT_FOUND Then
With pObject
Set prp = .CreateProperty(pProperty, pType, pValue)
.Properties.Append prp
.Properties.Refresh
End With
Resume SetObjProperty_Exit
Else
MsgBox Err.Number & ": " & Err.Description, vbCritical,
"SetObjProperty"
Resume SetObjProperty_Exit
End If

End Sub
''' END CODE '''

Use it like this:

SetObjProperty fld, "Caption", dbText, strCaption

Re: Editing Caption Property

"Bob Darlington" wrote in message
news:472192c9$0$14825$afc38c87@news.optusnet.com.au...
> I'm using the following code to try to change a caption property for a
> field in a table.
> Dim dbs As Database, fld As Field, fFormat As Property
> Set dbs = OpenDatabase(CurrentDataFile)
> Set fld = dbs.TableDefs(tblName).Fields(fldName)
> Set fFormat = fld.CreateProperty("Caption", dbText, strCaption)
> fld.Properties.Append fFormat
>
> Each time it's generating error 3367 - "Cannot Append. An object with that
> name already exists".
> It doesn't matter what strCaption I enter, the same message appears.
> I'm using Access 2002.
> Any ideas welcome.
>

Hi Bob.

Just a shot in the dark but wouldn't the property you're trying to create
already exist? Shouldn't you be trying to set its value rather than create
it?

Keith.
www.keithwilby.com

Re: Editing Caption Property

Thanks Stuart,
Sorry about the delay getting back to you.

--
Bob Darlington
Brisbane
"Stuart McCall" wrote in message
news:ffs6s9$ps$1$8300dec7@news.demon.co.uk...
> "Bob Darlington" wrote in message
> news:472192c9$0$14825$afc38c87@news.optusnet.com.au...
>> I'm using the following code to try to change a caption property for a
>> field in a table.
>> Dim dbs As Database, fld As Field, fFormat As Property
>> Set dbs = OpenDatabase(CurrentDataFile)
>> Set fld = dbs.TableDefs(tblName).Fields(fldName)
>> Set fFormat = fld.CreateProperty("Caption", dbText, strCaption)
>> fld.Properties.Append fFormat
>>
>> Each time it's generating error 3367 - "Cannot Append. An object with
>> that name already exists".
>> It doesn't matter what strCaption I enter, the same message appears.
>> I'm using Access 2002.
>> Any ideas welcome.
>>
>> --
>> Bob Darlington
>> Brisbane
>
> This function first tries to set an already existing property, and if an
> error 3270 (property not found) occurs, it appends the new property to the
> collection. Paste it into a standard module:
>
> ''' BEGIN CODE '''
> Sub SetObjProperty(pObject As Object, pProperty As String, pType As
> Integer, pValue As Variant)
> Const PROPERTY_NOT_FOUND As Long = 3270
> Dim prp As Property
> '
> On Error GoTo SetObjProperty_Err
> '
> pObject.Properties(pProperty) = pValue
> pObject.Properties.Refresh
>
> SetObjProperty_Exit:
> Set prp = Nothing
> Exit Sub
>
> SetObjProperty_Err:
> If Err.Number = PROPERTY_NOT_FOUND Then
> With pObject
> Set prp = .CreateProperty(pProperty, pType, pValue)
> .Properties.Append prp
> .Properties.Refresh
> End With
> Resume SetObjProperty_Exit
> Else
> MsgBox Err.Number & ": " & Err.Description, vbCritical,
> "SetObjProperty"
> Resume SetObjProperty_Exit
> End If
>
> End Sub
> ''' END CODE '''
>
> Use it like this:
>
> SetObjProperty fld, "Caption", dbText, strCaption
>
>
>

Re: Editing Caption Property

Thanks Keith,
That was it (see Stuart's reply).
Sorry about the delay getting back to you.

--
Bob Darlington
Brisbane
"Keith Wilby" wrote in message
news:4721a268$1_1@glkas0286.greenlnk.net...
> "Bob Darlington" wrote in message
> news:472192c9$0$14825$afc38c87@news.optusnet.com.au...
>> I'm using the following code to try to change a caption property for a
>> field in a table.
>> Dim dbs As Database, fld As Field, fFormat As Property
>> Set dbs = OpenDatabase(CurrentDataFile)
>> Set fld = dbs.TableDefs(tblName).Fields(fldName)
>> Set fFormat = fld.CreateProperty("Caption", dbText, strCaption)
>> fld.Properties.Append fFormat
>>
>> Each time it's generating error 3367 - "Cannot Append. An object with
>> that name already exists".
>> It doesn't matter what strCaption I enter, the same message appears.
>> I'm using Access 2002.
>> Any ideas welcome.
>>
>
> Hi Bob.
>
> Just a shot in the dark but wouldn't the property you're trying to create
> already exist? Shouldn't you be trying to set its value rather than
> create it?
>
> Keith.
> www.keithwilby.com