Recordset error: Too few parameters. Expected 2

[Guest]

I have two queries that I join with a union query. I want to use this union
query to generate a series of emails to co-workers via SendObjects. The
queries run fine as standalone queries but fail with the listed message when
I attempt to create the recordset in my code.

The individual queries each point to separate checkboxes on a form, and I
have added the references to these checkboxes as parameters in the individual
queries and have both references listed as parameters in the union query.
The union query runs fine as a standalone, but generates the error when I
attempt to open it as a recordset. The parameters definition in the union
query looks like:

PARAMETERS [Forms]![frm_Cap_Tasks_Warning]![chk_Cap_Team] Bit,
[Forms]![frm_Cap_Tasks_Warning]![chk_Reviewer] Bit;

Dale

 

[Ken Snell \(MVP\)]

You need to evaluate the parameters before you open the recordset. Here is
some sample code:

Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("NameOfQueryWithParameter(s)")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
' rst object contains the query's records after applying the parameters
' code goes here to use the records' data from rst object
'
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing

 

[Guest]

Ken,

The problem is that I am using this union query in a couple of different
steps as I build this message I am putting out.

strSQL = "SELECT distinct Email FROM qryUnion"
set rs = currentdb.openrecordset(strsql,, dbfailonerror)

so even though the query parameters are set, this recordset statement fails.

Dale
--
Email address is not valid.
Please reply to newsgroup only.

You need to evaluate the parameters before you open the recordset. Here is
some sample code:

Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("NameOfQueryWithParameter(s)")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
' rst object contains the query's records after applying the parameters
' code goes here to use the records' data from rst object
'
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing


--

Ken Snell
<MS ACCESS MVP>

I have two queries that I join with a union query. I want to use this
union
query to generate a series of emails to co-workers via SendObjects. The
queries run fine as standalone queries but fail with the listed message
when
I attempt to create the recordset in my code.

The individual queries each point to separate checkboxes on a form, and I
have added the references to these checkboxes as parameters in the
individual
queries and have both references listed as parameters in the union query.
The union query runs fine as a standalone, but generates the error when I
attempt to open it as a recordset. The parameters definition in the union
query looks like:

PARAMETERS [Forms]![frm_Cap_Tasks_Warning]![chk_Cap_Team] Bit,
[Forms]![frm_Cap_Tasks_Warning]![chk_Reviewer] Bit;

Dale

 

[Guest]

I've created individual queries to address the issue in my previous post, and
it is working properly now.

Thanks.
--
Email address is not valid.
Please reply to newsgroup only.

You need to evaluate the parameters before you open the recordset. Here is
some sample code:

Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("NameOfQueryWithParameter(s)")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
' rst object contains the query's records after applying the parameters
' code goes here to use the records' data from rst object
'
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing


--

Ken Snell
<MS ACCESS MVP>

I have two queries that I join with a union query. I want to use this
union
query to generate a series of emails to co-workers via SendObjects. The
queries run fine as standalone queries but fail with the listed message
when
I attempt to create the recordset in my code.

The individual queries each point to separate checkboxes on a form, and I
have added the references to these checkboxes as parameters in the
individual
queries and have both references listed as parameters in the union query.
The union query runs fine as a standalone, but generates the error when I
attempt to open it as a recordset. The parameters definition in the union
query looks like:

PARAMETERS [Forms]![frm_Cap_Tasks_Warning]![chk_Cap_Team] Bit,
[Forms]![frm_Cap_Tasks_Warning]![chk_Reviewer] Bit;

Dale

 

[Ken Snell \(MVP\)]

You can do what you seek by creating a temporary querydef based on your
strSQL statement, and then using the code I provided.
But your solution also is good.
--

Ken Snell
<MS ACCESS MVP>

I've created individual queries to address the issue in my previous post,
and
it is working properly now.

Thanks.
--
Email address is not valid.
Please reply to newsgroup only.

You need to evaluate the parameters before you open the recordset. Here
is
some sample code:

Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim strSQL As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("NameOfQueryWithParameter(s)")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
' rst object contains the query's records after applying the parameters
' code goes here to use the records' data from rst object
'
rst.Close
Set rst = Nothing
qdf.Close
Set qdf = Nothing
dbs.Close
Set dbs = Nothing


--

Ken Snell
<MS ACCESS MVP>

I have two queries that I join with a union query. I want to use this
union
query to generate a series of emails to co-workers via SendObjects.
The
queries run fine as standalone queries but fail with the listed message
when
I attempt to create the recordset in my code.

The individual queries each point to separate checkboxes on a form, and
I
have added the references to these checkboxes as parameters in the
individual
queries and have both references listed as parameters in the union
query.
The union query runs fine as a standalone, but generates the error when
I
attempt to open it as a recordset. The parameters definition in the
union
query looks like:

PARAMETERS [Forms]![frm_Cap_Tasks_Warning]![chk_Cap_Team] Bit,
[Forms]![frm_Cap_Tasks_Warning]![chk_Reviewer] Bit;

Dale