execute a query with parameter in a vba code

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • D-Zyl
    #1

    execute a query with parameter in a vba code

    I wanna execute a query with one parameter from MS VBA.
    the trouble is that the parameter is a control in a form.
    and I still don't know what is the code I need...
    Tags: None
    • Allen Browne
      #2
      Re: execute a query with parameter in a vba code

      Simplest way it to just create the query string on the fly and execute it:

      strSQL = "UPDATE ... WHERE SomeField = " & Forms!MyForm!My TextBox & ";"
      dbEngine(0)(0). Execute strSQL, dbFailOnError

      Alternatively, you can supply the Parameter for the QueryDef, and execute
      that:

      Dim qdf As DAO.QueryDef
      Set qdf = db.QueryDefs("M yQuery")
      qdf.Parameters( "Forms!MyForm!M yTextBox") = Forms!MyForm!My TextBox
      ....

      --
      Allen Browne - Microsoft MVP. Perth, Western Australia.
      Tips for Access users - http://allenbrowne.com/tips.html
      Reply to group, rather than allenbrowne at mvps dot org.

      "D-Zyl" <d_zyl38@hotmai l.com> wrote in message
      news:ef7e39ee.0 404262158.127b9 44d@posting.goo gle.com...[color=blue]
      > I wanna execute a query with one parameter from MS VBA.
      > the trouble is that the parameter is a control in a form.
      > and I still don't know what is the code I need...[/color]


        Comment

        • Trevor Best
          #3
          Re: execute a query with parameter in a vba code

          Allen Browne wrote:
          [color=blue]
          > Simplest way it to just create the query string on the fly and execute it:
          >
          > strSQL = "UPDATE ... WHERE SomeField = " & Forms!MyForm!My TextBox & ";"
          > dbEngine(0)(0). Execute strSQL, dbFailOnError
          >
          > Alternatively, you can supply the Parameter for the QueryDef, and execute
          > that:
          >
          > Dim qdf As DAO.QueryDef
          > Set qdf = db.QueryDefs("M yQuery")
          > qdf.Parameters( "Forms!MyForm!M yTextBox") = Forms!MyForm!My TextBox
          > ...
          >[/color]
          If all your parameters are form controls, then you can:

          For Each prm In qdf.Paramters
          prm.value = eval(prm.name)
          Next

          I use that in a generic procedure, my latest domain function
          replacements
          (http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) use this
          technique.

          --
          Error reading sig - A)bort R)etry I)nfluence with large hammer

            Comment

            • Allen Browne
              #4
              Re: execute a query with parameter in a vba code

              Good trick, Trevor.

              You would need to check that the control was not Null first, as Eval() does
              not handle nulls.

              As Trevor would know, it is also a *very* good idea to declare these
              parameters. In query design, choose Parameters on the Query menu, and enter
              the name and data type. Something like:
              Forms!MyForm!My TextBox Long

              --
              Allen Browne - Microsoft MVP. Perth, Western Australia.
              Tips for Access users - http://allenbrowne.com/tips.html
              Reply to group, rather than allenbrowne at mvps dot org.

              "Trevor Best" <nospam@localho st> wrote in message
              news:408e0ead$0 $6746$afc38c87@ auth.uk.news.ea synet.net...[color=blue]
              > Allen Browne wrote:
              >[color=green]
              > > Simplest way it to just create the query string on the fly and execute[/color][/color]
              it:[color=blue][color=green]
              > >
              > > strSQL = "UPDATE ... WHERE SomeField = " & Forms!MyForm!My TextBox & ";"
              > > dbEngine(0)(0). Execute strSQL, dbFailOnError
              > >
              > > Alternatively, you can supply the Parameter for the QueryDef, and[/color][/color]
              execute[color=blue][color=green]
              > > that:
              > >
              > > Dim qdf As DAO.QueryDef
              > > Set qdf = db.QueryDefs("M yQuery")
              > > qdf.Parameters( "Forms!MyForm!M yTextBox") = Forms!MyForm!My TextBox
              > > ...
              > >[/color]
              > If all your parameters are form controls, then you can:
              >
              > For Each prm In qdf.Paramters
              > prm.value = eval(prm.name)
              > Next
              >
              > I use that in a generic procedure, my latest domain function
              > replacements
              > (http://easyweb.easynet.co.uk/~trevor.../baslookup.zip) use this
              > technique.
              >
              > --
              > Error reading sig - A)bort R)etry I)nfluence with large hammer[/color]


                Comment

                • MaxZeta
                  #5
                  Re: execute a query with parameter in a vba code

                  "Allen Browne" <AllenBrowne@Se eSig.Invalid> wrote in message news:<408e2902$ 0$16606$5a62ac2 2@freenews.iine t.net.au>...[color=blue]
                  > Good trick, Trevor.
                  >
                  > You would need to check that the control was not Null first, as Eval() does
                  > not handle nulls.
                  >
                  > As Trevor would know, it is also a *very* good idea to declare these
                  > parameters. In query design, choose Parameters on the Query menu, and enter
                  >.......[/color]



                  Check http://www.RealTimeInformatica.it/Stany to try the free
                  component to manage SQL and parameters..

                    Comment

                    Previous template Next
                    Working...