none
Use form filter for query criteria RRS feed

  • Question

  • Form XYZ has filter property; if you look at in the design view the Filter property will have a String - let's call that S1....and S1 can change depending on what the user is opting for, which is fine.....

    Can one use S1 string as a criteria for a new query?

    Am scratching my head as to how it might be done......

    Friday, July 24, 2015 6:55 PM

Answers

  • You could use code like this:

        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef(Name:="qryNew", _
            SQLText:="SELECT * FROM tblData WHERE " & Me.Filter
        dbs.QueryDefs.Append qdf

    Modify to match your database.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 24, 2015 7:12 PM

All replies

  • Hi,

    How is the user able to change S1? The only way it can be used in a query is if the form has a textbox that also has the same value as S1. Or, if when the user changes S1 that it is also stored in a TempVar at the same time.

    Just a thought...

    Friday, July 24, 2015 7:07 PM
  • Can one use S1 string as a criteria for a new query?

    Hi msdn....,

    You can change the filter property to get a new selection. Just assign a new value:  Me.Filter = ....

    In my applications I do not use the filter property, but I manipulate the RecordSource. In this way I can influence the selection criteria, sorting order, even add some joins to make a selection on some "rarely joined" fields.

    Imb.

    Friday, July 24, 2015 7:11 PM
  • You could use code like this:

        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef(Name:="qryNew", _
            SQLText:="SELECT * FROM tblData WHERE " & Me.Filter
        dbs.QueryDefs.Append qdf

    Modify to match your database.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, July 24, 2015 7:12 PM
  • thanks all....HV your code works - impressive command of vba !!!! ..... but the last line throws an error.... Invalid Operation.....  It worked none the less, and so I deactivated the last line and it is still working... I see qryNew in the query objects in the navigation pane once it is closed and reopened....and it has the correct results....

    I believe I can carry on from this point - - - and so  - - much thanks.....

    Friday, July 24, 2015 7:39 PM