access 2002 vba and exportXML method

[jeff maultby]

Hi
Found bug with ExportXML method when query is used to
populate form. Can anyone help with a fix please?

To reproduce please do the following:
Access 2002

Create new db

Create 2 simple master detail tables, use the wizards for
contacts &
orders, relate orders to contacts via contact id. Contact
tabel has ContactID, firstname, lastname. Orders table
has OrderID, ContactID, PurchaseOrderNumber.

Create 2 forms for each table, use the wizards.

Add command button to contact form. In command button
code add
"DoCmd.OpenForm Orders".

Add query named "Orders Query" with content of
SELECT Orders.OrderID, Orders.ContactID,
Orders.PurchaseOrderNumber
FROM Orders
WHERE Orders.ContactID=Forms!Contacts!ContactID;

In Record Source properties of Orders form select "Orders
Query".

Run Contacts form and see that orders form opens and
shows only those
orders for the current contact in the contact form.

Add command button to orders form labelled "Export XML"
and named
cmdExportXML.

In code for cmdExportXML button add
"Application.ExportXML
acExportForm, "Orders", "Orders.xml","Orders.xsd", "Orders
..xsl"

Run contacts form, click orders button, click Export XML
button and note
that a dialog opens asking for Forms!Contacts!ContactID

This does not happen with the form. Neither does it
happen with OutputTo
method which correctly generates XLS, RTF etc without
prompting.

I have a very small access 2002 mdb file which replicates
this problem, if u wish it please email me.

Jeff

 

[Dev Ashish]

Run contacts form, click orders button, click Export XML
button and note
that a dialog opens asking for Forms!Contacts!ContactID

I think ExportXML only works with Static objects and doesn't handle
paramters.

One workaround, using DAO, would be to replace the parameters of a query
with it's evaluated value and set the resulting SQL to a temp querydef.
This temp querydef can then be exported out using ExportXML.

Here's a sample proc...

' *** Code Start ***
Sub ExportQuery(strQryName As String)
Dim qdf As DAO.QueryDef, qdfTemp As DAO.QueryDef
Dim p As DAO.Parameter
Dim sql As String, tmp As String
Const tmpQry As String = "tmpQuery"
Const Q = """"
On Error GoTo ErrHandler

Set qdf = Application.CurrentDb.QueryDefs(strQryName)
On Error Resume Next
Set qdfTemp = Application.CurrentDb.QueryDefs(tmpQry)
If (qdfTemp Is Nothing) Then
Set qdfTemp = Application.CurrentDb.CreateQueryDef(tmpQry)
End If
On Error GoTo ErrHandler

sql = qdf.sql

For Each p In qdf.Parameters
tmp = p.Name
If (p.Type = dbText Or p.Type = dbMemo Or p.Type = dbChar) Then
sql = Replace$(sql, p.Name, Q & Eval(p.Name) & Q)
ElseIf (p.Type = dbTime Or p.Type = dbTimeStamp) Then
sql = Replace$(sql, p.Name, "#" & Eval(p.Name) & "#")
Else
sql = Replace$(sql, p.Name, Eval(p.Name))
End If
Next
qdfTemp.sql = sql

With Application
.ExportXML acExportQuery, tmpQry, strQryName & ".xml", _
strQryName & ".xsd", strQryName & ".xsl"
End With
qdfTemp.Close
qdf.Close

Exit Sub
ErrHandler:
With Err
MsgBox "Error: " & .Number & vbCrLf & _
.Description, vbCritical
End With
End Sub
' *** Code End ***

-- Dev

 

[jeff maultby]

Thanks very much
However i've had an other suggestion which is to
construct the sql within vba in the child form open event
and the parent form current event and assign it to the
child form's recordsource property. seems to work ok,
thanks