MS Access Convert Recordsource to SQL

I have a function that generates an HTML table from an SQL query string.

I want to get the current active report's query string with all my filters and generate an HTML table from that. Then, I can include it in my Outlook email.

I am trying to create a function that does the following:

1. Opens up MS Outlook.
2. Opens an already made template.
3. Replaces a string in the template with the table generated from the currently active report.
4. Add the currently active report as a PDF attachment.

Here is my code:

Option Compare Database
Option Explicit

Private Sub emailSupplier_Click()
    ' Define the parameters
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim objOutlookAttach As Outlook.Attachment
    Dim templateExpediter As String
    Dim msgBody As String
    Dim strFind As String
    Dim strNew As String
    Dim currentReport As Report
    Dim query As String

    ' Set the params
    Set currentReport = Screen.ActiveReport
    Set query = currentReport.RecordSource
    Set templateExpediter = "D:\Templates\expediter.oft"
    ' Create the Outlook session.
    Set objOutlook = CreateObject("Outlook.Application")
    ' Create the message.
    Set objOutlookMsg = objOutlook.CreateItemFromTemplate(templateExpediter)

    With objOutlookMsg
       ' Add the To recipient(s) to the message.
       Set objOutlookRecip = .Recipients.Add("firstmail")
       objOutlookRecip.Type = olTo

       ' Add the CC recipient(s) to the message.
       Set objOutlookRecip = .Recipients.Add("secondamail")
       objOutlookRecip.Type = olCC

       ' Set the Subject, Body, and Importance of the message.
       .BodyFormat = olFormatHTML
       .Subject = "Urgent Delivery Request - " & Date
       .Importance = olImportanceHigh 'High importance

       strFind = "{X}"
       ' Get HTML from the query for the record set
       strNew = GenHTMLTable(query)
       .HTMLBody = Replace(.HTMLBody, strFind, strNew)

       ' Resolve each Recipient's name.
       For Each objOutlookRecip In .Recipients
          objOutlookRecip.Resolve
       Next

       ' Should we display the message before sending?
       'If DisplayMsg Then
          '.Display
       'Else
          .Save
          .Display
       'End If

    End With
    Set objOutlook = Nothing

End Sub

My question is how can I convert my current active report recordsource or set, into an active HTML table?

Or at least get the SQL Query with filters so I can generate using the function QueryToHtmlTable(Query).

• EDIT 2 - Ok, so i got the correct SQL with filters. Now it seems this function to generate HTML from the sql is giving me an error 'item is not found in collection'

  Function GenHTMLTable(sQuery As String, Optional bInclHeader As Boolean = True) As String
      On Error GoTo Error_Handler
      Dim db As DAO.Database
      Dim qdf As DAO.QueryDef
      Dim prm As DAO.Parameter
      Dim rs As DAO.Recordset
      Dim fld As DAO.Field
      Dim sHTML As String
  
      Set db = CurrentDb
      Set qdf = db.QueryDefs(sQuery)
  
      For Each prm In qdf.Parameters
          prm = Eval(prm.Name)
      Next prm
  
      Set rs = qdf.OpenRecordset
      With rs
          sHTML = "<table>" & vbCrLf
          If bInclHeader = True Then
              'Build the header row if requested
              sHTML = sHTML & vbTab & "<tr>" & vbCrLf
              For Each fld In rs.Fields
                  sHTML = sHTML & vbTab & vbTab & "<th>" & fld.Name & "</th>" & vbCrLf
              Next
              sHTML = sHTML & vbTab & "</tr>" & vbCrLf
          End If
          If .RecordCount <> 0 Then
              Do While Not .EOF
                  'Build a row for each record in the recordset
                  sHTML = sHTML & vbTab & "<tr>" & vbCrLf
                  For Each fld In rs.Fields
                      sHTML = sHTML & vbTab & vbTab & "<td>" & fld.Value & "</td>" & vbCrLf
                  Next
                  sHTML = sHTML & vbTab & "</tr>" & vbCrLf
                  .MoveNext
              Loop
          End If
          sHTML = sHTML & "</table>"
      End With
  
      GenHTMLTable = sHTML
  
  Error_Handler_Exit:
      On Error Resume Next
      If Not fld Is Nothing Then Set fld = Nothing
      If Not rs Is Nothing Then
          rs.Close
          Set rs = Nothing
      End If
      If Not db Is Nothing Then Set db = Nothing
      Exit Function
  
  Error_Handler:
      MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
             "Error Number: " & Err.Number & vbCrLf & _
             "Error Source: GenHTMLTable" & vbCrLf & _
             "Error Description: " & Err.Description & _
             Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
             , vbOKOnly + vbCritical, "An Error has Occured!"
      Resume Error_Handler_Exit
  End Function
  

ANSWER

    Dim currentReport As Report
    Dim strSQL As String

    ' Set the params
    Set currentReport = Screen.ActiveReport
    ' Replace double qoutes with single qoutes
    strSQL = Replace(currentReport.RecordSource, ";", "") & " AND " & currentReport.filter
    strSQL = Replace(strSQL, Chr(34), "'")
    strSQL = Replace(strSQL, ")", "")
    strSQL = Replace(strSQL, "(", "")