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 **

[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 = "qryEmergency", 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 "qryEmergency", where in the database do I need to create the QueryDef object, and how do I do it?

[NeoPa]

Not quite Sue, but you're thinking in the right direction at least.

You have a simple string variable called queryName defined on line #9. This is set on line #15 to be equal to "qry" plus the value in Me.cboHospCat. This indicates to me you have a control (almost certainly a ComboBox from the name) which, at the time of testing, was set to the string value "Emergency" (hence the resulting value of queryName was "qryEmergency").

When, on line #18, you tried to reference QueryDefs("qryEmergency") it said, in computer speak, "Don't be daft girl! We have no [qryEmergency] here".

QueryDefs sound complicated, but they're simply the term that refers to saved query objects. Most people think of them simply as queries (They're even stored under the tab called queries in the database window), but they're not really queries as such. They include queries perhaps, but the object that they are is a QueryDef. In short the error is that the query of that name doesn't exist in your database.

[sueb]

OMG, so that's what's missing! Yes, in the original database, there are a slew of queries named exactly this kind of thing! Lovely--I'll just copy them all over. Thanks, NeoPa!

(the dawning of that particular light was more of a lighting bolt--almost gave me a headache! :D)

[NeoPa]

NeoPa:
"Don't be daft girl! We have no [qryEmergency] here"

If the translation helps that much there are threads here that deal with reconfiguring the error messages. You could set up a special version called "Access 2010 With Attitude!".

[sueb]

Am I able to create queries on the fly? I'm thinking of the day when the user creates a new Hospital Category; it would be lovely if the code could just handle that.

[NeoPa]

The short answer is Yes, but that doesn't allow for much. The variations on what you could mean when you ask that question (as opposed to the question itself) leave it fairly meaningless. However, the full question should be posted in a separate thread I suspect.

[sueb]

Okay, will do--thanks!