Export XML from query, current form record only (1 Viewer)

[Geronimo]

Hi

I'm going round in circles and, as a pretty amateur Access bod, wondered if anyone might be able to point me in the right direction with this, I'll try to describe the issue as best I can! :

My users complete a form called claims to create a new claim. Each new claim gets a unique auto number (claim number). I want the user to be able to click a button on the form that exports the output of a query based on the claim number on the form, so they just export to XML the claim they have just created.

I have created the query and used the criteria [Forms]![Claims]![Claim number] in the Claim number field of the query (the query uses data from linked tables to populate results). To test I created a command button on the form to run the query and this works perfectly. But, this just opens the query result so I now want to create a command button that rather than showing the query result, just exports the result as xml to a network folder.

My problem is that I do not know how to edit the VBA in a Application.ExportXML to make the query use the claim number on the form, I'm guessing that I need to add a command to set the paramaters to be used but am clueless on how to achieve this!

The code I've been playing with is this below, which when run, pops up a box asking for the claim number (assuming that this means the query does not know which claim number I want to use), once the claim number is inputted it runs fine but I want it to know the claim number without asking as I don't want the users having to input it manually as that introduces opportunity for human error....hope that makes sense:

Private Sub Command306_Click()
On Error GoTo Err_Command306_Click
Application.ExportXML ObjectType:=acExportQuery, DataSource:="Repairtech Job order_NEW TEST", _
Datatarget:="\\10.1.0.5\data\Repairtech\Data Transfers\EISL Jobs to RT\EISL AGA Job order" & Format(Now, "_dmmmyy_hhnnss") & ".xml"

Exit_Command306_Click:
Exit Sub

Err_Command306_Click:
MsgBox Err.Description
Resume Exit_Command306_Click

End Sub

Any help anyone could give would be much appreciated, thanks.

 

[arnelgp]

you need to be sure Form [Forms]![Claims] is open when you do the Export
since your parameter is based on this form.

 

[Geronimo]

Thanks arnelgp. That's what's confusing me as the form is open (the cmd button is on the form itself so has to be open to be clicked in the first place) but it doesn't seem to tie up.

 

[Geronimo]

The test cmd button on the form successfully runs the query (code for that one below) but the xml export cmd button on the form (code above) does not. Has me scratching my head!

Private Sub Command305_Click()
On Error GoTo Err_Command305_Click

Dim stDocName As String

stDocName = "Repairtech Job order_NEW TEST"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command305_Click:
Exit Sub

Err_Command305_Click:
MsgBox Err.Description
Resume Exit_Command305_Click

End Sub

 

[arnelgp]

what type is Claim Number]? string or numeric?

better you create a Dummy Query from the Original query using vba.
then use this dummy query to export.
but before you export, you change the Parameter, [Forms]![Claims]![Claim number]
to the Actual Value of the variable:

Private Sub Command306_Click()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim sql As String
Set db = CurrentDb
'delete old dummy query
DoCmd.DeleteObject acQuery, "zzDummy"
'copy Original Query to dummy
DoCmd.CopyObject , "zzDummy", acQuery, "yourOriginalQueryNameHere"
'refresh window
Application.RefreshDatabaseWindow
Set qd = db.QueryDefs("zzDummy")
sql = Replace$(qd.sql, "[Forms]![Claims]![Claim number]", [Forms]![Claims]![Claim number])
qd.sql = sql
qd.Close
Set qd = Nothing
Set db = Nothing
On Error GoTo Err_Command306_Click
'now do the Export using zzDummy query
Application.ExportXML ObjectType:=acExportQuery, DataSource:="zzDummy", _
Datatarget:="\\10.1.0.5\data\Repairtech\Data Transfers\EISL Jobs to RT\EISL AGA Job order" & Format(Now, "_dmmmyy_hhnnss") & ".xml"

Exit_Command306_Click:
Exit Sub

Err_Command306_Click:
MsgBox Err.Description
Resume Exit_Command306_Click

End Sub

 

[Geronimo]

Claim number is a number (autonumber)

Crikey, more involved than I thought! I'll try the above in a mo and let you know how I get on...thanks

 

[Geronimo]

Hi, that worked a treat, thank you very, very much, saved me a lot of time and head scratching!

.....just a quickie, if I wanted to include a field from the query output (or a field from the form) in the exported filename, is there an easy way to do that? Its a nice to have, not a need to have!

Thanks Again.

 

[Geronimo]

Scrub that last question, sussed it out!

Thanks again for your help.