Column Heading Sort Order

[PC User]

I'm trying to sort by clicking on the label in the header of a continuous subform list, but I'm getting an error saying that the recordsource is locked. I haven't encountered this type of error before. Usually I can change the recordsource while the form or subform is open. Does anyone have any ideas on how to resolve this?

Private Function SortOrder(col As String, xorder As String) As Integer
On Error GoTo Whoops

    strSelect = "SELECT DISTINCTROW JournalSortId, ColumnDesc, ColumnName, SortDirection "
    strFrom = "FROM tblJournalSort "
    strOrderBy = "ORDER BY " & col & " " & xorder

    strSQL = strSelect & strFrom & strOrderBy
    Me.RecordSource = strSQL
    Me.Form.Requery

OffRamp:
    Exit Function
Whoops:
    MsgBox "Error #" & Err & ": " & Err.Description
    Resume OffRamp
End Function

Private Sub lblSortBy_Click()
    Dim response As Integer
    If strSortOrder = "DESC" Then
        response = SortOrder("ColumnDesc", "Asc")
        strSortOrder = "asc"
    Else
        response = SortOrder("ColumnDesc", "Desc")
        strSortOrder = "DESC"
    End If
End Sub

Private Sub lblDirection_Click()
    Dim response As Integer
    If strSortOrder = "DESC" Then
        response = SortOrder("SortDirection", "Asc")
        strSortOrder = "asc"
    Else
        response = SortOrder("SortDirection", "Desc")
        strSortOrder = "DESC"
    End If
End Sub

 

[RuralGuy]

I'm not sure what is causing your error but I do not think the .Requery is necessary since changing the RecordSource should cause the requery. What version of Access are you using and what OS?

 

[PC User]

I'm using Access 2007 on Windows Vista.

 

[RuralGuy]

I'm using Access 2007 on Windows Vista.

Are both Vista and ac2007 up to date? Have you applied the ac2007 HotFixes as well?

 

[PC User]

I successfully used this code in several databases when I was using Access 2000. Look like I'll need to spend some more time to figure out why it doesn't work well in the newer version.

 

[RuralGuy]

I do similar things in a Continuous form to change the sort order. Are you running an MDB in ac2007 or are you using the 2007 format ACCDB?