Mark,
1. It uses ADO
2. There's a lot of extra stuff here
3. It exports html, not xml
But, the principle is the same. Hope it gives you a start.
Darryl Kerkeslager
---------------------------------------
Private Sub btnUpdatePDA_Click()
On Error GoTo handle_error
errText = " @ Start"
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Dim charCounter As Long
Dim dq As String
Dim p As Long
Dim sOff As String
Dim vOff As String
Dim safeNotes As String
Dim safeTats As String
Dim filePhoto As String
dq = Chr$(34)
errText = " @ Offenders.html"
Open odisFolder & "offenders.html" For Output As #1
Print #1, "<html><body><center>District #" & GetDistrictID() & "
Offenders</center><center>ODIS v." & GetOdisVersion() & "</center><center>"
& Now() & "</center><hr/>"
For charCounter = 65 To 90
Print #1, "<input TYPE=" & dq & "button" & dq & " VALUE=" & dq &
Chr$(charCounter) & dq & "onClick=" & dq & "window.location.href='" &
Chr$(charCounter) & ".html'" & dq & "></input>"
Next charCounter
Print #1, "<input TYPE=" & dq & "button" & dq & " VALUE=" & dq &
"Fugitives" & dq & "onClick=" & dq & "window.location.href='fugitives.html'"
& dq & "></input>"
Print #1, "</body></html>"
Close #1
For charCounter = 65 To 90
Open odisFolder & Chr$(charCounter) & ".html" For Output As #1
Print #1, "<html><body>"
With rs
.Source = "SELECT * FROM PDAQuery" & InServer() & "WHERE off_name
Like '" & Chr(charCounter) & "%' ORDER BY off_name"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText
errText = " @ Begin Query"
Do While Not .EOF
p = CLng(.Fields("offender_id"))
If .Fields("off_sex_offender") Then
sOff = "* Sex Offender"
Else
sOff = ""
End If
If .Fields("off_violent") Then
vOff = "* History of Violence"
Else
vOff = ""
End If
If IsNull(.Fields("off_tattoo")) Then
safeTats = ""
Else
safeTats = StrSafe(CStr(.Fields("off_tattoo")), Chr(34),
Chr(39))
End If
If IsNull(.Fields("cs_pda_notes")) Then
safeNotes = ""
Else
safeNotes = StrSafe(CStr(.Fields("cs_pda_notes")),
Chr(34), Chr(39))
End If
filePhoto = "/" & cardName & "/" & Format(Int(p / 1000),
"0000") & "/" & Format(p, "0000000") & "A.jpg"
frm.BottomMessage ("Data file: " & CStr(p) & ".html")
errText = " @ Open file"
Open odisFolder & CStr(p) & ".html" For Output As #2
Print #2, "<html><body><a name=" & dq & "T" & dq & "></a><a
href=" & dq & "#I" & dq & ">Identifiers</a> | <a href=" & dq & "#N" & dq &
">Notes</a> | <a href=" & dq & "#P" & dq & ">Photo</a><br/><br/>"
Print #2, "<b>" & RTrim(.Fields("off_name")) &
"</b><br/><hr/>"
If Len(Trim(.Fields("off_cur_location"))) > 0 Then Print #2,
..Fields("off_cur_location") & "<br/>"
Print #2, .Fields("off_address1") & "<br/>"
Print #2, .Fields("off_address2") &
" <b>Map: </b> " & .Fields("off_map_id") & "<br/>"
If Len(Trim(.Fields("off_cur_location"))) = 0 Then Print #2,
"<br/>"
Print #2, Nz(Format(.Fields("off_phone"), "(@@@) @@@-@@@@"),
"") & "<br/><br/>"
Print #2, "<b>PO: </b>" & .Fields("ppo_login") & "<br/>"
Print #2, "<b>Opened: </b>" & .Fields("cs_open_date") &
"<br/>"
Print #2, "<b>Last PC: </b>" & .Fields("cs_last_pc") &
"<br/>"
Print #2, "<b>PC Due: </b>" & .Fields("cs_pc_due") &
"<br/><br/>"
Print #2, sOff & "<br/>"
Print #2, vOff & "<br/>"
Print #2, "<b>Mental Health: </b>" & .Fields("mh_diagnosis")
& "<br/><br/><hr/>"
Print #2, "<a name=" & dq & "I" & dq & "></a>"
Print #2, .Fields("off_race") & "/" & .Fields("off_sex") &
" " & Format(.Fields("off_height"), "#'##") &
" " & .Fields("off_weight") & "lbs<br/>"
Print #2, "<b>Tattoos: </b>" & safeTats & "<br/><br/>"
Print #2, "<b>DOB: </b>" & .Fields("off_dob") & "<br/>"
Print #2, "<b>SSN: </b>" & Format(.Fields("off_ssn"),
"###-##-####") & "<br/>"
Print #2, "<b>Vaccis: </b>" & .Fields("offender_id").Value &
"<br/><br/>"
Print #2, "<a href=" & dq & "#T" & dq & ">Top</a><hr/><a
name=" & dq & "N" & dq & "></a>"
Print #2, "<b>Notes: </b>" & safeNotes & "<br/><br/><hr/>"
Print #2, "<a name=" & dq & "P" & dq & "></a>"
Print #2, "<img src=" & dq & filePhoto & dq & "><br/>"
Print #2, "<a href=" & dq & "#T" & dq &
">Top</a></body></html>"
Close #2
Print #1, "<a href=" & dq & .Fields("offender_id").Value &
".html" & dq & ">" & RTrim(.Fields("off_name")) & "</a><br>"
.MoveNext
Loop
.Close
End With
Print #1, "<br><br><a href=" & dq & "#TOP" & dq & ">Top of
Page</a></body></html>"
Close #1
Next charCounter
With rs
Open odisFolder & "fugitives.html" For Output As #3
Print #3, "<html><body>"
.Source = "SELECT offender_id, off_name FROM PDAQuery" & InServer() &
"WHERE cs_level = 'A' ORDER BY off_name"
.Open , cnxn, adOpenForwardOnly, adLockReadOnly, adCmdText
Do While Not .EOF
Print #3, "<a href=" & dq & .Fields("offender_id").Value &
".html" & dq & ">" & RTrim(.Fields("off_name")) & "</a><br>"
.MoveNext
Loop
.Close
Print #3, "<br><br><a href=" & dq & "#TOP" & dq & ">Top of
Page</a></body></html>"
Close #3
End With
Exit Sub
handle_error:
LogError Err.Number, "FieldContactForm.btnUpdatePDA", Err.Description &
errText
End Sub
"Mark" <by*******@hotmail.com> wrote:
I am trying to export an access 2003 query to XML and need to get it
to export in a very specific format. I am stuck and could use some
help badly (time is critical with this project).
This is what we have:
<?xml version="1.0" encoding="UTF-8" ?>
- <dataroot xmlns:od="urn:schemas-microsoft-com:officedata"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="order.xsd"
generated="2004-10-26T18:32:43">
- <order>
<order_number>24158</order_number>
<marketchannel>TIMCUST</marketchannel>
<Ship_Method>GRD</Ship_Method>
<Ship_To_Name>Some_Company_Name</Ship_To_Name>
<Ship_To_Address_1>44 SW here ave</Ship_To_Address_1>
<Ship_To_Address_2 />
<Ship_To_City>WORCESTER</Ship_To_City>
<Ship_To_State>MA</Ship_To_State>
<Ship_To_Zip>01608</Ship_To_Zip>
<Product_ID>1096.</Product_ID>
<UOM>EACH</UOM>
<Product_Quantity>4</Product_Quantity>
</order>
This is what we need:
- <Orders>
<customer_number>111111</customer_number>
<total_line_items>4</total_line_items>
- <Order>
<order_number>29381274</order_number>
<marketchannel>SOME_MARKET_CHANNEL</marketchannel>
<Ship_Method>FDG</Ship_Method>
<Ship_To_Name>SHIP NAME</Ship_To_Name>
<Ship_To_Address_1>SHIP ADDRESS 1</Ship_To_Address_1>
<Ship_To_Address_2>SHIP ADDRESS 2</Ship_To_Address_2>
<Ship_To_City>SHIP CITY</Ship_To_City>
<Ship_To_State>CO</Ship_To_State>
<Ship_To_Zip>12345</Ship_To_Zip>
- <Order_Line_Item>
<Line_number>01</Line_number>
<Product_ID>ITEM_CODE</Product_ID>
<UOM>EA</UOM>
<Product_Quantity>25</Product_Quantity>
<Rush_Code>N</Rush_Code>
</Order_Line_Item>
- <Order_Line_Item>
<Line_number>02</Line_number>
<Product_ID>ITEM_CODE</Product_ID>
<UOM>EA</UOM>
<Product_Quantity>50</Product_Quantity>
<Rush_Code>N</Rush_Code>
</Order_Line_Item>
</Order>
The problem I have is the first few lines. I need to drop the first 4
lines as well as moving <customer_number> and <total_line_items> into
an <Orders> header as shown in the second sample.
How can I achieve this or is it even possible with Access?
Any help would be great.
Thanks
