Results 1 to 14 of 14
  1. #1
    Join Date
    Jun 2012
    Posts
    38

    Unanswered: Dynamically Fill in Data to an HTML Generated Table

    I'm trying to generate an HTML table to be put into a vba generated email. The table will need to consist of dynamic data. The headers can be static.

    This is my code so far, but I'm getting an error that says "Item cannot be found in the collection corresponding to the requested name or ordinal."

    Code:
    Private Function HTML_Table(rs As ADODB.Recordset) As String
        
        Dim str_Html As String
        str_Html = "<table><tr><th>q_AuthToRoute</th></tr><tr><th>Transaction_Type</th><th>PBG_Cd</th><th>Mfg_Cd</th><th>SCN_QCN</th><th>Line_Create_Dt</th><th>Company</th><th>Number</th><th>Line_Item</th><th>Auth_Status</th><th>Auth_Send_Dt</th><th>Auth_Approved</th></tr>"
        rs.MoveFirst
        Do
            Dim x As Integer
            For x = 1 To rs.Fields.Count
            str_Html = str_Html & _
            "<tr><td> " & rs.Fields(x).Value & "</td></tr></table>"
            Next
    Loop Until rs.EOF
    HTML_Table = str_Html
        
    End Function
    The error highlights these 2 lines, but points to the 2nd line:
    Code:
     str_Html = str_Html & _
            "<tr><td> " & rs.Fields(x).Value & "</td></tr></table>"
    After this script, I call it through my email sending script by putting this into the email body:
    Code:
    HTML_Table(rs_Data)
    Any advice?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    For x = 0 To rs.Fields.Count - 1
    Paul

  3. #3
    Join Date
    Jun 2012
    Posts
    38
    It's weird, but when I compile with your suggestion, Access will stop responding and will not recover.

    When I commented out the loop, just to see if it worked, Access was not responding then too.

    So, I think your suggestion worked, but it brings me to another issue. Is there a reason compiling code might be causing Access to crash?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The Fields collection is zero based, so you have to loop it from 0 to the count minus 1. One alternative would be to leave it the way you had it and do this:

    "<tr><td> " & rs.Fields(x - 1).Value & "</td></tr></table>"

    I can't imagine why that code would cause the behavior you describe. If you comment out that whole block does it compile normally?
    Paul

  5. #5
    Join Date
    Jun 2012
    Posts
    38
    It does compile normally if I comment out the entire function.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And it compiled before? How about with your original loop and the "x-1" bit?
    Paul

  7. #7
    Join Date
    Jun 2012
    Posts
    38
    Access doesn't respond with my original loop and the x-1 bit or your suggestions to the loop. I think the error occurs in my email script where this HTML_Tables is being called on.

    If instead of trying to call HTML_Tables, I make the email body fully statics, it will compile.

  8. #8
    Join Date
    Jun 2012
    Posts
    38
    This is the function that generates the email and uses the HTML_Tables script in the body:
    Code:
    Function exporthtml(str_Sender As String, rs_Data As ADODB.Recordset)
    
    Dim strlin As String, strHTML As String
    Dim objOutlook As Outlook.Application
    Dim objOutlookMsg As Outlook.MailItem
    Dim objOutlookRecip As Outlook.Recipient
    Dim rs As Recordset
      Set rs = Me.Recordset
      Set objOutlook = Outlook.Application
      Set objOutlookMsg = Outlook.Application.CreateItem(olMailItem)
      
      If Left(objOutlook.Version, 2) = "10" Then
        objOutlookMsg.BodyFormat = olFormatHTML
      End If
      objOutlookMsg.HTMLBody = "Hi Team,<br>Please let me know if the following orders are okay to approve." & vbCrLf & vbCrLf & HTML_Table(rs_Data) & "<br>Thank You" & vbCrLf & vbCrLf
    
          With objOutlookMsg
          Do
          
          Set objOutlookRecip = .Recipients.Add(str_Sender)
          objOutlookRecip.Type = olTo
          Loop Until rs.EOF
          Close
          Set objOutlookRecip = .Recipients.Add("*removed*")
          objOutlookRecip.Type = olCC
          objExport = exporthtml
          .Subject = "International Authorization"
          .Importance = olImportanceHigh
          For Each objOutlookRecip In .Recipients
             objOutlookRecip.Resolve
          If Not objOutlookRecip.Resolve Then
             objOutlookMsg.Display
          End If
          Next
          .Send
          End With
    
    End Function

  9. #9
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    U need to take out the </table> inside the loop

    Add it after the next as the /is a close tag
    Outlook will see the error and will just put up with it
    It could bit in the ... When you try to add more thing to the HTML code later
    Also think u need to do some more reading on HTML code in outlook messages

    If I was writeing it I would do some like this

    str_Html= "<table>"
    str_Html = "<table><tr><th>q_AuthToRoute</th></tr><tr><th>Transaction_Type</th><th>PBG_Cd</th><th>Mfg_Cd</th><th>SCN_QCN</th><th>Line_Create_Dt</th><th>Company</th><th>Number</th><th>Line_Item</th><th>Auth_Status</th><th>Auth_Send_Dt</th><th>Auth_Approved</th></tr>"
    Do until rs.eof
    str_Html = str_Html & "<tr>" ' new row
    str_Html = str_Html & "<td>" & rs("feildname") & "</td>"
    str_Html = str_Html & "<td>" & rs("nextfeildname") & "</td>"
    str_Html = str_Html & "<td>" & rs("nextfeildname") & "</td>"
    ......
    str_Html = str_Html & "</tr>" ' end row
    loop
    HTML_Table = str_Html & "</table>"

    easyer to read and have the / switch in the right place
    Last edited by myle; 06-29-12 at 21:57.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  10. #10
    Join Date
    Jun 2012
    Posts
    38
    I tried your suggestion myle. Maybe it's unrelated to this script because I have a couple routines being called but Access will still stop responding when I compile.

    Why would some code cause this, rather than just an error?

  11. #11
    Join Date
    Jun 2012
    Posts
    38
    The reason Access would stop responding is because I was creating an infinite loop. I fixed it with "rs.MoveNext"

    I also modified my code to look like this, and now it works:

    Code:
    Private Function HTML_Table(rs As ADODB.Recordset) As String
        
        Dim str_Html As String
        
        rs.MoveFirst
        str_Html = "<Table><tr>"
        Dim x As Integer
        For x = 1 To rs.Fields.Count - 1
            str_Html = str_Html & "<td>" & rs.Fields(x).Name & "</td>"
            Debug.Print str_Html
        Next
        str_Html = str_Html & "</Tr>"
        Do
            str_Html = str_Html & "<TR>"
            For x = 1 To rs.Fields.Count - 1
            str_Html = str_Html & _
            "<td> " & rs.Fields(x).Value & "</td>"
            Next
            str_Html = str_Html & "</tr>"
            rs.MoveNext
    Loop Until rs.EOF
    str_Html = str_Html & "</Table>"
    Debug.Print str_Html
    HTML_Table = str_Html
        
    End Function
    Thanks for the help guys.

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I missed that.

    That shouldn't have affected it compiling though.
    Paul

  13. #13
    Join Date
    Jun 2012
    Posts
    38
    I was testing the script by clicking on the command button that generates the email instead of using the Debug menu option to compile, I don't know if that makes a difference.

    But at any rate problem solved! Thanks!

  14. #14
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Bugger sorry about I miss that rs.movenext in my code example
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •