如何从数据字典批量填充Access字段描述、状态栏及控件提示文本？

一、快速从数据字典批量填充字段描述、Control Tip Text和Status Bar Text #

通过VBA脚本遍历数据库对象，匹配数据字典记录后批量赋值，是最高效的实现方式：

1. 填充表字段的Field Description #

Sub UpdateFieldDescriptions()
    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim rs As Recordset
    
    Set db = CurrentDb
    ' 打开数据字典表（假设表名为DataDictionary，含字段：TableName, FieldName, Description）
    Set rs = db.OpenRecordset("DataDictionary")
    
    Do While Not rs.EOF
        On Error Resume Next
        Set tbl = db.TableDefs(rs!TableName)
        If Err.Number = 0 Then
            Set fld = tbl.Fields(rs!FieldName)
            If Err.Number = 0 Then
                fld.Properties("Description") = rs!Description
            End If
        End If
        On Error GoTo 0
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    MsgBox "字段描述更新完成"
End Sub

2. 批量更新控件的Control Tip Text和Status Bar Text #

遍历所有窗体、报表的绑定控件，匹配数据字典后赋值：

Sub UpdateControlTipsAndStatusText()
    Dim db As Database
    Dim obj As Object
    Dim ctl As Control
    Dim rs As Recordset
    Dim strTableName As String
    Dim strFieldName As String
    Dim strDesc As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("DataDictionary")
    
    ' 处理窗体
    For Each obj In CurrentProject.AllForms
        DoCmd.OpenForm obj.Name, acDesign
        For Each ctl In Forms(obj.Name).Controls
            If ctl.ControlType <> acLabel And Not IsNull(ctl.ControlSource) Then
                ' 拆分控件源获取表名和字段名（格式为"表名.字段名"）
                strTableName = Split(ctl.ControlSource, ".")(0)
                strFieldName = Split(ctl.ControlSource, ".")(1)
                rs.FindFirst "TableName='" & strTableName & "' AND FieldName='" & strFieldName & "'"
                If Not rs.NoMatch Then
                    strDesc = rs!Description
                    ctl.ControlTipText = strDesc
                    ctl.StatusBarText = strDesc
                End If
            End If
        Next ctl
        DoCmd.Close acForm, obj.Name, acSaveYes
    Next obj
    
    ' 处理报表（逻辑同窗体）
    For Each obj In CurrentProject.AllReports
        DoCmd.OpenReport obj.Name, acDesign
        For Each ctl In Reports(obj.Name).Controls
            If ctl.ControlType <> acLabel And Not IsNull(ctl.ControlSource) Then
                strTableName = Split(ctl.ControlSource, ".")(0)
                strFieldName = Split(ctl.ControlSource, ".")(1)
                rs.FindFirst "TableName='" & strTableName & "' AND FieldName='" & strFieldName & "'"
                If Not rs.NoMatch Then
                    strDesc = rs!Description
                    ctl.ControlTipText = strDesc
                    ctl.StatusBarText = strDesc
                End If
            End If
        Next ctl
        DoCmd.Close acReport, obj.Name, acSaveYes
    Next obj
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    MsgBox "控件提示文本和状态栏文本更新完成"
End Sub

二、开启Control Tip Text的可继承字段属性 #

Access默认仅开启Description（对应状态栏文本）的继承，需手动为字段添加可继承的ControlTipText属性：

Sub AddInheritableControlTipProperty()
    Dim db As Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim prp As Property
    
    Set db = CurrentDb
    For Each tbl In db.TableDefs
        ' 跳过系统表
        If Left(tbl.Name, 4) <> "MSys" Then
            For Each fld In tbl.Fields
                On Error Resume Next
                Set prp = fld.Properties("ControlTipText")
                If Err.Number <> 0 Then
                    ' 创建可继承的ControlTipText属性
                    Set prp = fld.CreateProperty("ControlTipText", dbText, "")
                    prp.Inherited = True
                    fld.Properties.Append prp
                End If
                On Error GoTo 0
            Next fld
        End If
    Next tbl
    Set db = Nothing
    MsgBox "ControlTipText可继承属性添加完成"
End Sub

执行该函数后，修改字段的ControlTipText属性，即可通过Inheritable Field Properties的Propagate功能同步到绑定控件。

三、用数据字典作为Lookup Table实现动态关联 #

若想实时同步数据字典的更新，可通过以下两种方式实现：

1. 窗体/报表加载时动态读取 #

给窗体的Load事件添加代码，每次打开窗体时自动从数据字典同步：

Private Sub Form_Load()
    Dim ctl As Control
    Dim rs As Recordset
    Dim strTableName As String
    Dim strFieldName As String
    
    Set rs = CurrentDb.OpenRecordset("DataDictionary")
    For Each ctl In Me.Controls
        If ctl.ControlType <> acLabel And Not IsNull(ctl.ControlSource) Then
            strTableName = Split(ctl.ControlSource, ".")(0)
            strFieldName = Split(ctl.ControlSource, ".")(1)
            rs.FindFirst "TableName='" & strTableName & "' AND FieldName='" & strFieldName & "'"
            If Not rs.NoMatch Then
                ctl.ControlTipText = rs!Description
                ctl.StatusBarText = rs!Description
            End If
        End If
    Next ctl
    rs.Close
End Sub

2. 单个控件用DLookup函数直接绑定 #

针对少量控件，可直接在属性面板中设置：

• Control Tip Text属性设为：=DLookup("Description", "DataDictionary", "TableName='目标表名' AND FieldName='目标字段名'")
• Status Bar Text属性同理，无需编写批量脚本。

内容的提问来源于stack exchange，提问作者Bugman160