none
xml from access RRS feed

  • Question

  • Hi!

    I would like to compile xml from access using VBA. the following works very well. but I would like the rowsofreport be a result of a do---loop in a recordset. how do I break the statement and insert the do---loop ?

        docXMLDOM.loadXML "<?xml version=""1.0"" encoding=""utf-8"" ?>" & _

                        "<v1:report_ready xmlns:v1=""http://www......"">" & _

                        " <companycode>987321</companycode>" & _

                        " <periodyear>" & Forms!mydata!year & "</periodyear>" & _

                        " <periodmonth>" & Forms!mydata!month & "</periodmonth>" & _                   

                          " <rowsofreport>" & _

                          "  <reportrow>" & _

                          "    <passcode>123456789</passcode>" & _

                          "    <prodtype>100</prodtype>" & _

                          "  </reportrow>" & _

                          "  <reportrow>" & _

                          "    <passcode>987654321</passcode>" & _

                          "    <prodtype>100</prodtype>" & _

                          "  </reportrow>" & _

                          "  <reportrow>" & _

                          "    <passcode>456123</passcode>" & _

                          "    <prodtype>100</prodtype>" & _

                          "  </reportrow>" & _

                          " </rowsofreport> " & _

                          "</v1:report_ready >"

    Thank you!

    Wednesday, January 18, 2017 3:55 PM

All replies

  • Hi,

    One approach is to use a separate variable for the report rows and then use the loop to populate it and then insert it between the beginning and ending XML text. For example (in pseudocode):

    Dim strReportRows As String

    Loop through all records

    strReportRows = strReportRows & "XML elements..."

    Next record

    docXMLDOM.LoadXML = "<?xml version... <rowsofreport>" & strReportRows & "</rowsofreport>..."

    Hope it helps...

    Wednesday, January 18, 2017 4:36 PM
  • Hi! thanks for the suggestion. I still lack some basic knowledge on how to insert the string, these 2 options result in an empty xml:

    " <rowsofreport>" & _
    " & strReportRows & " & _
    " </rowsofreport> " & _

    " <rowsofreport>" & _
    strReportRows & _
    " </rowsofreport> " & _

    Thank you!

    Thursday, January 19, 2017 11:18 AM
  • Hi,

    I think you should use the 2nd one because strReportRows is an variable.

    We use & to combine strings, but for an variable, there is no need use like " & strReportRows & ".

    You could use debug.print to output the combined string to check if it meets your requirement.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 23, 2017 8:07 AM
    Moderator