exporting to xml and recordsets

[sha7jpm]

hi all

I have some code which takes a table, and exports it to xml
this is fine but I have now added in some code which gives a list of providers (URN) and creates a file just for the pupils at that particular provider..

the idea being that I have 150 xml files 1 for each provider..

my code though does produce the right amount of files and with the correct filename for each one, but the data does not filter so for provider 111 I get all the info for all pupils, not just 111

I think the issue is with the querydef where I have a maketable (so I can do the xml export) I think the query is not redoing itself eachtime....
help!

Public Function ExportXML()

Set dBase = CurrentDb()
Set repQuery = dBase.QueryDefs("qry2_xml_export_file_ey")
Set rsRep = CurrentDb.OpenRecordset("URN_list_for_export")

Do While Not rsRep.EOF
data1 = rsRep.Fields("URN").Value

repQuery.sql = "SELECT * INTO ey_07_xml_export_file_ FROM qry1_xml_export_file_ey WHERE ((([qry1_xml_export_file_ey].Establishment.URN)= " & data1 & "));"
repQuery.Close

' Function to export a table as XML
Application.ExportXML acExportTable, "ey_07_xml_export_file_", "C:\" & "ey_07_xml_export_file_" & data1 & ".xml"

rsRep.MoveNext
Loop

Set rsRep = Nothing

Set rsRep = Nothing

End Function

Private Sub export_Click()
Call ExportXML
End Sub

 

[sha7jpm]

fixed!

I seem to have fixed it!

I amended my code as follows (see below) and also my basetbl has the field as a text and I needed it as a number so that also resolved the issue..

Public Function ExportXML()

Set dBase = CurrentDb()
Set repQuery = dBase.QueryDefs("qry2_xml_export_file_ey")
Set rsRep = CurrentDb.OpenRecordset("URN_list_for_export")

Do While Not rsRep.EOF
data1 = rsRep.Fields("URN").Value

DoCmd.DeleteObject acTable, "ey_07_xml_export_file_"
repQuery.sql = "SELECT * INTO ey_07_xml_export_file_ FROM qry1_xml_export_file_ey WHERE ((([qry1_xml_export_file_ey].Establishment.URN)= " & data1 & "));"
repQuery.Execute
repQuery.Close


' Function to export a table as XML
Application.ExportXML acExportTable, "ey_07_xml_export_file_", "C:\" & "ey_07_xml_export_file_" & data1 & ".xml"

rsRep.MoveNext
Loop

Set rsRep = Nothing

Set rsRep = Nothing

End Function

Private Sub export_Click()
Call ExportXML
End Sub