Changing Field Captions in VBA

[Dave Johnson]

Anyone Know how to Change Table field captions using VBA
code.

 

[Duane Hookom]

I am not sure why you want to apply captions to fields. It's generally
agreed that users should be prevented from viewing any records in tables.
Forms allow you to set any label captions that you want. When I (as a
developer) view a table or query, I want to see the REAL field names, not
some captions.

I handle captions, input masks, format, and lookup field properties in
tables all the same... ignore them.

 

[Allen Browne]

Set the Caption property of the Field in the TableDef.
If the property does not exist, you have to create it.

Call SetPropertyDAO(dbEngine(0)(0).TableDefs("Table1").Fields("Field1"),
"Caption", dbText, "Whatever you want as the caption here.")


Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to
" & _
varValue & ". Error " & Err.Number & " - " & Err.Description &
vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function