vba - 在 Access 表单上取消排序或禁止排序

Question

我有一个带有数据表子表单的 Access 表单。此数据表子窗体显示动态创建的记录集(数据透视表)，并使用一些 VBA 动态加载该数据透视表，同时允许可变数量的列。

我想禁止对该子表单进行排序，因为一旦对子表单进行排序，应用程序就会崩溃(不是 VBA 错误，而是应用程序不可恢复的崩溃)。我已经禁用了右键菜单，但人们仍然可以使用顶部菜单进行排序，我不想禁用主菜单。

无论如何要禁用排序操作(就像我们可以通过将 Form.AllowFilters 设置为 False 来处理过滤器一样)，或者在它运行之前拦截它？

我已经尝试在 Form_ApplyFilter 操作和 Form_Filter 操作上设置 Cancel = True :

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    Cancel = True
End Sub
Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
    Cancel = True
End Sub

但是，这无济于事。

相关代码重现

子窗体包含 256 个名为 Text0 到 Text255 的文本框，它的默认 View 是数据表 View 。表单和所有文本框都是未绑定(bind)的。

子窗体上的相关代码:

Public Function LoadRS(myRS As Recordset)
    Dim i As Long
    Dim myTextbox As textbox
    Dim fld As Field
    i = 0
    With myRS
        For Each fld In myRS.Fields
            Set myTextbox = Me.Controls("Text" & i)
            myTextbox.Properties("DatasheetCaption").Value = fld.NAME
            myTextbox.ControlSource = fld.NAME
            myTextbox.ColumnHidden = False
            i = i + 1
        Next fld
    End With
    For i = i To 255
        Set myTextbox = Me.Controls("Text" & i)
        myTextbox.ColumnHidden = True
    Next i
    Set Me.Recordset = myRS
End Function

在主要形式上:

Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef
Set qd = CurrentDb.CreateQueryDef("", "TRANSFORM Max(measurementValue) AS MaxOfValue " & _
    " SELECT measurementValue, measurementCategory, measurementDate " & _
    " From MyTable " & _
    " WHERE ID = ?"  & _
    " GROUP BY measurementCategory" & _
    " PIVOT measurementDate "
qd.Parameters(0) = Me.ID
Set rs = qd.OpenRecordSet()
Me.subformControl.SourceObject = "mySubform"
Me.subformControl.Form.LoadRS rs

Answer 1

问题在于，当使用参数化记录集作为源时，Access 会使用无效的 SQL 手动填充表单的记录源属性。

我使用的解决方案是设置记录源属性，而不是记录集属性:

Public Function LoadSQL(sqlString As String)
    Dim myRS As DAO.RecordSet
    Set myRS = CurrentDb.OpenRecordset(sqlString)
    Dim i As Long
    Dim myTextbox As textbox
    Dim fld As Field
    i = 0
    With myRS
        For Each fld In myRS.Fields
            Set myTextbox = Me.Controls("Text" & i)
            myTextbox.Properties("DatasheetCaption").Value = fld.NAME
            myTextbox.ControlSource = fld.NAME
            myTextbox.ColumnHidden = False
            i = i + 1
        Next fld
    End With
    For i = i To 255
        Set myTextbox = Me.Controls("Text" & i)
        myTextbox.ColumnHidden = True
    Next i
    Me.RecordSource = sqlString
End Function

有点荒谬的是，我在问题中提供的代码对表单进行了块排序。但显然还不够早，无法防止 Access 崩溃。