INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...You have made an incredible site which is truly a great help to me in solving problems. A tip of my hat to you!..."
Geography
Where in the world do Tek-Tips members come from?
|
Output to XML button not working anymore...
|
|
Dear All, Thank you all for a great forum!  I have the following problem. The code below is giving me a Quote (error):Run-time error 31532.
EH Reports was unable to export the data.
Code for button below. CODEPrivate Sub CpOutputQueryToXML_Click()
'*COMMERCIAL PREMISES CP OUTPUT TO XML BUTTON
Dim stDocName As String
stDocName = Me!ListCP.Column(2)
Dim sFullPath As String
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. CODELike "*" & [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 |
|
Dear All, I hav also tried.. CODEstDocName = Me.ListCP.Column(2) Thank you,
Kind regards
Triacona |
|
Dear All, I have figured out what the problem is, but I don't know how to fix it. If I remove CODELike "*" & [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 (TechnicalUser) |
24 Feb 12 10:22 |
Dear all, update I have also removed the ".xml" which just added the name. CODEsFullPath = txtPath & stDocName & ".xml" Thanks Thank you,
Kind regards
Triacona |
|
Dear All, Any thoughts? Thank you Thank you,
Kind regards
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 |
|
Dear PHV, Thanks again for your help. I have tried this and it still gives me an Error. CODE
DoCmd.OpenQuery stDocName, acViewNormal
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml" Quote (Error:):Run-time error '31532' Reports unable to export data
Thank you,
Kind regards
Triacona |
|
Dear PHV, Update : I click debug and it highlights the following code. CODE
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml"
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 (Programmer) |
27 Feb 12 10:36 |
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/access-help/functions-and-properties-in-access-2007-blocked-by-sandbox-mode-HA001230190.aspx |
|
|
Triacona (TechnicalUser) |
27 Feb 12 11:28 |
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 (TechnicalUser) |
27 Feb 12 11:29 |
Oh yes before I forget, I am running MS Access 2003 Thank you,
Kind regards
Triacona |
|
Dear all, Below is the code I am using and I am still getting Quote (error 31532):Run-time error '31532' Reports unable to export data
CODEPrivate 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
DoCmd.OpenQuery stDocName, acViewNormal
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml"
DoCmd.Close
End Sub
I have no idea what the problem is, please help Thank you so much! Thank you,
Kind regards
Triacona |
|
|
PHV (MIS) |
28 Feb 12 10:20 |
|
|
Triacona (TechnicalUser) |
28 Feb 12 11:29 |
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.. CODEPrivate 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 sWhere As String
sWhere = "TRADEAS Like '*" & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & "*'"
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml", , , , , , sWhere
End Sub 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. CODESELECT 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 |
|
Dear All, I have now tried the following... CODEPrivate 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 Pack As String
Dim stDocName As String
stDocName = ListCP.Column(2)
Dim sFullPath As String
sFullPath = txtPath & stDocName
'Dim sWhere As String
'sWhere = "[TRADEAS] Like '*" & Forms!MainScreen!SubFrmInput.Form!cmbCpTradeAs & "*'"
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
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml" ', , , , , , sWhere
qdf.Close
Set qdf = Nothing
Set db = Nothing
End Sub
The above code gives me the following error.. Quote (Run-time error '13':):
Type mismatch
This is due to the code above highlighted in yellow... If I take it out I get the same error Quote (Run-time error '31532':):
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 |
|
Any thoughts anyone? Any help would be greatly appreciated, I am quite stuck on this problem... Thank you Thank you,
Kind regards
Triacona |
|
|
Taaner (Programmer) |
6 Mar 12 7:18 |
You should solve that type mismatch first by analysing the sql-statement at runtime. |
|
|
PHV (MIS) |
6 Mar 12 8:23 |
CODEPrivate 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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer? |
|
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: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 |
|
Dear PHV, Sorry I didn't see the spacing in the SQL (that make a difference?? why?)... CODELike '*" & 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. CODE<?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 |
|
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.. CODEPrivate 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
Dim sXmlInfo As String
sXmlInfo = txtPath & "XmlInfo"
Set db = CurrentDb
Set 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"
Debug.Print SQL
qdf.SQL = SQL
db.QueryDefs.Refresh
Application.ExportXML acExportQuery, stDocName, sFullPath & ".xml", sXmlInfo, txtPath & "XmlData", , acUTF16
qdf.Close
Set qdf = Nothing
Set db = Nothing
End Sub Please help, this is driving me up the wall... Thank you Thank you,
Kind regards
Triacona |
|
Any thoughts anyone? Thanks! Thank you,
Kind regards
Triacona |
|
|
Triacona (TechnicalUser) |
16 Mar 12 12:10 |
Any thoughts? Thank you,
Kind regards
Triacona |
|
Help...! Thank you,
Kind regards
Triacona |
|
Please is there anyone who can help further??? Thank you! Thank you,
Kind regards
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 |
|
Please is there anyone who can help further??? Thank you! Thank you,
Kind regards
Triacona |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One