26
Hi
I am trying to write a select query that prompts a user for an application number that will call a recordset from an already existing table that will be used to export XML file data that will later be transposed to another XML File. In my first sub procedure, I keep getting the runtime error message with the From statement. Would someone please help me. PS. any suggestions are for the code below is greatly appreciated. I am writing this code for use in Access 2003 and my OS is Windows XP.
Function TransformData()
Sub Find_Data_Set
Dim Number As String
Dim myprompt As String
Dim TBLGapApprovedCostNew As ADODB.Recordset
Set TBLGapApprovedCostNew = New ADODB.Recordset
Const myTitle = "Enter Application Number"
myprompt = "Please enter Project's Application Number"
Number = InputBox(myprompt, myTitle)
With TBLGapApprovedCostNew
.source = "SELECT TBLGapApprovedCosts.*" & _
"From TBLGapApprovedCosts" & _
"Where & TBLGapApprovedCosts.Application_Number= '" & Number & "'"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open TBLGapApprovedCosts:=adCmdTable
End With
End Sub
Sub createXML()
'use the ExportXML method to create a source XML data file
Application.ExportXML ObjectType: acExportTable , _
DataSource:="TBLGapApprovedCostNew", _
DataTarget:="C:\Database\Test\XML Tranformation Files\NewCarryover.xml"
End Sub
Sub TransformXML()
'use the transformXML method to create a new XML data file
Application.TransformXML DataSource:="C:\ Database\Test\XML Tranformation Files\NewCarryover.xml", _
TransformSource:="C:\Database\Test\XML Tranformation Files\TransformTBLGapApprovedCost7.xsl", _
OutputTarget:="C:\Database\Test\XML Tranformation Files\CarryoverX.xml", _
WellFormedXMLOutput:=False
End Sub
DoCmd.DeleteObject acTable, "TBLGapApprovedCostNew"
MsgBox "Please load the CarryoverXML Tranformation File"
End Function
