I am trying to use VBA behind a command button to send out an email based on a SQL select query.
Here is the code:
I am getting a runtime error 3061 Too few parameters . Expected 1 on the line:
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
Im not a VBA expert and have pieced this code together from what I could find on the web, so I wouldn't doubt it could be done way better than I am doing. Any advise would be much appreciated.
Here is the code:
Code:
Private Sub cmdEmailConfirmed_Click()
Dim Db As Database
Dim rs As Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
Dim lngRScount As Long
Dim strSQL As String
strSQL = "SELECT tblTrainingContacts.TrainingID, Contacts.[E-mail Address], " & _
" tblTrainingContacts.Confirmed FROM Contacts INNER JOIN tblTrainingContacts " & _
" ON Contacts.ID = tblTrainingContacts.ContactID WHERE " & _
" (((tblTrainingContacts.TrainingID)=[Forms]![frmTraining]![txtTrainingID]) " & _
" AND ((tblTrainingContacts.Confirmed)=True));"
Debug.Print strSQL
Set Db = CurrentDb()
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
lngRScount = rs.RecordCount
If lngRScount = 0 Then
MsgBox "No email messages to send.", vbInformation
Else
rs.MoveLast
rs.MoveFirst
Do Until rs.EOF
If IsNull("'rs.Fields(3)'") = False Then
sToName = "'rs.Fields(3)'"
sSubject = "Training Equipment: " & rs.Fields(4)
sMessageBody = "Hello, Thank you for your interest in TECTERRA's GNSS Simulator training course running January 17-19th, 2012. It is my pleasure to inform you that you have been confirmed for attendance at this 3-day course. We had a very large response: well over 30 people interested in only 13 spaces."
DoCmd.SendObject acSendNoObject, , , , , sToName, sSubject, sMessageBody, False, False
End If
rs.MoveNext
Loop
End If
rs.Close
Set Db = Nothing
Set rs = Nothing
End Sub
I am getting a runtime error 3061 Too few parameters . Expected 1 on the line:
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)
Im not a VBA expert and have pieced this code together from what I could find on the web, so I wouldn't doubt it could be done way better than I am doing. Any advise would be much appreciated.