Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

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

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.


    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;

  2. #2
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to pass a parameter into a query to sort by a particular order

    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.

  3. #3
    Board Regular
    Join Date
    May 2013
    Posts
    552
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to pass a parameter into a query to sort by a particular order

    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?
    Code:
    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.
    Last edited by welshgasman; Apr 19th, 2019 at 04:05 AM.
    Office 2007
    Access novice. Sometimes trying to give something back

  4. #4
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to pass a parameter into a query to sort by a particular order

    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.

  5. #5
    Board Regular
    Join Date
    May 2013
    Posts
    552
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to pass a parameter into a query to sort by a particular order

    Try a different approach then.
    Get the SQL from the qdf and append the sort order?
    Office 2007
    Access novice. Sometimes trying to give something back

  6. #6
    Board Regular
    Join Date
    Sep 2018
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to pass a parameter into a query to sort by a particular order

    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.

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    552
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to pass a parameter into a query to sort by a particular order

    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.

    Code:
    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
    Office 2007
    Access novice. Sometimes trying to give something back

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,315
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to pass a parameter into a query to sort by a particular order

    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?
    Last edited by xenou; Apr 22nd, 2019 at 10:55 AM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •