Recordset from Parameter Query

[mickarlsmith]

I have this code and a query called Q_ExportToOutlook, but when i try
and run it i get the message 'Item not found in Collection'. I have
copied the Parameter straight from the Query SQL and then deleted it
from the SQL for the query.

This is the code i have running off the button-

Dim dbs As Database
Dim rst As Recordset
Dim qd As QueryDef
Set dbs = CurrentDb()
Set qd = dbs.QueryDefs("Q_ExportToOutlook")
qd.Parameters![tblStaffSelect.StaffID] = [Forms]!
[frmExportToOutlook].[StaffSelect]

Can anyone tell me what i'm missing?

 

[Ken Sheridan]

It’s the reference to the control on the form which is the parameter, not the
reference to the column. In fact you don't need to specify the parameter by
name, or even know how many parameters there are; you can evaluate each
parameter in the query's Parameters Collection:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Dim prm as DAO.Parameter

Set dbs = CurrentDb()
Set qd = dbs.QueryDefs("Q_ExportToOutlook")

For Each prm In qd.Parameters
prm = Eval(prm.Name)
Next prm

Set rst = qd.OpenRecordset

Ken Sheridan
Stafford, England

 

[mickarlsmith]

thanks!