Hi,
I am new to this help forum and fairly new to access. I created a query and I convert it to xml format from a click(event procedure). I am able to create xml's fine through the below code, but I cannot figure out how to hide duplicate values as it is converting. All of my data is unique(and I used all the DISTINCT, and unique value settings), but of course I get duplicate xml tags because I read from the query row by row.
I used many IF and while loops to try and save the field to a variable , then compare it, but it still shows duplicates (so I removed them from the code). NOTE: I do not want to change any of the access table structure so I am trying to do this through VBA code only while converting. Thank You!
Public Sub ConvertXML_Click()
On Error GoTo Err_ConvertXML_Click
Dim db As Database
Dim MyAccess As String
Dim rs As Recordset
Dim fld As Field
Dim currDate As Date
currDate = Now()
MyAccess = "SELECT DISTINCT * FROM States_coefficientsTest"
If Len(Filter) > 0 Then
MyAccess = MyAccess & " WHERE " & Filter
End If
Set db = CurrentDb
Set rs = db.OpenRecordset(MyAccess, dbOpenSnapshot)
With rs
If rs.EOF = False Then
Open "C:\OMPS Database\Reports\cth\States_coefficientsTest7.xml" For Output As FreeFile
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " ?>"
Print #1, "<AppID App_ID=" & Chr(34) & "544" & Chr(34) & " >"
Print #1, currDate;
Do Until rs.EOF = True
Print #1, "<ROW>";
For Each fld In rs.Fields
With fld
Print #1, " <" & fld.Name & ">"
Print #1, fld;
Print #1, " </" & fld.Name & ">"
End With
Next fld
Print #1, "</ROW>"
rs.MoveNext
Loop
Print #1, "</AppID>"
End If
End With
Egress:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Close #1
ErrHandler:
MsgBox Err.Description
Resume Egress
Exit_ConvertXML_Click:
Exit Sub
Err_ConvertXML_Click:
MsgBox Err.Description
Resume Exit_ConvertXML_Click
End Sub
---------------------------------------------
Here is a sample snippet of the XML. As you can see, the last two elements are unique, but it keeps repeating the other elements.
I am new to this help forum and fairly new to access. I created a query and I convert it to xml format from a click(event procedure). I am able to create xml's fine through the below code, but I cannot figure out how to hide duplicate values as it is converting. All of my data is unique(and I used all the DISTINCT, and unique value settings), but of course I get duplicate xml tags because I read from the query row by row.
I used many IF and while loops to try and save the field to a variable , then compare it, but it still shows duplicates (so I removed them from the code). NOTE: I do not want to change any of the access table structure so I am trying to do this through VBA code only while converting. Thank You!
Public Sub ConvertXML_Click()
On Error GoTo Err_ConvertXML_Click
Dim db As Database
Dim MyAccess As String
Dim rs As Recordset
Dim fld As Field
Dim currDate As Date
currDate = Now()
MyAccess = "SELECT DISTINCT * FROM States_coefficientsTest"
If Len(Filter) > 0 Then
MyAccess = MyAccess & " WHERE " & Filter
End If
Set db = CurrentDb
Set rs = db.OpenRecordset(MyAccess, dbOpenSnapshot)
With rs
If rs.EOF = False Then
Open "C:\OMPS Database\Reports\cth\States_coefficientsTest7.xml" For Output As FreeFile
Print #1, "<?xml version=" & Chr(34) & "1.0" & Chr(34) & " ?>"
Print #1, "<AppID App_ID=" & Chr(34) & "544" & Chr(34) & " >"
Print #1, currDate;
Do Until rs.EOF = True
Print #1, "<ROW>";
For Each fld In rs.Fields
With fld
Print #1, " <" & fld.Name & ">"
Print #1, fld;
Print #1, " </" & fld.Name & ">"
End With
Next fld
Print #1, "</ROW>"
rs.MoveNext
Loop
Print #1, "</AppID>"
End If
End With
Egress:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Close #1
ErrHandler:
MsgBox Err.Description
Resume Egress
Exit_ConvertXML_Click:
Exit Sub
Err_ConvertXML_Click:
MsgBox Err.Description
Resume Exit_ConvertXML_Click
End Sub
---------------------------------------------
Here is a sample snippet of the XML. As you can see, the last two elements are unique, but it keeps repeating the other elements.
PHP:
-<AppID App_ID="544">
6/19/2013 7:52:11 AM
-<ROW>
<Tlm_ID>-2112284002</Tlm_ID>
<Tlm_Mnemonic>M_L_PHASE</Tlm_Mnemonic>
<Start_Bit>14</Start_Bit>
<Bit_Size>2</Bit_Size>
<Coefficient_0>Null</Coefficient_0>
<Coefficient_1>Null</Coefficient_1>
<Coefficient_2>Null</Coefficient_2>
<Coefficient_3>Null</Coefficient_3>
<Coefficient_4>Null</Coefficient_4>
<Coefficient_5>Null</Coefficient_5>
<State_Conversion_Name>A_MINUS_B_MINUS</State_Conversion_Name>
<State_Conversion_Value>2</State_Conversion_Value>
</ROW>
- <ROW>
<Tlm_ID>-2112284002</Tlm_ID>
<Tlm_Mnemonic>M_L_PHASE</Tlm_Mnemonic>
<Start_Bit>14</Start_Bit>
<Bit_Size>2</Bit_Size>
<Coefficient_0>Null</Coefficient_0>
<Coefficient_1>Null</Coefficient_1>
<Coefficient_2>Null</Coefficient_2>
<Coefficient_3>Null</Coefficient_3>
<Coefficient_4>Null</Coefficient_4>
<Coefficient_5>Null</Coefficient_5>
<State_Conversion_Name>A_MINUS_B_PLUS</State_Conversion_Name>
<State_Conversion_Value>1</State_Conversion_Value>
</ROW>
- <ROW>
<Tlm_ID>-2112284002</Tlm_ID>
<Tlm_Mnemonic>M_L_PHASE</Tlm_Mnemonic>
<Start_Bit>14</Start_Bit>
<Bit_Size>2</Bit_Size>
<Coefficient_0>Null</Coefficient_0>
<Coefficient_1>Null</Coefficient_1>
<Coefficient_2>Null</Coefficient_2>
<Coefficient_3>Null</Coefficient_3>
<Coefficient_4>Null</Coefficient_4>
<Coefficient_5>Null</Coefficient_5>
<State_Conversion_Name>A_PLUS_B_MINUS</State_Conversion_Name>
<State_Conversion_Value>3</State_Conversion_Value>
</ROW>
- <ROW>
<Tlm_ID>-2112284002</Tlm_ID>
<Tlm_Mnemonic>M_L_PHASE</Tlm_Mnemonic>
<Start_Bit>14</Start_Bit>
<Bit_Size>2</Bit_Size>
<Coefficient_0>Null</Coefficient_0>
<Coefficient_1>Null</Coefficient_1>
<Coefficient_2>Null</Coefficient_2>
<Coefficient_3>Null</Coefficient_3>
<Coefficient_4>Null</Coefficient_4>
<Coefficient_5>Null</Coefficient_5>
<State_Conversion_Name>INVALID</State_Conversion_Name>
<State_Conversion_Value>0</State_Conversion_Value>
</ROW>
</AppID>