Solved: Using Query for recordset in form

[Kicker]

I have a form that works very well with a query as the datasource. However, I need to open (create) another recordset in the form for a couple of special purposes.

When I use the following code:

dim rst as ado.recordset
set rst = currentdb.openRecordset("Names")

When I use an actual Table, I get a recordset with all the records. When I use a query, I only get the first record of the query and recordcount = 1.

What am I doing wrong?

[CreganTur]

When you want to run a query using ADO you need to Execute it as a part of the ADODB Command method. The method below will open a recordset based on a query and uses the .GetString method (which pulls all of the returned records as a string) and outputs them to the Immediate Window.

Sub ExecuteSelectQueryADO()
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim strPath As String
strPath = "C:\Acc07_ByExample\Northwind.mdb" '***Replace with your filepath***
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath
.CommandText = "[Products by Category]" '***Replace with your query name***
.CommandType = adCmdTable
End With
Set rst = New ADODB.Recordset
Set rst = cmd.Execute
Debug.Print rst.GetString
rst.Close
Set rst = Nothing
Set cmd = Nothing
MsgBox "View results in the Immediate Window."
End Sub

HTH:thumb