Microsoft Access VBA Run Time Syntax Error for SQL Query

Question

Here is my updated code per @Parfait suggestion. It still isn't working, getting the following error:

Run-time error '3421' Data type conversion error

On the following line: Set rec = qdef.OpenRecordset(strQry)

Option Compare Database

Private Sub Command0_Click()

    Dim db As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim rec As DAO.Recordset
    Dim olApp As Object
    Dim olItem As Variant
    Dim strQry As String
    Dim aHead(1 To 4) As String
    Dim aRow(1 To 4) As String
    Dim aBody() As String
    Dim lCnt As Long
       
    'Prepared Statement No Data
    
    strQry = "PARAMETERS cboParam TEXT(255);" _
        & " SELECT [Loan ID], [Prior Loan ID], [SRP Rate], [SRP Amount] " _
        & " FROM emailtable " _
        & " WHERE [Seller Name:Refer to As] = [cboParam]"
        
    
    Set db = CurrentDb
    Set qdef = db.CreateQueryDef("", strQry)
    
    ' BIND PARAMETER
    qdef!cboParam = Me.Combo296

    ' OPEN RECORDSET
    Set rec = qdef.OpenRecordset(strQry)
    
    'Create the header row
    aHead(1) = "Loan ID"
    aHead(2) = "Prior Loan ID"
    aHead(3) = "SRP Rate"
    aHead(4) = "SRP Amount"
    
    lCnt = 1
    ReDim aBody(1 To lCnt)
    aBody(lCnt) = "<HTML><body><table border='2'><tr><th>" & Join(aHead, "</th><th>") & "</th></tr>"
    
   
    If Not (rec.BOF And rec.EOF) Then
        Do While Not rec.EOF
            lCnt = lCnt + 1
            ReDim Preserve aBody(1 To lCnt)
            aRow(1) = rec("[Loan ID]")
            aRow(2) = rec("[Prior Loan ID]")
            aRow(3) = rec("[SRP Rate]")
            aRow(4) = rec("[SRP Amount]")
            aBody(lCnt) = "<tr><td>" & Join(aRow, "</td><td>") & "</td></tr>"
            rec.MoveNext
        Loop
    End If
    
    aBody(lCnt) = aBody(lCnt) & "</table></body></html>"
                   
    Set objOutlook = CreateObject("Outlook.Application")
       Set objMail = objOutlook.CreateItem(0)
    With objMail
       .Display   'To display message
       .To = Me.Combo88
       .cc = Me.Combo282
       .Subject = "*SECURE* " & Me.Combo296 & " Refund Request (" & Me.Combo212 & " " & Me.Combo284 & ")"
       .HTMLBody = "<p><font face=""calibri"" style=""font-size:11pt;"">Greetings,</p>" _
       & "<p>We recently acquired loans from " & Me.Combo296 & ", some of which have paid in full and meet the criteria for early prepayment defined in the governing documents. We are requesting a refund of the SRP amount detailed on the attached list.</p>" _
       & "<p>Please wire funds to the following instructions:</p>" _
       & "<ul>Bank Name: My Bank</ul>" _
       & "<ul>ABA: 1111111</ul>" _
       & "<ul>Credit To: ABC Mortgage</ul>" _
       & "<ul>Acct: 11111111111</ul>" _
       & "<ul>Description: " & Combo296 & " EPO SRP Refund</ul>" _
       & "<p>Thank you for the opportunity to service loans from " & Me.Combo296 & "!  We appreciate your partnership.</p>" _
       & "<p>If you have any questions, please contact your Relationship Manager, " & Me.Combo336 & " (Cc'd).</p>" _
       & "<p><br>Sincerely,</br>" _
       & "<br>Acquisitions</br>" _
       & "<br>[email protected]</br></p>"
       
    End With
    
    rec.Close
    Set rec = Nothing: Set qdef = Nothing: Set db = Nothing

End Sub

Any help would be greatly appreciated.

Where do you ever use aBody(lCnt)? It is not placed in HTMLBody. — Parfait
– Parfait, Commented Jan 12, 2021 at 1:09
For second error, do not include brackets in string literal of name: aRow(1) = rec("Loan ID") or aRow(1) = rec![Loan ID]. — Parfait
– Parfait, Commented Jan 12, 2021 at 1:25
This is code I got from a post somewhere and tried to replicate. I very well don't need it but I am learning so am not sure. — skailey
– skailey, Commented Jan 12, 2021 at 14:54
Please see above comment: do not include brackets in string literal of name. — Parfait
– Parfait, Commented Jan 12, 2021 at 22:33

Parfait · Accepted Answer · 2021-01-12 22:32:53Z

1

Avoid concatenating VBA data to SQL even HTML strings. Instead, consider the industry standard of SQL parameterization.

Dim db DAO.Database, qdef As DAO.QueryDef, rec AS DAO.Recordset

' PREPARED STATEMENT (NO DATA)
strQry = "PARAMETERS cboParam TEXT(255);" _
        & " SELECT [Loan ID], [Prior Loan ID], [SRP Rate], [SRP Amount] " _
        & " FROM emailtable " _
        & " WHERE [Seller Name:Refer to As] = [cboParam]" 

Set db = CurrentDb 
Set qdef = db.CreateQueryDef("", strQry)

' BIND PARAMETER
qdef!cboParam = Me.Combo296 

' OPEN RECORDSET
Set rec = qdef.OpenRecordset()

... ' REST OF CODE USING rec

rec.Close
Set rec = Nothing: Set qdef = Nothing: Set db = Nothing

Also, consider saving the email message HTML markup as a text in the table or as text box on form with placeholders to be replaced with combo box values:

.HTMLBody = Replace(Replace(Me.EmailMessage, "placeholder1", Me.Combo296), 
                    "placeholder2", Me.Combo336)

edited Jan 12, 2021 at 22:32

answered Jan 12, 2021 at 1:31

Parfait

108k19 gold badges102 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

skailey Over a year ago

Thank you, I will give this a try. This is my first time taking a deep dive into VBA. I found some code on the internet I am trying to replicate for my needs. I will definitely give the parameterization a try.

skailey Over a year ago

In the .HTMLBody suggestion, if the combo boxes are already listed in the statement, I don't understand the place holder purpose.

skailey Over a year ago

How would I include my original code that will generate the table code for the email based on what I had in my original code? I am assuming the code I need will come after opening the recordset and before rec.close?

skailey Over a year ago

I would like to post my revised code for you to look at. I am now getting a data type conversion error. I feel that my code may be out of place. How can I post my revised code for your review?

skailey Over a year ago

Ok. I am not sure if it is related or not. I tried inserting my table code between the code blocks like you suggested and I am getting the error. I just wanted to make sure I inserted it correctly. If so, then I can troubleshoot the conversion error separately. I will edit my original post with new code.

|

ArcherBird · Accepted Answer · 2021-01-11 15:40:23Z

0

I'm guessing (from your photo) that the data type of your [Seller Name:Refer to as] column is supposed to be string? In which case, your query is missing quotes to denote the string value in your comparison:

'Create each body row
strQry = "Select * from emailtable where [Seller Name:Refer to As] = """ & Me.Combo296 & """"
Set db = CurrentDb
Set rec = CurrentDb.OpenRecordset(strQry)

answered Jan 11, 2021 at 15:40

ArcherBird

2,1441 gold badge14 silver badges35 bronze badges

1 Comment

Parfait Over a year ago

Can potentially break if combo box value contained quotes.

Collectives™ on Stack Overflow

Microsoft Access VBA Run Time Syntax Error for SQL Query

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related