Access快速开发平台--记录每个用户数据表窗体的列宽列序和隐藏列

时　间:2021-07-08 17:51:38
作　者:vincent   ID：20933  城市：广州
摘　要:记录每个用户数据表窗体的列宽列序和隐藏列。
正　文:

      多个客户在使用Access快速开发平台开发的系统，每次给客户升级后，MAIN会复盖，就会把客户的窗体个性化复原了。为了解决这个问题，在后台数据库建一个表来记录每个用户名和窗体名对应字段的列宽，隐藏列，列序，然后再返出恢复原状，问题解决。

附   件:

盟威Access快速开发平台V2.5.1版(32位)记录用户列序列宽隐藏列示例

代   码:

Public Function RememberColumn(Frm As Object)'记录列

    On Error Resume Next
    Dim rst As DAO.Recordset
    Dim UsName As String
    Dim strSQL As String
    Dim str1 As String
    Dim arr()
    Dim ctl As Control
    Dim d1, d2 As Object
    Dim cnn As New ADODB.Connection
    Set cnn = CurrentProject.Connection
    Set d1 = CreateObject("scripting.dictionary")
    Set d2 = CreateObject("scripting.dictionary")
    UsName = GetParameter("Current User Username") '用户名
    Set rst = Frm.Recordset
    strSQL = "delete from [tblUserFrmSetting] where [UserName]=" & SQLText(UsName) & " and [FrmName]=" & SQLText(Frm.Name) '删除同用户名，同窗体名的数据
    cnn.Execute (strSQL)
    For i = 0 To rst.Fields.Count - 1
        d1(rst.Fields(i).Name) = Frm(rst.Fields(i).Name).ColumnOrder '列顺序号
        d2(rst.Fields(i).Name & "Width") = Frm(rst.Fields(i).Name).ColumnWidth '列宽
        If Frm(rst.Fields(i).Name).ColumnHidden = True Then
            d2(rst.Fields(i).Name) = 1 '隐藏的字段
        Else
            d2(rst.Fields(i).Name) = 0
        End If
    Next
    strSQL = ""
    ar = d1.keys
    For i = 0 To UBound(ar)
        strSQL = "insert into [tblUserFrmSetting] ([UserName],[FrmName],[FieldsName],[FieldsColumnSN],[ColumnHide],[ColWidth]) values ('" & UsName & "','" & Frm.Name & "','" & ar(i) & "'," & d1(ar(i)) & "," & d2(ar(i)) & "," & d2(ar(i) & "Width") & ")"
        cnn.Execute (strSQL) '把记录写入表中
    Next
    
    Erase ar
    d1.RemoveAll
    d2.RemoveAll
    Set cnn = Nothing
    Set rst = Nothing
End Function

Public Function LayoutColumn(Frm As Object) '返出列
    On Error Resume Next
    DoCmd.SetWarnings False
    Dim ctl As Control
    Dim cnn As New ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim UsName As String
    UsName = GetParameter("Current User Username") '当前用户名
    strSQL = "select [FieldsName],[ColumnHide],[FieldsColumnSN],[ColWidth] from [tblUserFrmSetting] where [UserName]=" & SQLText(UsName) & " and [FrmName]=" & SQLText(Frm.Name) & " order by FieldsColumnSN"
    Set rs = cnn.Execute(strSQL)
    Do Until rs.EOF
        If rs!ColumnHide Then
            Frm("[" & rs!FieldsName & "]").ColumnHidden = True '隐藏用户列
        End If
        For Each ctl In Frm.Controls
            If ctl.ControlType = acTextBox Then ' 确保控件为文本框
                If ctl.Name = rs!FieldsName Then ' 确保控件的名称与字段名称一致
                    ctl.ColumnOrder = rs!FieldsColumnSN ' 赋值控件的顺序号
                    ctl.ColumnWidth = rs!ColWidth ' 赋值控件的列宽
                End If
            End If
        Next ctl
        rs.MoveNext
    Loop
    DoCmd.SetWarnings True
    Set cnn = Nothing
    Set rst = Nothing
End Function

'调用
Private Sub Form_Close()
    Call RememberColumn(Me)'返出列信息
End Sub

Private Sub Form_Open(Cancel As Integer)
    'ApplyTheme Me
    LoadLocalLanguage Me
    Call LayoutColumn(Me)'记录列信息
End Sub

Access快速开发平台QQ群 (群号:321554481) 　　　　　　Access源码网店