none
qry used in dao.recordset does NOT get value from the form RRS feed

  • Question

  • but when I run the qry in Navigation Pane or Run in Qry tools it works.

    But does NOT get value as critera    when qry  has  critera  of me.txtbox1.value

    do I need to use a 'QDF" somehow?

    and call the critera I get from a txtbox on a form a parameter

    then run or exucite the QDF instead of a qry?

    Mark J


    Mark J

    Wednesday, September 14, 2016 10:18 AM

Answers

  • If you're using DAO to open a recordset, more or less like this:

        Set rs = CurrentDb.OpenRecordset("qryMyQuery")

    ... then if the query has any reference to a control on a form, then DAO will look at that reference as a parameter, and you need to specify the value for that parameter.  DAO doesn't know anything about Access objects.  One flexible way to do it is to use a QueryDef object and the Eval function, like this:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rs As DAO.Recordset
    
    Set db = CurrentD
    Set qdf = db.QueryDefs("qryMyQuery")
    
    For Each prm in qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    
    Set rs = qdf.OpenRecordset()
    
      

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, September 14, 2016 4:49 PM
  • Looks like I need to learn more about this.      Any recommended sites to read about this.

    All the sites I found so far, were not about getting using the Eval function and the Query Def Object.

    I'm glad it worked for you. I'm not sure what specific "this" you'd like to read more about, but here's a good site with lots of specific "how-to" articles on specific Access-related topics:

        The Access Web
        http://access.mvps.org/access/

    On that site, for example, this specific page discusses using the Eval function in the way I suggested:

        http://access.mvps.org/access/queries/qry0013.htm

    The Eval function itself is discussed in the online help; for example, in this article:

        https://msdn.microsoft.com/en-us/library/office/ff834705(v=office.15).aspx


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by PuzzledByWord Friday, September 16, 2016 2:37 PM
    Friday, September 16, 2016 2:01 PM

All replies

  • Sorry forgot to add to question just a few seconds ago

    "qry used in dao.recordset does NOT get value from the form "            

    in access 2013 VBA 7.1 64 bit  and win7 64bit

    Mark J                    


    Mark J

    Wednesday, September 14, 2016 10:21 AM
  • If you're using DAO to open a recordset, more or less like this:

        Set rs = CurrentDb.OpenRecordset("qryMyQuery")

    ... then if the query has any reference to a control on a form, then DAO will look at that reference as a parameter, and you need to specify the value for that parameter.  DAO doesn't know anything about Access objects.  One flexible way to do it is to use a QueryDef object and the Eval function, like this:

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim rs As DAO.Recordset
    
    Set db = CurrentD
    Set qdf = db.QueryDefs("qryMyQuery")
    
    For Each prm in qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    
    Set rs = qdf.OpenRecordset()
    
      

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, September 14, 2016 4:49 PM
  • Dirk Goldgar,

    This works great!

    Looks like I need to learn more about this.      Any recommended sites to read about this.

    All the sites I found so far, were not about getting using the Eval function and the Query Def Object.

    Thanks

    Mark J


    Mark J

    Friday, September 16, 2016 11:57 AM
  • Looks like I need to learn more about this.      Any recommended sites to read about this.

    All the sites I found so far, were not about getting using the Eval function and the Query Def Object.

    I'm glad it worked for you. I'm not sure what specific "this" you'd like to read more about, but here's a good site with lots of specific "how-to" articles on specific Access-related topics:

        The Access Web
        http://access.mvps.org/access/

    On that site, for example, this specific page discusses using the Eval function in the way I suggested:

        http://access.mvps.org/access/queries/qry0013.htm

    The Eval function itself is discussed in the online help; for example, in this article:

        https://msdn.microsoft.com/en-us/library/office/ff834705(v=office.15).aspx


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Marked as answer by PuzzledByWord Friday, September 16, 2016 2:37 PM
    Friday, September 16, 2016 2:01 PM
  • Dirk Goldgar,

    Thanks for the links.  Very helpful. Only some of the info I was aware of. These links opened my eyes to new possibilities in my code.

    Mark J


    Mark J

    Friday, September 16, 2016 2:46 PM