• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1973
  • Last Modified:

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

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
   Next obj

Thanks,
Steve

 
0
ccsgc
Asked:
ccsgc
  • 3
  • 2
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
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
        .Properties.Refresh
        Set prp = .CreateProperty(strPropertyName, dbText, varPropVal)
        .Properties.Append prp
    End With
   
    Set prp = Nothing
    Set qdf = Nothing
    Set db = Nothing
   
End Sub
0
 
ccsgcAuthor Commented:
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).

0
 
Leigh PurvisDatabase DeveloperCommented:
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
0
 
ccsgcAuthor Commented:
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!
0
 
Leigh PurvisDatabase DeveloperCommented:
OK cool.  
Glad to help.
0
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

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now