CODEWITHSUNDEEP
vbams-accessruntime-error
user2785178
user2785178

Reputation: 1

Set rst = CurrentDb.OpenRecordset(strSQL) returning an "invalid procedure call"

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: 2707

Answers (1)

Gustav
Gustav

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

Related Questions