Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Extracting a result of a Query using VBA in Access

Options
  • 07-07-2005 12:36am
    #1
    Peter B
    Registered Users Posts: 1,086 ✭✭✭


    Very new to VBA.

    Want to know how to return a resultSet in VBA from just performing a query in Access.

    I am using Docmd.OpenQuery("Queryname")

    I think the command I am using executes the query (by displaying its results) but I do not know how to utilise the result in VBA i.e save them as variables, or iterate through the results.

    I am probably using the wrong command....

    Need some command so I can perform Query allowing me to run a loop through Query results.

    Cheers


Comments

  • Options
    #2
    bonkey
    Registered Users Posts: 15,443 ✭✭✭✭bonkey


    DoCmd is indeed not the right choice.

    What you want is to open a recordset from the query. You then manipulate the recordset (reading field values, looping through the records, and so on).

    A quick check through help on how to use recordsets should give you exactly the code you want. If not...check it out then ask further :)

    jc
    jc


  • Options
    #3
    McGintyMcGoo
    Closed Accounts Posts: 333 ✭✭McGintyMcGoo


    Just to add that I think every Access developer comes across this issue at some stage (myself included) and as Bonkey said, recordsets are the answer.


  • Options
    #4
    Peter B
    Registered Users Posts: 1,086 ✭✭✭Peter B


    Thanks very much for the help.

    Like most things, I solve one problem and reach another.

    I tried designing VBA code in access to open a Word document, perfrom a Query and display the results in that (MS) Word document through iteration through the ResultSet. (Mail Merge is not suitable for my task as far as I m aware). I was bogged down for a while until I realised I used ADOBD instead of DAO (don't know what this means). Now this is working fine.

    However when trying to display text I am finding a lot of trouble with displaying exact font type (especially underline), pattern shading etc. I usually record a macro in word, have a look at the Word VBA code and then paste it into my Access VBA code putting the Word.Application object in front of it certain statements such as -> oApps.Selection.....

    Then I realised instead of modifying the VBA code when pasting it from Word to Access I could just run an Query straight from the VBA in Word on an Access database and return the ResultSet.

    Only problem is I do not know how to, through VBA in Word, connect to a database (on same computer), execute a Query in that database and return the results as a ResultSet so I can iterate through each row.

    I have tried googling many times but have not arrived at an answer making any sense to me.

    Any thoughts on what I am doing / doing wrong?

    If anyone has any relevant code / sites it would be much appreciated! :)


Advertisement