OpenRecordset error ... what am I doing wrong?

[DianeM]

I'm trying to create some code that will open a query and send an e-
mail reminder to every record in that query. I've found lots of
examples and have followed them as closely as possible, but I keep
getting errors. Does anyone have any pointers?

This is the code I currently have (I'm using Access 2003):

Private Sub CreateEmailsButton_Click()

Dim db As DAO.Database
Dim rstReminders As DAO.Recordset

Set db = CurrentDb()
Set rstReminders = db.OpenRecordset("qryReminders")
'ERROR: when I get to the line above, I get the
following error:
'Run Time Error '3061': Too few parameters. Expected 1.

For Each record In rstReminders
DoCmd.SendObject acSendNoObject, , ,
[EmailAddressFieldFromQuery], _
"cc Line address", , "Subject line text", "Message body
text"
Next

End Sub


Thanks in advance for your thoughts!

 

[Stefan Hoffmann]

hi Diane,

Set rstReminders = db.OpenRecordset("qryReminders")

For Each record In rstReminders
DoCmd.SendObject acSendNoObject, , ,
[EmailAddressFieldFromQuery], _
"cc Line address", , "Subject line text", "Message body
text"
Next

Instead of the For-Next loop:

Do While Not rs.Eof
DoCmd.SendObject acSendNoObject,,, rs!,...
rs.MoveNext
Loop


mfG
--> stefan <--

 

[DianeM]

hi Diane,

Set rstReminders = db.OpenRecordset("qryReminders")

For Each record In rstReminders
DoCmd.SendObject acSendNoObject, , ,
[EmailAddressFieldFromQuery], _
"cc Line address", , "Subject line text", "Message body
text"
Next

Instead of the For-Next loop:

Do While Not rs.Eof
DoCmd.SendObject acSendNoObject,,, rs!,...
rs.MoveNext
Loop

mfG
--> stefan <--[/QUOTE]

Thanks, I'll try that ... once I can get my code to move beyond the
set recordset line. Any thoughts on that?

 

[Douglas J. Steele]

Does your query expect any parameters?

 

[DianeM]

Does your query expect any parameters?

My query pulls a date from an open form. The SQL reads:

SELECT Format([Visits]![VisitDate],"mm/dd/yyyy") AS VisitDateOnly,
VisitDate, Name, EmailAddress
FROM (Visits
WHERE (VisitDateOnly=Format([Forms]![frmSingleDateSelector]!
[ReminderDate],"mm/dd/yyyy")));

NOTES
I had to add the format function so that it would find all the visits
for that day, regardless of time ... there may be a more program-
friendly way of doing that piece.

The actual query has joins (the name and e-mail fields are pulled in
from other tables), but I simplified the above so the parameter info
was easier to deciper.

Thanks!

 

[Douglas J. Steele]

So yes, your query expects a parameter:
[Forms]![frmSingleDateSelector]![ReminderDate]. That means you can't simply
open it: you have to explicitly set the parameter. Off the top of my head, I
believe it's

Private Sub CreateEmailsButton_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstReminders As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReminders")
qdf.Parmeters(1) = [Forms]![frmSingleDateSelector]![ReminderDate]
Set rstReminders = qdf.OpenRecordset

For Each record In rstReminders
DoCmd.SendObject acSendNoObject, , , _
[EmailAddressFieldFromQuery], _
"cc Line address", , "Subject line text", "Message body text"
Next

End Sub


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Does your query expect any parameters?

My query pulls a date from an open form. The SQL reads:

SELECT Format([Visits]![VisitDate],"mm/dd/yyyy") AS VisitDateOnly,
VisitDate, Name, EmailAddress
FROM (Visits
WHERE (VisitDateOnly=Format([Forms]![frmSingleDateSelector]!
[ReminderDate],"mm/dd/yyyy")));

NOTES
I had to add the format function so that it would find all the visits
for that day, regardless of time ... there may be a more program-
friendly way of doing that piece.

The actual query has joins (the name and e-mail fields are pulled in
from other tables), but I simplified the above so the parameter info
was easier to deciper.

Thanks!

 

[DianeM]

So yes, your query expects a parameter:
[Forms]![frmSingleDateSelector]![ReminderDate]. That means you can't simply
open it: you have to explicitly set the parameter. Off the top of my head, I
believe it's

Private Sub CreateEmailsButton_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstReminders As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReminders")
qdf.Parmeters(1) = [Forms]![frmSingleDateSelector]![ReminderDate]
Set rstReminders = qdf.OpenRecordset

For Each record In rstReminders
DoCmd.SendObject acSendNoObject, , , _
[EmailAddressFieldFromQuery], _
"cc Line address", , "Subject line text", "Message body text"
Next

End Sub

Well, I feel like I'm getting closer, but I still can't make it work.
I'm getting an error that says "Item not found in this collection" on
the Parameters(1) line. I've also tried Parameters
("VisitDateNoTime"), which is the name I've assigned to the parameter
in my query.

In searching Google groups for more information about parameters, I've
noted that many times there seems to be a reference to the term
"parameter" appearing in the SQL, which it does not in this case. The
SQL for my query is as follows:

SELECT Format([Visits]![VisitDate],"mm/dd/yyyy") AS VisitDateNoTime,
Visits.VisitID, Visits.VisitDate, Agents.AgentName,
LeadersInspectors.LILNName
FROM (Visits LEFT JOIN Agents ON Visits.AgentID = Agents.[Employee
ID]) LEFT JOIN LeadersInspectors ON Agents.LeaderID =
LeadersInspectors.LeaderInspectorID
WHERE (((Format([Visits]![VisitDate],"mm/dd/yyyy"))=Format([Forms]!
[frmSingleDateSelector]![ReminderDate],"mm/dd/yyyy")));

Is there a way I can just define this query in VBA? I really don't
need it except to call it for this sub.

Cheers,
Diane

 

[DianeM]

I got this to work by changing my query to a parameter query.

Thanks to you all for your help ... it's really slick now!

Cheers,
Diane

 

[Douglas J. Steele]

So yes, your query expects a parameter:
[Forms]![frmSingleDateSelector]![ReminderDate]. That means you can't
simply
open it: you have to explicitly set the parameter. Off the top of my
head, I
believe it's

Private Sub CreateEmailsButton_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rstReminders As DAO.Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryReminders")
qdf.Parmeters(1) = [Forms]![frmSingleDateSelector]![ReminderDate]
Set rstReminders = qdf.OpenRecordset

For Each record In rstReminders
DoCmd.SendObject acSendNoObject, , , _
[EmailAddressFieldFromQuery], _
"cc Line address", , "Subject line text", "Message body text"
Next

End Sub

Well, I feel like I'm getting closer, but I still can't make it work.
I'm getting an error that says "Item not found in this collection" on
the Parameters(1) line. I've also tried Parameters
("VisitDateNoTime"), which is the name I've assigned to the parameter
in my query.

Sorry, my fault. The Parameters collection (like virtually all collections
in Access) starts at 0, so you'd need

qdf.Parmeters(0) = [Forms]![frmSingleDateSelector]![ReminderDate]

And the name of the parameter would have been
[Forms]![frmSingleDateSelector]![ReminderDate], so

qdf.Parameters("[Forms]![frmSingleDateSelector]![ReminderDate]") =
[Forms]![frmSingleDateSelector]![ReminderDate]

should have worked.

Is there a way I can just define this query in VBA? I really don't
need it except to call it for this sub.

I realize you've already got this working, but for the future, yes, you
didn't really need to use a predefined query:

Private Sub CreateEmailsButton_Click()
Dim db As DAO.Database
Dim rstReminders As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Format([Visits]![VisitDate],"mm/dd/yyyy") " & _
"AS VisitDateNoTime, Visits.VisitID, Visits.VisitDate, " & _
"Agents.AgentName, LeadersInspectors.LILNName " & _
"FROM (Visits LEFT JOIN Agents " & _
"ON Visits.AgentID = Agents.[Employee ID]) " & _
"LEFT JOIN LeadersInspectors ON Agents.LeaderID = " & _
"LeadersInspectors.LeaderInspectorID " & _
"WHERE Format([Visits]![VisitDate],"mm/dd/yyyy")=" & _
"'" &
Format([Forms]![frmSingleDateSelector]![ReminderDate],"mm/dd/yyyy") & "'"

Set db = CurrentDb()
Set rstReminders = db.OpenRecordset(strSQL)

For Each record In rstReminders
DoCmd.SendObject acSendNoObject, , , _
[EmailAddressFieldFromQuery], _
"cc Line address", , "Subject line text", "Message body text"
Next

End Sub