OpenRecordSet Help

[Jackie]

I would like to extract a field (SSN) from a query results using the OpenRecordSet Method.

When I use the following code, the error "RunTime Error 3061 Too Few Parmeters" is displayed. When I run the query from the database window, it executes without error.

Any guidance would be appreciated.
-jackie

Below is the code:

sql = "SELECT qry_max_hrs_multi_letter.SSN FROM qry_max_hrs_multi_letter;"

Set CheckForRecords = CurrentDb.OpenRecordset(sql, DB_OPEN_DYNASET)

 

[rdeleon]

I assume you have CheckForRecords defined as a recordset?

Dim CheckForRecords as Recordset

 

[Jackie]

Yes, CheckForRecords is defined earlier in the code.

 

[cmmrfrds]

like this???
Dim CheckForRecords as DAO.Recordset

 

[GummowN]

Assuming that qry prefix means query then make sure qry_max_hrs_multi_letter does not look up a parameter if it does then use the following:

Dim dbs as database
Dim qdf as querydef
Dim rst as recordset

set dbs = currentdb
set qdf = dbs.querydefs("qry_max_hrs_multi_letter&quot
qdf.parameter(0) = X ' First Parameter in your query
qdf.parameter(1) = X1 ' Second Parameter in your query
etc
qdf.parameter = Xn ' nth Parameter in your query

set rst = qdf.openrecordset

NOTE: X = value you wish to set the parameter to

 

[Jackie]

Thank you for responding.

The query does not have parameters, but it is based on another query that does have parameters.

I took a stab at the code (as follows) and get a compile error "Method or data member not found" on the qdf.Parameter([begintrans]) = [transaction number]line.


Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qry_max_hrs_multi_letter&quot
qdf.Parameter([begintrans]) = [transaction number]
qdf.Parameter([endtrans]) = [transaction number]

Set rst = qdf.OpenRecordset

 

[GummowN]

"Method or data member not found"

Literally means that ([begintrans]) is not in the query.

As far as I can remember you only ever pass parameters as an integers (starting at 0). And Access assigns the first passed parameter to the first parameter in the query, and so on.

Or should I say it works for me

 

[Jackie]

Does this mean I cannot create a recordset based on a query that is based on a query?

 

[GummowN]

No - and I will explain

Say you have a query (with parameters) that is based on a query (also with parameters). In code, each parameter should be passed (as an integer starting at 0) in the order they would be used.

In your case (I am guessing here) you will need to use qdf.parameters(0) and (1), which Access will not need to use in the first query and so will continue to pass to the subquery, at which point it will use them in the order they are required in the base query.

The principle holds for a multi layer query that may include many parameter queries.

Bizarrely once you have got it working the logic that I am trying to explain becomes fairly simple.

;-)

 

[Jackie]

I ended up using the following code that I found in a reference book:

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qry_max_hrs_multi_letter&quot
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm


Set rst = qdf.OpenRecordset(dbOpenDynaset)


Thank you for your help.