Editing Caption Property

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Darlington - 26 Oct 2007 08:10 GMT

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.

Signature

Bob Darlington
Brisbane

Reply to this Message

Stuart McCall - 26 Oct 2007 08:53 GMT

> I'm using the following code to try to change a caption property for a
> field in a table.
[quoted text clipped - 9 lines]
> I'm using Access 2002.
> Any ideas welcome.

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

Reply to this Message

Bob Darlington - 30 Oct 2007 06:32 GMT

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

Signature

Bob Darlington
Brisbane

>> I'm using the following code to try to change a caption property for a
>> field in a table.
[quoted text clipped - 49 lines]
>
> SetObjProperty fld, "Caption", dbText, strCaption

Reply to this Message

Keith Wilby - 26 Oct 2007 09:32 GMT

> I'm using the following code to try to change a caption property for a
> field in a table.
[quoted text clipped - 9 lines]
> 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

Reply to this Message

Bob Darlington - 30 Oct 2007 06:33 GMT

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

Signature

Bob Darlington
Brisbane

>> I'm using the following code to try to change a caption property for a
>> field in a table.
[quoted text clipped - 18 lines]
> Keith.
> www.keithwilby.com

Reply to this Message