Solved

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

Posted on 2006-11-25
5
1,818 Views
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
   Next obj

Thanks,
Steve

 
0
Comment
Question by:ccsgc
  • 3
  • 2
5 Comments
 
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
        .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
 

Author Comment

by:ccsgc
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).

0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 250 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
0
 

Author Comment

by:ccsgc
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!
0
 
LVL 44

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now