InfoPath Dev

Hello All,

Is it possible to execute a union query from infopath? Basically I need to display a table of questions pulled from two separate tables. Each of those tables have to right join another two tables to filter the questions displayed to a particular work center. Here is what the code looks like:

Sub BuildCheckList()
Dim i
Dim flw
Dim fw

'Set the filters and query the secondary data source.
flw = XDocument.DOM.SelectSingleNode("//my:Filters/my:fltrWC").text

fw = "SELECT CORE.NUMBER, CORE.QTYPE, CORE.TYPE, CORE.QUESTION FROM CORE LEFT JOIN WCQUEST ON CORE.NUMBER = WCQUEST.QNUMBER WHERE (((WCQUEST.WORKCTR)='" & flw & "')) OR (((WCQUEST.WORKCTR)='ALL')) UNION ALL SELECT SUP.NUMBER, SUP.QTYPE, SUP.TYPE, SUP.QUESTION FROM SUP LEFT JOIN SUPWC ON SUP.NUMBER = SUPWC.NUMBER WHERE (((SUPWC.WORKCTR)='" & flw & "')) OR (((SUPWC.WORKCTR)='ALL')) Order By NUMBER "

XDocument.DataObjects("CCORE").QueryAdapter.Command = fw
XDocument.DataObjects("CCORE").Query()

'retrieve the secondary data tables.
Set SDS = XDocument.DataObjects("CCORE").DOM.getElementsByTagName("d:CORE")

'Clone Row before Clearing the PDS table.
Set PDS = XDocument.DOM.selectSingleNode("//my:CheckLists/my:CheckList")
Set Seed = PDS.cloneNode(true)

'Delete all rows from PDS table.
Set PDS = XDocument.DOM.selectNodes("//my:CheckLists/my:CheckList")
For i = 0 to (PDS.length - 1)
   Set row = PDS.item(i)
   row.parentNode.removeChild(row)
Next

'Populate the PDS table with the new data.
For i = 0 to (SDS.length - 1)
	Set parent = XDocument.DOM.selectSingleNode("//my:CheckLists")
	Set PDS = parent.selectSingleNode("./my:CheckList[last()]")
	Seed.selectSingleNode("my:Number").text = SDS.Item(i).getAttribute("NUMBER")
	Seed.selectSingleNode("my:QType").text = SDS.Item(i).getAttribute("QTYPE")
	Seed.selectSingleNode("my:Type").text = SDS.Item(i).getAttribute("TYPE")
	Seed.selectSingleNode("my:Question").text = SDS.Item(i).getAttribute("QUESTION")
	If i = 0 Then
		parent.insertBefore Seed, PDS
	Else
		parent.insertBefore Seed, PDS.nextSibling
	End If
Next

If Err Then
	XDocument.UI.Alert "Error " & Err.number & ": " & Err.Description
End If
End Sub

As always your help is immeasurably appreciated.

Hi Darrell,

I think the easiest way to do this would be to create a view in SQL and then connect to that view from InfoPath. This also seems like it would be more performant, but I can't vouch for that.
Hope this helps!

Hey Matt,

You know its funny that would seem obvious but I just didn't think of it before you said something. Basically, all I have to do is create the union query and then create a select query based on that union query. I think that is what you are saying, right? Simple enough, and much much more efficient. Thanks again.