CODEWITHSUNDEEP
vbams-access
Carl
Carl

Reputation: 5779

Access: Running query with DAO.recordset that selects from another query

I have an access form with a text field, and a subform. The text field is a parameter to a query named myQuery, and the subform shows the results of myQuery.

In VBA I want to access the first row of myQuery so I tried using DAO.recordsets like so:

dim query as string
dim rs as DAO.recordset
query = "select top 1 * from myQuery"
set rs=CurrentDb.OpenRecordset(query)

I get an error saying "Too few parameters".

If I go to the create query tab, and run the SQL code "select top 1 * from myQuery" it works. I am guessing that when running queries via CurrentDb.OpenRecordset that you can't treat another query as a table like you can with regular SQL.

Does anyone know how to select from another query using OpenRecordset?

Regards

Upvotes: 0

Views: 4303

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider using a querydef and evaluate parameters before opening to a recordset. However, no specific SELECT expression can be used in this instance but whole query object. The TOP 1 can be mirrored with selecting recordset values without looping (as it picks first).

Dim db As DAO.Database
Dim qdf As DAO.querydef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("myQuery")
For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset)
rst.MoveFirst

Debug.Print rst.Fields(0), rst.Fields(1), rst.Fields(2), rst.Fields(3), rst.Fields(4)

Upvotes: 1

Related Questions