Sending email based on the field in a form 1

[platypus71]

Hi there,

Recently, I had some help with generating emails from my database. Now that I have tested and played around with the previous email, I've run into a new hands-tied problem.

I need to run the email code against Status = "Attended" and EventID is equal to the current EventID on the Form entitled "Roster Form" and ONLY that event ID.

Here is what I have so far:

Dim X As Boolean

Dim mydb As Database
Dim rst As Recordset
Dim sqlstr As String
Dim sbj As String
Dim bod As String

sqlstr = "SELECT ReconReport.EventID, Courses.CourseID, Courses.CourseName, ReconReport.StartDate, ReconReport.EndDate, ReconReport.Instructor, Roster.[User ID] AS UserID, Roster.Status FROM (Courses INNER JOIN ReconReport ON Courses.CourseID = ReconReport.CourseID) INNER JOIN Roster ON ReconReport.EventID = Roster.EventID WHERE (((Roster.Status) = 'Attended') AND ((ReconReport.EventID) = Forms![Roster Form]!EventID.Value));"

Set mydb = CurrentDb
Set rst = mydb.OpenRecordset(sqlstr)
Do While Not rst.EOF
sbj = "This is the Subject"
bod = "This is the Body message"
X = SendEmail(rst!UserID, bod, "", sbj, "")
rst.MoveNext
Loop

End Function

What am I missing? The debugger says my error is "Too few parameters" and gives 3081 as the error code.

 

[Remou]

You need to include the value, not the reference:

[tt]sqlstr = "SELECT ReconReport.EventID, Courses.CourseID, Courses.CourseName, ReconReport.StartDate, ReconReport.EndDate, ReconReport.Instructor, Roster.[User ID] AS UserID, Roster.Status FROM (Courses INNER JOIN ReconReport ON Courses.CourseID = ReconReport.CourseID) INNER JOIN Roster ON ReconReport.EventID = Roster.EventID WHERE (((Roster.Status) = 'Attended') AND ((ReconReport.EventID) = " & Forms![Roster Form]!EventID.Value & "));"[/tt]

 

[platypus71]

Thanks.

I knew I was close.