UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Set query property using VBA    
 
   
grazird
post Sep 12 2008, 06:29 PM
Post#1



Posts: 401
Joined: 21-June 07
From: Olympia, WA


Hi All,
set out to do what I thought would be relatively simple; however, after struggling and not finding much help on this subject, discovered it may be more difficult than I anticipated.
Simply put, I would like to set the "Filter" property of a query using VBA. "Filter" is one of the properties listed when creating a query using the GUI, but I can not find out how to reference it from VBA.
After many attempts and searches for more info, I have been unable to find anything related to this. Does anyone have any additional info regarding this?
Thanks,
Robert
Go to the top of the page
 
vtd
post Sep 12 2008, 07:43 PM
Post#2


Retired Moderator
Posts: 19,667
Joined: 14-July 05



Something like:
CODE
CurrentDB.QueryDefs("VTDQuery_Union").Properties("Filter") = "[Employee] = 3077"
Go to the top of the page
 
LPurvis
post Sep 13 2008, 08:11 AM
Post#3


UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)


Just remember that, as with many extended properties in DAO, the property might not exist yet and so need creating before it is referred to or set.
don't know why (I really can't imagine) - but I had this lying around
CODE
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

Though I'd probably suggest making that more traditional
CODE
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
        Set prp = qdf.Properties(strPropertyName)
        If Err Then
            Set prp = .CreateProperty(strPropertyName, dbText, varPropVal)
            .Properties.Append prp
        Else
            prp.Value = varPropVal
        End If
    End With
    
    Set prp = Nothing
    Set qdf = Nothing
    Set db = Nothing
    
End Sub

SetQueryProperty "VTDQuery_Union", "Filter", "[Employee] = 3077"
Bear in mind that this (as an extended/application property) really is only relevant to Access.
i.e. displaying thre query results in the Access UI.
In an application - queries in such a mode are not considered good practice to offer to a user as part of the UI.
Cheers.
Go to the top of the page
 
vtd
post Sep 13 2008, 06:11 PM
Post#4


Retired Moderator
Posts: 19,667
Joined: 14-July 05



Thanks, Leigh.

Forgot that it is an Access property. The Query I used to test must have had some filter set before so it worked OK.

Cheers
Go to the top of the page
 
grazird
post Sep 15 2008, 05:55 PM
Post#5



Posts: 401
Joined: 21-June 07
From: Olympia, WA


Awesome info! Thank you both for your help!
obert
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 03:18 AM