Results 1 to 4 of 4

Thread: docmd.runsql

  1. #1
    Join Date
    Apr 2004
    Posts
    100

    Unanswered: docmd.runsql

    Hello,

    suppose the following code in Microsoft Access Visual Basic for Applications 2003.

    strsubquery = "SELECT REQUEST.REQ_NO FROM REQUEST WHERE REQUEST.AXA_REQUEST_NO = 'GE55555'"
    strquery = " UPDATE REQUEST SET REQUEST.RELATED_REQ_NO = (' " & strsubquery & " ') WHERE (((REQUEST.AXA_REQUEST_NO)= 'GE55555'));"

    DoCmd.RunSQL strquery

    How can I accomplish to execute a query and store the retrieved records in a variable.
    e.g.
    temp = docmd.runsql strquery
    for some reason this doesn't work. Can someone please explain me how I could accomplish that?
    Thanks a lot.
    Proximus

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    You can not store records in a variable.
    You can


    PHP Code:
    SELECT REQUEST.REQ_NO INTO tempTab
      FROM REQUEST 
        WHERE REQUEST
    .AXA_REQUEST_NO 'GE55555'" 
    You can update the new created table tempTab accordin to your criteria, then make a record set of that table and make what ever you want with that RecordSet

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Also for reference, the DoCmd.RunSQL method will execute ONLY DML calls, that is UPDATE, INSERT or DELETE statements. It may not be used to create and store a recordset.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    "the retrieved records in a variable"

    if the SQL works for you, open a recordset (many DAO or ADO examples on this site) and feed the field values into an array (arrays also frequently discussed here)

    of course, you will now bump into the STUPID new rule on this site that you can only search the site once every 90 seconds!

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •