Caption properties for a Field object in Access

The Caption property (that is, the caption) of a Field object in an Access database is used to set the caption for the data field. In the normal database design in order to maintain the convenience of maintenance, many developers have the field name and title are set separately, the title is often more friendly than the field name, more can explain the purpose of the field. This article explains how to read and write this property in VBA from another angle.

The Caption property of a Field object is not an ADO native object, but an access property that can be accessed by ADO, and describes two ways to access this property in the Help document, one that utilizes ADO, one that uses DAO, Since the Field object is not a AccessObject object in ACCESS2003 and previous versions, there is no accessobjectproperties attribute, so we can't solve the problem in ADO. Now use another way to solve the DAO's code.

Sub SetProperty (dbstemp as DAO. Field, StrName as String, _
Bootemp as String)
Dim prpnew as DAO. Property
Dim Errloop as Error
' Attempt to set the specified property.
On Error GoTo Err_property
Dbstemp.properties (strName) = Bootemp
On Error GoTo 0
Exit Sub
Err_property:
' Error 3270 means that ' is not found.
If dbengine.errors (0). Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpnew = Dbstemp.createproperty (StrName, _
Dbtext, Bootemp)
DbsTemp.Properties.Append prpnew
Resume Next
Else
' If different error has occurred, display message.
For each errloop in Dbengine.errors
MsgBox "Error number:" & Errloop.number & vbcr & _
Errloop.description
Next Errloop
End
End If
End Sub
Sub displayclumcaption (ByVal tbname as String,
ByVal Fldindex as Integer)
Dim Dset as DAO. TABLEDEF)//***** must use TableDef object
Dim I as Integer
Dim Tmpprop as DAO. Property//Forced to use DAO type
Dim fld as DAO. Field//mandatory use of DAO type
Dim Tmptxt as String
' On Error Resume Next
Dim msg as String
Dim CDB as DAO. Database//***** Force DAO type
Set cdb = CurrentDb//**** key, determining static references to the current database
Set dset = cdb. TableDefs (tbname)//***** must use TableDef object
For each fld in Dset. Fields
Tmptxt = fld. Name
SetProperty fld, "Caption", Tmptxt
msg = msg + FLD. Properties ("Caption")
msg = msg + CHR (+) + CHR (13)
Next FLD
MsgBox msg
End Sub

In the above section of the code there are two sub, one is SetProperty, to determine whether a field has the specified property, if not set, the corresponding value assigned to the property. The other is displayclumcaption, which is a demo code that sets its Caption property by field name for fields in the specified table. If necessary, you can modify the SetProperty to make him a read-only function that enumerates the caption properties for each field in the specified table. In the Displayclumcaption code, the place to hit "Asterisk" is to focus attention, otherwise it may be more detours in MSDN.