Solved

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

Posted on 2006-11-25
5
1,891 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

617 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