How to sort Datasheet which has RecordSet created from SQL Server stored proc? RRS feed

  • Question

  • I have a form set to Datasheet View.  I use a DAO.QueryDef to create the RecordSet object because it is an expensive query for Access.

    But I get this error when I try to use the Datasheet GUI to sort:

    A button click runs GetResults() method.
    Here is the code:

    Private Sub GetResults(JobCode As String, FromDate As Date, ToDate As Date)
        Set Me.Child0.Form.Recordset = GenerateRecordSet(Me.cbxJobCode, Me.txtFromDate, Me.txtToDate)
        Me.txtRevenue = GetRevenueTotal(Me.cbxJobCode, Me.txtFromDate, Me.txtToDate)
        Me.txtOpenBals = GetOpenTotal(Me.cbxJobCode, Me.txtFromDate, Me.txtToDate)
    End Sub
    Private Function GenerateRecordSet(JobCode As String, FromDate As Date, ToDate As Date) As DAO.Recordset
        On Error GoTo Error_Handler
        Dim db As DAO.Database
        Dim qDef As DAO.QueryDef
        Set db = CurrentDb
        Set qDef = db.CreateQueryDef("")
        qDef.ReturnsRecords = True
        qDef.Connect = db.TableDefs("Orders").Connect
        qDef.sql = "usp_RevenueDetailsByJobCodeAndDate " & JobCode & ", '" & Format(FromDate, "mm-dd-yyyy") & "', '" & Format(ToDate, "mm-dd-yyyy") & "' "
        Set GenerateRecordSet = qDef.OpenRecordset()
    End Function

    Wednesday, November 30, 2016 9:54 PM

All replies

  • Hi,

    Just a guess, but could you try something like?

    Me.Child0.Form.OrderBy = "FieldName"
    Me.OrderByOn = True

    Just a thought...

    Wednesday, November 30, 2016 10:47 PM
    I get same error with that DBGuy. This is also leading down the road to having to customize every stored proc to return a sorted RecordSet. Is there really no way to use the built in Datasheet column headers for sorting or filtering?
    Thursday, December 1, 2016 6:13 PM
  • Sorry to hear it didn't work although I noticed I made a syntax mistake, which you probably caught.

    I think Me.OrderByOn should have been Me.Child0.Form.OrderByOn.

    Other than that, I am wondering if you'll need to temporarily store a copy of the recordset in a local table to be able to sort the data. It might work if you're not updating the data or if it's read-only. Otherwise, you will have to update SQL Server with the changes to the local table.

    Just a thought...

    Thursday, December 1, 2016 6:22 PM