Reputation: 1
I have an Access database I use to email invoices. It has worked for a couple of years. I am getting an invalid procedure call on Set rst = CurrentDb.OpenRecordset(strSQL)
.
It appears that the SQL is correct. I've not made any changes to the database since March of last year.
private Sub Email_AP_Click()
Dim strSQL As String
Dim rst As DAO.Recordset
Dim strSubject As String
Dim strtext As String
strSQL = "SELECT DISTINCT Client_Table_AP.EmailAddress, Client_Table_AP.CCEmailAddress, Client_Table_AP.CombineCode, Client_Table_AP.closed, Qry_Client_Table_AP.Client_ID, Qry_Client_Table_AP.Invoice_Total, Qry_Client_Table_AP.CombineName, Qry_Client_Table_AP.FirstName, Qry_Client_Table_AP.TrueMonthText, Qry_Client_Table_AP.TrueYear " & _
"FROM Client_Table_AP INNER JOIN Qry_Client_Table_AP ON Client_Table_AP.CombineCode = Qry_Client_Table_AP.CombineCode " & _
"WHERE Len(Client_Table_AP.EmailAddress & '') >0 and Qry_Client_Table_AP.Invoice_Total <>0"
Set rst = CurrentDb.OpenRecordset(strSQL)
While rst.EOF = False
strSubject = "SLIM CD invoice for the Advantage Program: " & rst!CombineName & " for " & rst!TrueMonthText & " " & rst!TrueYear
strtext = "Dear " & rst!FirstName & "," & Chr(13) & Chr(13) & "Please find your Slim CD statement for " & rst!CombineName & " attached. The amount due will be debited automatically on the 10th of this month." & Chr(13) & Chr(13) & "Let us know if you have any questions." & Chr(13) & Chr(13) & "Thank you," & Chr(13) & Chr(13) & "Ascent Processing Inc." & Chr(13) & "888 721 9301 office" & Chr(13) & "303 827 2467 fax" & Chr(13) & "http://www.ascentprocessing.com"
strEMail = rst!EmailAddress
strccemail = rst!CCEmailAddress
DoCmd.OpenReport "Rpt_Invoice_AP", acViewPreview, , "[CombineCode]= " & rst!CombineCode, acHidden
DoCmd.SendObject acReport, "Rpt_Invoice_AP", acFormatPDF, strEMail, strccemail, "[email protected]", strSubject, strtext, True
DoCmd.Close acReport, "Rpt_Invoice_AP"
rst.MoveNext
Wend
Set rst = Nothing
End Sub
Upvotes: 0
Views: 2706
Reputation: 56026
Insert this line:
Debug.Print strSQL
before this line:
Set rst = CurrentDb.OpenRecordset(strSQL)
Then run the code,copy the output from the Immediate Window, and paste it into a new query (in SQL view).
Now, run the query and study any error it will show. If no error shows, proceed with the suggestions posted by Erik above.
Upvotes: 1