vba - Cancel sorting or disallow sorting on Access form

Question

I have an Access form with a datasheet subform. This datasheet subform is displaying a dynamically created recordset (a pivot), and is using some VBA to dynamically load that pivot while allowing for a variable amount of columns.

I'd like to disallow sorting on that subform, since the application crashes as soon as the subform is sorted (not a VBA error but an unrecoverable crash of the application). I have already disallowed the right click menu, but people are still able to sort using the top menu, and I don't want to disable the home menu.

Is there anyway to either disable the sort action (like we can do with filters by setting Form.AllowFilters to False), or intercept it before it gets run?

I've tried setting Cancel = True on both the Form_ApplyFilter action and the Form_Filter action:

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

However, this does not help.

Relevant code to reproduce

The subform contains 256 textboxes named Text0 to Text255, and it's default view is datasheet view. Both the form and all textboxes are unbound.

Relevant code on the subform:

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

And on the main form:

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

The problem was that Access manually fills in the form's record source property with invalid SQL when using a parameterized recordset as source.

The solution I used was to set the record source property, instead of the recordset property:

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

The somewhat ridiculous part is that the code I provided in the question did block sorting on the form. But not early enough to keep Access from crashing, apparently.