Recordsets and Queries

[Novice1]

I want to send an e-mail to a select group of people. I narrow the group based on a query. When the query is run, the user is asked to provide a counselor’s name (criteria in the query [Provide Counselor’s Name]). That works fine.

However, when I add the query to a recordset, the criteria question isn’t run. How do I capture that filter data for the recordset? Not sure where to begin.


On Error Resume Next

Dim db As DAO.Database
Dim rs As Object
Dim iCount As Integer
Dim rsEmail As DAO.Recordset

Dim vRecipientList As String


Set db = CurrentDb()
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryMissingItems")

 

[RuralGuy]

I see several issues in the code but have you tried:
Set rs = CurrentDb.OpenRecordset("qryMissingItems",dbOpenDynaset)

 

[Novice1]

Thanks but the criteria is still not being solicited from the user. Any other ideas?

 

[arnelgp]

it will not fire when opening through recordset.
the best you can do is create an alternative solution, an inputbox:

Private Sub x()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef


Set db = CurrentDb

Set qd = db.QueryDefs("qryMissingItems")
qd.Parameters(0) = InputBox("[Provide Counselor's Name]") & ""
Set rs = qd.OpenRecordset(dbopendynaset)