Output to XML button not working anymore... 1

[Triacona]

Dear All,

Thank you all for a great forum!

I have the following problem.

The code below is giving me a

Run-time error 31532.
EH Reports was unable to export the data.

Code for button below.

[blue]Private Sub CpOutputQueryToXML_Click()[/blue]
[green]'*COMMERCIAL PREMISES CP OUTPUT TO XML BUTTON[/green]

[blue]Dim[/blue] stDocName [blue]As String[/blue]
    stDocName = Me!ListCP.Column(2)

[blue]Dim[/blue] sFullPath [blue]As String[/blue]
    sFullPath = txtPath & stDocName & ".xml"

Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml"
 
 
End Sub

I have tried changing a number of things but to not avail...
The query for this export also takes a value off the MainScreen form.

Like "*" & [forms]![MainScreen]![SubFrmInput].[form]![cmbCpTradeAs] & "*"

Please help I have been banging my head on this one
Thank you for all the forthcoming help

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear PHV,
Sorry I didn't see the spacing in the SQL (that make a difference?? why?)...

Like '*" & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & "*'

I had spaces with '* " and " *' now corrected.

And it is inputing the correct data from the form...

The only problem is it does not export that data to the xml file...
I run the query and the data is all there.

I check the xml file and it has on the Date and time it was created, no records...

Below is the xml file output if opened in Notepad.

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" generated="2012-03-06T15:51:57"/>

Is there anything else to configure within the Application.ExportXML command.

Thank you again for all of your help and forthcoming help


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear all,
I have changed the Xml output code slightly...

There is 1 positive...it now displayes the fields as headers in the XML file.

BUT it still does not display the data within those fields!!

DOH!!

I have no idea what to do now, I have read the MS helpfile and implemented the suggested changes in my output to code but no data...

Please see my code below..

[blue]Private Sub[/blue] CpOutputQueryToXML_Click()
[green]'*COMMERCIAL PREMISES CP OUTPUT TO XML BUTTON[/green]

    [blue]Dim[/blue] db [blue]As[/blue] DAO.Database
    [blue]Dim[/blue] qdf [blue]As[/blue] DAO.QueryDef
    [blue]Dim[/blue] SQL [blue]As String[/blue]
    [blue]Dim[/blue] stDocName [blue]As String[/blue]
        stDocName = ListCP.Column(2)
    [blue]Dim[/blue] sFullPath [blue]As String[/blue]
        sFullPath = txtPath & stDocName
    [blue]Dim[/blue] sXmlInfo [blue]As String[/blue]
        sXmlInfo = txtPath & "XmlInfo"
      
    [blue]Set[/blue] db = CurrentDb
    [blue]Set[/blue] qdf = db.QueryDefs(stDocName)

SQL = "SELECT SotQ5.REFVAL, SotQ5.TRADEAS, SotQ5.Con, SotQ5.CONTACT, SotQ5.UPRN, SotQ5.Add, " & _
        "SotQ5.POSTCODE, SotQ5.InspTyp, SotQ5.SCORE, SotQ5.TARGET_DATE, SotQ5.MainUse, SotQ5.MainUseDsc, " & _
        "SotQ5.SotUseDsc, SotQ5.Band, SotQ5.CPCONTTYPE, SotQ5.CONTADD, SotQ5.ACTUAL_DATE, SotQ5.QScore AS Q5, " & _
        "SotQ6.QScore AS Q6, SotQ7.QScore AS Q7, SotRating.TotalBcScore, SotRating.Rating, " & _
        "SotRating.LaemsUse AS businesstype, SotQ5.AddLine, AddressOnly(SotQ5.ADDRESS) AS AddO " & _
        "FROM SotRating " & _
        "INNER JOIN ((SotQ5 INNER JOIN SotQ6 ON SotQ5.REFVAL = SotQ6.REFVAL) " & _
        "INNER JOIN SotQ7 ON SotQ6.REFVAL = SotQ7.REFVAL) ON SotRating.REFVAL = SotQ5.REFVAL " & _
        "WHERE (((SotQ5.TRADEAS) Like '*" & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & "*')) " & _
        "ORDER BY SotQ5.TRADEAS"

        [blue]Debug.Print[/blue] SQL
        qdf.SQL = SQL
        db.QueryDefs.Refresh
        
        Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml", sXmlInfo, txtPath & "XmlData", , acUTF16

    qdf.Close
        
    [blue]Set[/blue] qdf = [blue]Nothing[/blue]
    [blue]Set[/blue] db = [blue]Nothing[/blue]
    

[blue]End Sub[/blue]

Please help, this is driving me up the wall...
Thank you

Thank you,

Kind regards

Triacona

 

[Triacona]

Any thoughts anyone?
Thanks!

Thank you,

Kind regards

Triacona

 

[Triacona]

Any thoughts?

Thank you,

Kind regards

Triacona

 

[Triacona]

Help...!

Thank you,

Kind regards

Triacona

 

[Triacona]

Please is there anyone who can help further???
Thank you!

Thank you,

Kind regards

Triacona

 

[Triacona]

Hi,
I exported the data to Excel, then tried to save it as am XML file.
This did not work...
I then used Data->Xml->Xml source.
I then clicked on XML maps...and choose the XML file I outputed with the XML button in I created in Access.
This then gave me the headings, I then choose each heading respectively and choose the xml data heading by double clicking.
This then enabled me to save as XML with all data...
I am wondering if that is what is needed in Access...
I have the headings, all I need now is to string in the data...

Please help I am really stuck on this
Thanks



Thank you,

Kind regards

Triacona

 

[Triacona]

Please is there anyone who can help further???
Thank you!

Thank you,

Kind regards

Triacona