How to set a query's Filter, OrderBy and OrderByOn properties from vba

Posted on 2006-11-25
Medium Priority
Last Modified: 2009-07-29
I know it is possible to dynamically change the queries SQL, but for my application I specifically want to alter the queries property sheet's to mimic the user using the A-Z and Filter On buttons in the query's toolbar. I have suceeded in dynamically doing this for a report using code similar to the following:

With Reports![rptBookings]
    .Filter = strFilter
    .FilterOn = True
    .OrderBy = strSortOrder
    .OrderByOn = True
End With

This does not work with queries. After many hours I have managed to list the query's properties using code described on this site (see below) and have modified that code so I can alter the OrderBy and OrderOn of my query SO LONG AS ORDERBY HAS ALREADY BEEN GIVEN A VALUE. My question is, how do I set OrderBy, etc, regardless of their previous state and content.

For Each obj In dbs.QueryDefs
        For cnt = 0 To obj.Properties.Count - 1
            On Error Resume Next
            Debug.Print obj.Properties(cnt).Name & "   " & obj.Properties(cnt).Value
   Next obj


Question by:ccsgc
  • 3
  • 2
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18012353
So - this imples that you're going to be displaying the raw query to the user - as displayed from the Database window?

Many, myself included, feel that's something best avoided in development.  That access to data should be entirely through forms.
(You'd then aquire the trivial properties as you displayed for reports earlier).

As for doing it in a query... you're right - a property can only be set if it already exists.
And you can't add a property if it already exists (as you can using ADO).
So you have to check first - if it exists change it - if not create it.
Or - you can cheat and attempt to delete the property... if it fails, ignore the error and then add the property regardless.

SetQueryProperty "QueryName", "Filter", strFilter
SetQueryProperty "QueryName", "OrderBy", strSortOrder

Where you've saved...

Sub SetQueryProperty(strQueryName As String, strPropertyName As String, varPropVal)
On Error Resume Next

    Dim db As Database
    Dim qdf As QueryDef
    Dim prp As DAO.Property
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQueryName)
    With qdf
        .Properties.Delete strPropertyName
        Set prp = .CreateProperty(strPropertyName, dbText, varPropVal)
        .Properties.Append prp
    End With
    Set prp = Nothing
    Set qdf = Nothing
    Set db = Nothing
End Sub

Author Comment

ID: 18012550
Thanks. Take your point about avoiding displaying raw queries, but this is very quick-and-dirty development at this stage for a very few users. Moreover, I've spent so much time trying to figure this out that I am determined to see the answer.

Your code snippet worked a treat. However, I can't figure out how to programatically turn the OrderBy on and off. I'm sure it needs the OrderByOn property toggled between False and True, but how? (The filter works fine with DoCmd.ApplyFilter , strFilter).

LVL 44

Accepted Solution

Leigh Purvis earned 1000 total points
ID: 18012898
I don't think you'll get the same level of response you would with a form or report.
If you run an action like ApplyFilter then yeah sure - that's an Access method - running on an open object that, otherwise, doesn't really respond to much.
Sure you can set the queries properties - but it isn't going to react to them until you next open it.
(Again - form's benefits become clear - the level of control is hugely different).

(As an aside - the above code always assumes a text type property - you could change that to be a boolean result for the OrderByOn - but I don't think that'll make much difference).
To change a property that exists (like OrderByOn) is simply

qdf.Properties(strPropertyName) = varPropVal

But as I said before - don't expect it to do anything until the next opening... :-S

Author Comment

ID: 18014013
OK, I see what you are getting at. I will re-consider using this kind of technique in my development - but I'm glad I know the answer! I am fairly new to vba and your answer has also helped me to understand syntax pertaining to the properties collection. Thanks!
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18014014
OK cool.  
Glad to help.

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

619 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question