How to pass a parameter into a query to sort by a particular order

[JONeill]

Trying to pass a parameter into a query to sort a recordset. I was keeping a recordset in memory to sort it but the rowsource will not take a recordset directly. So I figured I could use a parameter to pass into the query and sort it that way. The code works if I hard code the sort criteria but if I just get a bunch of records in no particular order if I pass a paremter in. Is it even possible to do this in Access? Here is the code.

'************** Calling Sub ************************

Private Sub GetMaintLogBySort()
    'Init sort criteria
    sortStates = Array("ProjectName ASC", "ProjectName DESC", "Location ASC, ProjectName ASC", _
        "Location DESC, ProjectName DESC", "StartDate ASC", "StartDate DESC")
        
    sortState = sortStates(SortStateIndex.Project_Asc)
    Set rs = IData.GetMaintLogBySort(sortState)
    
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        
        Do Until rs.EOF
            Debug.Print rs!projectName & ", " & rs!location & ", " & rs!startDate
            rs.MoveNext
        Loop
        
    Else
        Debug.Print "No records in recordset..."
        
    End If
    
End Sub


'**************  GetMaintLogBySort() ************************
Public Function GetMaintLogBySort(ByVal sortOrder As String) As Recordset
    Dim qDef As QueryDef
    
    'Don't flag error
    On Error Resume Next
    
    'Init function
    'Set GetMaintLog = New clsMaintLog
    
    'Set database object
    Set db = CurrentDb
    'Set query to execute
    Set qDef = db.QueryDefs("GetMaintLogBySort")
    
    qDef.Parameters("@SortOrder") = sortOrder
    
    'Execute
    Set rs = qDef.OpenRecordset()
    Debug.Print "Record Count: " & rs.RecordCount
    
    Set GetMaintLogBySort = rs
    
End Function

'************** Query being called ************************
SELECT ProjectName, Location, StartDate
FROM MaintLog
ORDER BY SortOrder;

 

[JONeill]

One quick thing. The calling function should not have the ampersand in the parm. I created the code without changing it. It doesn't work either way.

 

[welshgasman]

I do not believe you can have two modules called the same regardless of whether one is a sub and another a function.?
Not sure what this does?

sortState = sortStates(SortStateIndex.Project_Asc)

You can test that it works by just running the query and pasting in a relevant value to the prompt.
I'd also walk through the code checking values are what you think they are.
You could Debug.Print Qdf.Sql after setting the parameter.?

I'd also Dim all my variables as well.

 

[JONeill]

sortStates is an array of strings with the actual SQL "ORDER BY" criteria as in "ProjectName ASC".
SortStateIndex is an Enum with index number of the various ways a user can sort the recordset
sortState is the Order By code that holds the current sort order.
The variables are either at the private or public level so they are declared
The query doesn't work when I type in the parm. I guess you just can't do that with Access. So, I'll have to sort the recordset and add the items to the listbox indivdually. I think that's gonna mess up my colum headers.

 

[welshgasman]

Try a different approach then.
Get the SQL from the qdf and append the sort order?

 

[JONeill]

Well, not being familar with Access, I was really needing to use the recordset (instead of the rowsource) property of the listbox and that's what I did. So, I won't have to pass a parameter in to the Query which was not gonna work anyway. Even when I try to append something to the queryDef it just executes what's in the query. So, what I'm doing is just calling a sub that gets the data for the listbox and then calls a sort function based on the current data sort. Not onlly that, I can keep a recordset in memory and not make a call unless I change the filter on the search or reset the filter.


What frustrates me just learning this is how Access form controls don't seem to behave the same way Excel form controls do. For example, I found out from another post that I need to place a dummy control on the form and set focus to it before hiding a button. Also, to set the listIndex property of a listbox, I have to set focus to it. The error messages are not helpful either because all they say is that is something like "Invalid use of this property". Which leads me to believe that I just can't set it. I just don't understand why VBA and controls can't behave like the other office products let alone like every other MS development product I've ever used. You'd kind of think that might be a consideration. So, problem resolved I guess.

 

[welshgasman]

What I was thinking was extract the SQL, append your sort order, and use that string for your recordset.?
I cannot see how that would ignore the sort order.
In fact it is quite a good idea, having the base query and then tailoring for the sort order.

Sub ReadQdf()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strQuery As String


strQuery = "Query1"
Set db = CurrentDb()
Set qdf = db.QueryDefs(strQuery)
Debug.Print qdf.SQL
qdf.Close


Set qdf = Nothing
Set db = Nothing


End Sub

 

[xenou]

What frustrates me just learning this is how Access form controls don't seem to behave the same way Excel form controls do. For example, I found out from another post that I need to place a dummy control on the form and set focus to it before hiding a button. Also, to set the listIndex property of a listbox, I have to set focus to it. The error messages are not helpful either because all they say is that is something like "Invalid use of this property". Which leads me to believe that I just can't set it. I just don't understand why VBA and controls can't behave like the other office products let alone like every other MS development product I've ever used. You'd kind of think that might be a consideration. So, problem resolved I guess.

Actually its Excel controls that are the oddballs. Now you can find out how they are really supposed to work, at last. But error messages are in general not always helpful, that's true. But not completely an Access problem (I can't help but think of the ubiquitous Error 1004 "Application-defined or Object-defined error" or the ever-helpful error 400.


As far as the original problem,it's not at all clear how you expect a parameter to sort your query results. How does the actual sorting get done here?