Select from secondary data source - Object variable not set

NoCanDo — Tue, 19 Jun 2007 13:04:06 GMT

Hi I'm back again!

What I'm trying to do overall is select the lowest number (Text not integer) from a secondary source table where two other parameters are 'true' and put that value in the Main data table. Example of the secondary data source is below:

Available table (all fields are text)
Number Site Allocated
300397 Sleaford ALLOCATED
300398 Sleaford OPEN
300399 Sleaford OPEN
300400 Fenton ALLOCATED
300401 Fenton ALLOCATEDQ
300402 Fenton OPEN
300403 Fenton OPEN
300404 Fenton OPEN

At present, What I believe I am asking the code to do is to select the value from field 'Number' in the Available table where the field Allocated='ALLOCATEDQ' in the Available table and Site='Fenton' in the Available table. (In effect, I want it to put 300401 into Non-Standard_Table/@AllocatedNumber). (The next step is I want to be able to select the lowest number ie 300402 based on 'OPEN' and 'Fenton'.)

What happens is InfoPath returns: "Object variable not set".

I cannot get past this aspect. The problem occurs on the line:

strNode = Xdocument.dom.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Available_Table/Number[@Allocated='ALLOCATEDQ' and @Site='Fenton']")

All existing code associated with the button on the form is below.

I hope you can help as I've had a really really frustrating couple of days working on this.

Please help - the NoCanDo-er!

<namespacesDefinition>
XDocument.DOM.setProperty "SelectionNamespaces", "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:q=""http://schemas.microsoft.com/office/infopath/2003/ado/queryFields"" xmlns:d=""http://schemas.microsoft.com/office/infopath/2003/ado/dataFields"" xmlns:dfs=""http://schemas.microsoft.com/office/infopath/2003/dataFormSolution"" xmlns:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2006-02-09T12:56:16"" xmlns:xd=""http://schemas.microsoft.com/office/infopath/2003"""
'</namespacesDefinition>

Sub CTRL401_4_OnClick(eventObj)

' Code for 'Start Number Allocation Process'

'VARIABLES
'SITEID used to differentiate between sites without naming issues
Dim strSITE
Dim strSITEID
Dim strNEWNUM
Dim strNAME
Dim strEXISTS
Dim strNONSTD
Dim strNUMBER
strEXISTS=1

'INFOPATH READ INFO from Non-Standard table
strSITE = Xdocument.dom.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Non-Standard_Table/@SiteName").text
strSITEID = Xdocument.dom.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Non-Standard_Table/@SiteID").text
strNAME = Xdocument.dom.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Non-Standard_Table/@GeneralName").text
strNONSTD = Xdocument.dom.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Non-Standard_Table/@NonStdReq").text

dim strVal
dim strNode

strVal = -1
strNode = Xdocument.dom.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Available_Table/Number[@Allocated='ALLOCATEDQ' and @Site='Fenton']")
if( [strNode != ""] )then
strNode = strVal.text 'try strNode = strVal
end if

sql = "UPDATE Available SET Allocated = ('"& strNAME & "') WHERE Number = ('"& strNEWNUM & "')"
set rs = db.Execute(sql)

'WRITE TO INFOPATH
Xdocument.dom.selectSingleNode("//dfs:myFields/dfs:dataFields/d:Non-Standard_Table/@AllocatedNumber").text=strNEWNUM

End sub