How can my form allow the user to select fields to sort on?

[sueb]

I have a database that was created by a SQL programmer, and it's all wonderful, except that the programmer never completed it, and it's pretty sophisticated for my user community, so that means no one can complete it or maintain it.

I'm re-writing its functionality (and completing it!) so that it just uses familiar Access utilities. It doesn't need anything fancy, so although it's taking me a little time to accomplish, it's going to be simpler for the users to maintain, to the extent that they are able.

Currently, there is a form that presents one unbound control that functions as a filter for a specific field, and three pairs of controls, each pair of which allows the user to select a field and a sort order for that field. (I've attached a picture of the form.)

The underlying code simply runs a query and presents the data table, but I want to present the data in a sensibly-designed form instead of just in an unwieldly line of fields marching off to the right. Here's the code, and how do I make the code use the form I've created instead of just the raw query:

'Open the selected query.  Replace the order by clause with the order selected by user.
Private Sub cmdViewCategoryData_Click()
    
    Dim DB
    Dim qd As DAO.QueryDef
    'Dim rs As Recordset
    Dim Sort As String
    Dim sql As String
    Dim queryName As String
    Dim querySql As String
    Dim OrderByPosition As Integer
        
    Sort = " ORDER BY [" & Me.cboSort1.Value & "] " & Me.FirstSortAscDesc.Value & ", [" & Me.cboSort2.Value & "] " & Me.SecondSortAscDesc.Value & ", [" & Me.cboSort3.Value & "] " & Me.ThirdSortAscDesc.Value
    'Sort = " ORDER BY [" & Me.cboSort1.Value & "], [" & Me.cboSort2.Value & "], [" & Me.cboSort3.Value & "]"
    queryName = "qry" & Me.cboHospCat.Value
    
    Set DB = CurrentDb()
    Set qd = DB.QueryDefs(queryName)
    querySql = qd.sql
    
    'If there is an Order By clause, replace it with new order by, or else add it to end:
    If InStr(querySql, "ORDER BY") > 0 Then
        OrderByPosition = InStr(querySql, "ORDER BY")
    Else
        OrderByPosition = InStr(querySql, ";") 'Len(querySql)
    End If
    
    querySql = Left(querySql, OrderByPosition - 1)
    querySql = querySql & Sort & ";"
    qd.sql = querySql

    DoCmd.OpenQuery queryName
    Set qd = Nothing
    Set DB = Nothing

End Sub

** Edit **
[imgnothumb]http://bytes.com/attachments/attachment/5332d1313617944/billhold-form.jpg[/imgnothumb]

[NeoPa]

This sounds like some fun Sue.

1. Start by designing a form that will show the data as you want it. This is what you will use to display the data that you have filtered and sorted by your three fields.
2. For the filtering, I suggest you go and have a play in Example Filtering on a Form. It includes an example database you can play with, and works like a tutorial for the concept of filtering. It covers most of what you need to do this whole job.
3. The sorting of the data using the three fields specified in your three pairs of controls is similar but slightly different. You will use the data in these controls to form an OrderBy string which matches the ORDER BY clause of the SQL of the underlying query. Unfortunately, whereas the filtering is supported by a parameter to DoCmd.OpenForm( ), the sorting is not. This means you will need to apply this after the form has already been opened. This can be done by passing the data as OpenArgs or setting the properties explicitly in the calling code after the form's been opened. We can cover details for this later should you decide this route appeals to you.

Actually, that will be it. It's a good approach and makes it look professional. I like it.

[sueb]

Thanks for this, NeoPa! I certainly will dive into this this evening after work, and will report back (of course, you know I will, since there will be further questions! :D)

[sueb]

I thought I'd just check out that thread now, even though I can't start on it yet, but something must be wrong with the link or page or something cuz I get an error every time I click on it.

[NeoPa]

I've heard some people are temporarily having problems on the site with IE 8. I tested it here and all seems fine to me with Mozilla Firefox 5. Let me know how you manage (and if there are further questions them happily bring them on).

[sueb]

I still can't access that page today, and I'm not allowed to use another browser here at work. I don't suppose you could sort of paste the gist of it in a message could you? Or maybe email it to me or something?

[NeoPa]

Done Sue.

[sueb]

Thanks! You're the best, NeoPa!

[NeoPa]

I'm very happy to have helped Sue :-)

[sueb]

Let me apologize, starting off, for not having had time to do my homework with the filtering example you posted, NeoPa, but maybe you'll be willing to clear up this one thing for me anyway.

I'm borrowing some code from the original database, but I'm getting an error that I don't understand. Here are the lines of code in question:

    Dim DB
    Dim qd As DAO.QueryDef
    Dim Sort As String
    Dim sql As String
    Dim queryName As String
    Dim querySql As String
    Dim OrderByPosition As Integer
        
    Sort = " ORDER BY [" & Me.cboSort1.Value & "] " & Me.FirstSortAscDesc.Value & _
            ", [" & Me.cboSort2.Value & "] " & Me.SecondSortAscDesc.Value & _
            ", [" & Me.cboSort3.Value & "] " & Me.ThirdSortAscDesc.Value
    queryName = "qry" & Me.cboHospCat.Value
    
    Set DB = CurrentDb()
    Set qd = DB.QueryDefs(queryName)

When it hits line 16, it complains that "Item not found in this collection." This code works perfectly in the original database. I don't really understand the whole "Dim DB" thing, so I don't know why my version doesn't seem to have ".QueryDefs ". (I'm assuming that's the "item" that isn't being found.) Maybe you can point me in the right direction about what I'm missing in my version that is in the original?

Thanks for any advice!

[NeoPa]

The error message indicates that the QueryDef (saved query), called qryX where X represents the current value of cboHospCat, doesn't exist in your database. Does that help?

Your code doesn't make a lot of sense out of context I'm afraid, so I'm not in a good position to determine what you're trying to do or suggest any alternatives at this stage.

[sueb]

Well, my form does have all the combo boxes referenced in the code. I couldn't copy the whole form (attached to my original post) from the original database into mine, but I could (and did) copy the combo boxes into a new form in my new database. All the properties copied, so I assumed that the code would work (certainly at least to the point of being able to create the query).

[sueb]

When I set a break at line 18, queryname = "qryEmergen cy", which is what I would expect, and Sort = " ORDER BY [DSCHRG_DT] ASC, [ACCT_BAL] ASC, [PAT_ACCT_NBR] ASC", which is also what I would expect. However, qd = Nothing -- does that seem quite right? When I hover over qd.sql in line 19, it says "qd.sql = <Object variable or With block variable not set>". Does that just mean that .sql hasn't been set yet? Or does it mean something more sinister about the validity of qd's declaration?

[NeoPa]

I'm still reading that as saying the database doesn't contain a QueryDef object called [qryEmergency].

BTW Are you aware that you can export forms to files and import them again from files? That may be a way to transfer the original form across even if the database versions are different.

[sueb]

Okay, since it looks to me like I've defined an object called queryname that has a value of "qryEmergen cy", where in the database do I need to create the QueryDef object, and how do I do it?