current record to XML

[wailingrecluse]

All

I have written code, which places data into an XML file, using an HTML template for styling:
Public Sub generatexml3()
' declare variables
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strOut As String
Dim strOuts As String
Dim fs As Object
Dim xmlFile As Object

'create and execute the query

strSQL = "SELECT * FROM Q_email;"
Set rs = CurrentDb.OpenRecordset(strSQL)

'build a basic xml document
strOut = "<?xml version='1.0'?>"
strOut = "<?xml-stylesheet type=""text/xsl"" href=""J:\my data\genesys comms\template.xsl""?>"
strOut = strOut & "<faults>" & vbCrLf
Do Until rs.EOF
strOut = strOut & getTab(1) & "<genesys>" & vbCrLf
strOut = strOut & getTab(2) & "<fault_ref>" _
& rs.Fields("fault_ref") & "</fault_ref>" & vbCrLf
strOut = strOut & getTab(3) & "<priority>" _
& rs.Fields("priority") & "</priority>" & vbCrLf
strOut = strOut & getTab(4) & "<issue>" _
& rs.Fields("issue") & "</issue>" & vbCrLf
strOut = strOut & getTab(5) & "<BusinessImpact>" _
& rs.Fields("business_impact") & "</BusinessImpact>" & vbCrLf
strOut = strOut & getTab(6) & "<DateTimeRaised>" _
& rs.Fields("date/time_raised") & "</DateTimeRaised>" & vbCrLf
strOut = strOut & getTab(7) & "<RaisedBy>" _
& rs.Fields("raised_by") & "</RaisedBy>" & vbCrLf
strOut = strOut & getTab(8) & "<sites>" _
& rs.Fields("site(s)_affected") & "</sites>" & vbCrLf
strOut = strOut & getTab(9) & "<thirdparty>" _
& rs.Fields("third_party") & "</thirdparty>" & vbCrLf
strOut = strOut & getTab(10) & "<update>" _
& rs.Fields("update") & "</update>" & vbCrLf
strOut = strOut & getTab(11) & "<datetime>" _
& rs.Fields("date/time") & "</datetime>" & vbCrLf
strOut = strOut & getTab(1) & "</genesys>" & vbCrLf
rs.MoveNext
Loop

'clean up by shutting down the recordset
rs.Close
Set rs = Nothing
' add closing xml tag
strOut = strOut & "</faults>" & vbCrLf
' write out the xml document
Set fs = CreateObject("Scripting.FileSystemObject")

Set xmlFile = fs.CreateTextFile("J:\my data\genesys comms\fault_ref.xml", True)

xmlFile.Write (strOut)
xmlFile.Close

End Sub


Unfortunately, if a new fault is added and the "email" button is clicked - the code runs and pastes every fault into the XML file.

In other words, this only works properly if the faults table is empty.

How do I tell VBA to select the current record only from the form? I tried changing each line from strOut = strOut & getTab(7) & "<RaisedBy>" _
& rs.Fields("raised_by") & "</RaisedBy>" & vbCrLf to

strOut = strOut & getTab(7) & "<RaisedBy>" _
& rs.Fields(Forms![tbl_faults].[raised_by]) & "</RaisedBy>" & vbCrLf

But this tells me that "tbl_faults" is not associated with any macro or vba code

Any ideas?

Thanks a lot,

Richard

 

[namliam]

I have not looked at your unformated code, but... i understand you only want the current record of the form... try this:
strSQL = "SELECT * FROM Q_email where yourfield = '" & Forms![tbl_faults].[raised_by] & "'"

 

[wailingrecluse]

Hi

Thanks for the quick reply - im getting the error "cant find the form 'tbl_faults' referred to in a macro expression or visual basic code

 

[namliam]

Well I copied it over from your code, just change the tbl... to what ever the form is called

 

[wailingrecluse]

Well I copied it over from your code, just change the tbl... to what ever the form is called

yes, tried that when you sent it and got that error message

 

[namliam]

The error message simply says " the form tbl_faults doesnt exist "

Cannot make anything else of it.

 

[wailingrecluse]

HI

using "SELECT * FROM Q_email where strOut = '" & Forms![tbl_faults].[raised_by] & "'"

does nothing... still pulling all details and not just the current record

 

[gemma-the-husky]

why dont you mess around with a visual query, until you get one that selects the records you want - and then check out the sql for THAT query.

 

[wailingrecluse]

Hi all

Thanks to you both for your help.

I finally wrote a query for the save button which writes the current record to a holding table.

Then another query so that after the data is pulled from the holding table, the contents of the holding table are deleted.

Bit of a work around, but it works.

Thanks again for your help and suggestions.