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 All,

I hav also tried..

stDocName = Me.ListCP.Column(2)

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

I have figured out what the problem is, but I don't know how to fix it.

If I remove

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

Then the button works...
If I have the code above in the query; then even though the form MainScreen has the data in it, the prompt that appears after I click on the OutputToXml button, is the same code as above?!

Please help this is a strange error.
Do I have to declare the above code in the button code??

Thank you for your help

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear all,

update I have also removed the ".xml" which just added the name.

sFullPath = txtPath & stDocName [s]& ".xml"[/s]

Thanks

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

Any thoughts?

Thank you

Thank you,

Kind regards

Triacona

 

[PHV]

Did you try to open the query before exporting it ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Thanks for your reply, really appreciated

I don't open the query before exporting it...

Normally it used to work until I put the criteria in the query.

Is there any way to declare the criteria in the export?

Would I put in a Docmd.open query stdocname and then the export to XML code?

Thanks again for your help

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear PHV,

Thanks again for your help.

I have tried this and it still gives me an Error.

DoCmd.OpenQuery stDocName, acViewNormal
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml"

Run-time error '31532' Reports unable to export data

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear PHV,

Update :
I click debug and it highlights the following code.

[highlight]Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml"[/highlight]

The values in the variables are the following:
acExportQuery = 1
stDocName = CpSotFsaXml (correct)
sFullPath = G:\EH\CpSotFsaXml (correct)

I really don't know what could be causing the error, as the values are correct, the only value I am not certain about is acExportQuery = 1.

Your help would be greatly appreciated!

Thank you,

Kind regards

Triacona

 

[Taaner]

The constant acExportQuery always equals 1, it defines the type you want to export. In this case a query, that should be okay.

So what can be the problem:
1. the query does not work for any reason
2. the file you want to export to is not accessible

I should run the query itself, and if you have a result, and than export the result manually to the xml.

One of the possible reasons is that you run the program in sandbox mode. In Access 2007 the function ExportXML is blocked in the sandbox mode (dont know about 2010).

http://office.microsoft.com/en-us/a...2007-blocked-by-sandbox-mode-HA001230190.aspx

 

[Triacona]

Dear Taaner,

Thank you very much for your response

The query works as I run that from a form using a different button.

It runs the export when there are no criteria in the query.

It also fails exporting when there is criteria and I try to export it via File -> Export and then I choose XML; it askes for the criteria, as if it doesn't see the form and if nothing is entered it fails and if something is entered it does not bring the correct data in...

This is quite a weird error....

I am wondering if I would need to declare the SQL in the code and have a dynamic query?

Any further thoughts or help are greatly appreciated

Thank you!

Thank you,

Kind regards

Triacona

 

[Triacona]

Oh yes before I forget, I am running MS Access 2003

Thank you,

Kind regards

Triacona

 

[Triacona]

Dear all,

Below is the code I am using and I am still getting

Run-time error '31532' Reports unable to export data

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

[blue]    Dim [/blue]stDocName [blue]As String[/blue]
         stDocName = ListCP.Column(2)
[blue]    Dim[/blue] sFullPath [blue]As String[/blue]
         sFullPath = txtPath & stDocName
        
        DoCmd.OpenQuery stDocName, acViewNormal
        Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml"
        DoCmd.Close
        
[blue]End Sub[/blue]

I have no idea what the problem is, please help

Thank you so much!

Thank you,

Kind regards

Triacona

 

[PHV]

What about this if you remove the criteria in the query ?

Private Sub CpOutputQueryToXML_Click()
'*COMMERCIAL PREMISES CP OUTPUT TO XML BUTTON
Dim stDocName As String
stDocName = ListCP.Column(2)
Dim sFullPath As String
sFullPath = txtPath & stDocName
Dim strWhere As String
strWhere = "[[i]your field name here[/i]] Like '*" & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & "*'"
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml", , , , , , strWhere
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Thank you very much for your help

I have tried your way, I removed the criteria out of the query first, then tested it and that worked...

I then did the following..

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

[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] sWhere [blue]As String[/blue]
         sWhere = "TRADEAS Like '*" & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & "*'"
       

        Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml", , , , , , sWhere

    
[blue]End Sub[/blue]

The code above does not give an error message, but produces an incorrect xml file with just one field, the date and time it was created...

TRADEAS being the field in the query that is picked from a dropdown on the form MainScreen.

I would like to keep the criteria in the query as it is used on other buttons, (Run Query and Export Query to Excel)

So I am wondering if there is a way just for the xml button to create a dynamic query in the code so that there has to be no changes to other parts of the program.

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;

As you can see above, that is the sql for my query.

So if I will try to setup, something in the code that does so...

If you know of an easier way, that would be great!


Thank you,

Kind regards

Triacona

 

[Triacona]

Dear All,

I have now tried the following...

Private Sub CpOutputQueryToXML_Click()
'*COMMERCIAL PREMISES CP OUTPUT TO XML BUTTON

    [blue]Dim[/blue] db [blue]As[/blue] DAO.Database
    [blue]Dim[/blue] qdf [blue]As[/blue] DAO.QueryDef
    [blue]Dim[/blue] SQL [blue]As[/blue] String
[green]    'Dim Pack As String [/green]
    [blue]Dim[/blue] stDocName [blue]As String[/blue]
        stDocName = ListCP.Column(2)
    [blue]Dim[/blue] sFullPath [blue]As String[/blue]
        sFullPath = txtPath & stDocName
[green]    'Dim sWhere As String
        'sWhere = "[TRADEAS] Like '*" & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & "*'" [/green]
      
    [blue]Set[/blue] db = CurrentDb
    [blue]Set[/blue] qdf = db.QueryDefs("CpSotFsaXml")

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 " & _
[highlight]        "WHERE (((SotQ5.TRADEAS) Like " * " & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & " * ")) " & _[/highlight]
        "ORDER BY SotQ5.TRADEAS "

        [blue]Debug.Print[/blue] SQL
        
        Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml" [green] ', , , , , , sWhere[/green]
        
        qdf.Close
        
    [blue]Set[/blue] qdf = [blue]Nothing[/blue]
    [blue]Set[/blue] db = [blue]Nothing[/blue]
    

[blue]End Sub[/blue]

The above code gives me the following error..

Type mismatch

This is due to the code above highlighted in yellow...
If I take it out I get the same error

Reports unable to export data

So back to square 1! mooo
Am I coding the SQL code highlighted in yellow properly?
Please help this problem is doing my head in
Thank you

Thank you,

Kind regards

Triacona

 

[Triacona]

Any thoughts anyone?
Any help would be greatly appreciated, I am quite stuck on this problem...
Thank you

Thank you,

Kind regards

Triacona

 

[Taaner]

You should solve that type mismatch first by analysing the sql-statement at runtime.

 

[PHV]

Private Sub CpOutputQueryToXML_Click()
'*COMMERCIAL PREMISES CP OUTPUT TO XML BUTTON
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim SQL As String
    Dim stDocName As String
        stDocName = ListCP.Column(2)
    Dim sFullPath As String
        sFullPath = txtPath & stDocName
    Set db = CurrentDb
    Set qdf = db.QueryDefs("CpSotFsaXml")
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 "
        Debug.Print SQL
        [!]qdf.SQL = SQL
        db.QueryDefs.Refresh
        Application.ExportXML acExportQuery, "CpSotFsaXml", sFullPath & ".xml"[/!]
    Set qdf = Nothing
    Set db = Nothing
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV and Taaner,

Thanks for all the help !

@PHV I have implemented your solution, thank you so so much!. Have a star!
I have no more errors or prompts for the TRADEAS.

The only bug left is, the XML file output is 1kb and has none of the records in the xml file, it just has the date and time it was created...

I run the query and in the criteria of TRADEAS it has:

Like '*  *'

So somehow the calling of the combo box value is not working...

I have tried a few different approaches...
It doesn't seem to work...

Any further help will be most appreciated, I am a bit of a newbie at using SQL in code, should I be calling it differently?

Thank you for your forthcoming help


Thank you,

Kind regards

Triacona