How to call a query and receive the records in VBA??? 1

[RickBerem]

Hi,
I'd like to call a query I've build with the query builder from VBA and then use the data the query returns in the code.

is there anyway?

thanks

RickBerem

 

[DougP]

Sure:
Just copy your SQL query code into the line below
SQL = "your stuff in here"
here is a sample of how to open a recordset with SQL code and return values from it.

Dim db as database, rst as recordset, SQL as string
Set db = CurrentDb
' SQL string.
SQL = "SELECT * FROM Orders WHERE OrderDate >= #1-1-95#; Order by Number DESC"
Set rst = db.OpenRecordset(SQL)
'Assign value from recordset to a variable
somevalue = rst!OrderDate
' or set the value to a text box on a form
Me!MyTextbox = rst!OrderDate

' close the recordset and database
rst.close
db.close

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[RickSpr]

If you want to support queries that haven't been built yet (such as when you're letting the users build them), you can get the SQL out of an existing query with the following code:

Sub ProcessQuery(QueryName As String)
    Dim db As Database, qdf As QueryDef
    Dim rst As Recordset
    Set db = CurrentDb
    Set qdf = db.QueryDefs(QueryName)
    Set rst = db.OpenRecordset(qdf.SQL)
    ' Loop through the recordset
    Do While Not rst.EOF
        ' Use the data in the record here
        rst.MoveNext
    Loop
    rst.Close
    db.Close
    ' Set DAO vars to Nothing to avoid memory leak
    Set rst = Nothing
    Set db = Nothing
End Sub

Rick Sprague