Microsoft Access VBA Run Time Syntax Error for SQL Query

Question

Welcome To Ask or Share your Answers For Others

Microsoft Access VBA Run Time Syntax Error for SQL Query

asked Jan 27, 2021 in Technique[技术] by 深蓝 (71.8m points)

Microsoft Access VBA Run Time Syntax Error for SQL Query

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.

与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…

1 Answer

深蓝 · Answer 1 · 2021-01-27T03:53:03+0000

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)

Categories

Microsoft Access VBA Run Time Syntax Error for SQL Query

Microsoft Access VBA Run Time Syntax Error for SQL Query

Please log in or register to add a comment.

Please log in or register to answer this question.

1 Answer

Please log in or register to add a comment.

Just Browsing Browsing

Most popular tags