InfoPath Dev

Hi I'm new to blogs/forums and InfoPath!  I'm struggling with what is probably a very simple piece of vbscript on a button on an InfoPath form which selects a number from a seconday data source (Access database table).  The number it selects is the highest number in those which could be chosen, I need it to be the lowest!  If you could help I would most grateful.  The exsiting vbscript code is below, with the area where I believe the problem to be is now BOLD.

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 strALERT

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

'strALERT = XDocument.UI.Alert("Numbers for IPT equipment cannot be allocated from the Non-Standard range. Please use the correct form.")

' open database

set db = CreateObject("ADODB.Connection")

db.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\PaDa\InfoPath Test Area\Databases\Non-Standard.mdb")

'Non-Standard required?

If strNONSTD = "Yes" then

' use SQL to select information from Available table in Non-Standard.mdb

sql = "select * from Available"

set rs = db.Execute(sql)

' read resulting record set

do until rs.EOF

if rs("Allocated") = "OPEN" and rs("Site") = strSITE then

strNEWNUM = rs("Number")

end if

if rs("Allocated") = strSITE then

strEXISTS = rs("Number")

end if

rs.MoveNext

loop

'Set Existing Number Reservation

if strEXISTS <> 1 then

strNEWNUM = strEXISTS  'e.g. If strEXISTS is changed from 1 to 10 strNEWNUM will be 10

end if

' use SQL to insert new data into Available table in Non-Standard.mdb

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 if

End Sub

Hi NoCanDo, and welcome to the forums!

This code looks much too complicated to simply be choosing the largest number in a list of numbers, to do that, it should simply be a loop through all the numbers using "CurMax = Max( CurMax, CurCompareTo )". Could you explain in more detail what your code is doing? Also, we will need an idea of the schema and how things are related.  When pasting large amounts of code, it is a good idea to use a monotype font with less whitespace between lines so it is easier to read.

Hi Matt

Many thanks for the reply, I was beginning to think there was no-one out there!

What I'm trying to do is:

On the InfoPath form I have a button which has a script behind it which selects its Main connection namespace, variables and strings,  etc and then runs a VBScript part of which is if a condition =yes then use SQL to select all the records from the Available table in the Non-Standard database.  The process loops until the end of file (EOF) and will then select the item in the Number field of the Available table. 

Part of 'Available' Table
Number Site Allocated
300422 Fenton ALLOCATED
300423 Fenton ALLOCATED
300424 Fenton OPEN
300425 Fenton OPEN
300426 Fenton OPEN
300427 Fenton OPEN
300428 Fenton OPEN
300429 Fenton OPEN

When the script runs, it will select the last recordset in the table according to 'Allocated'= 'OPEN' and (in this case) 'Site'=strSITE=Fenton and will return the value in the 'Number' field which would be 300429 and this number would  then be used to populate other tables.  Following allocation of this number the 'Allocated' field associated with 300429 will be updated to 'ALLOCATED'.  The next time the script is run under the same conditions, it will select 300428, then 300427 etc.

What I want the scipt to do is select 300424, then 300425 etc in an ascending order, not a descending order.

If strNONSTD = "Yes" then
 ' use SQL to select information from Available table in Non-Standard.mdb
 sql = "select * from Available"  'select all fields in Available
 set rs = db.Execute(sql)   'do the SQL command
 ' read resulting record set
 do until rs.EOF
  if rs("Allocated") = "OPEN" and rs("Site") = strSITE then
   strNEWNUM = rs("Number")
  end if
   if rs("Allocated") = strSITE then
   strEXISTS = rs("Number")
  end if
  rs.MoveNext
 loop

Hope this all makes sense, as it's driving me a little crazy.  If you need any more info, I'll be only too pleased to supply.  Good luck.

Hi again,

I do believe you have incredibly over-complicated things. :)

If you have all of these rows in a secondary data source, then you can use a simple XPath query to get the number you are looking for.  Try this:

/dfs:myFields/dfs:dataFields/AvailableTable/Rows/row[ Allocated = 'OPEN' and Site = 'Fenton' ]/Number

Of course, you will have to edit this XPath to be syntactically correct with your schema, but hopefully you get the idea.  You can also replace 'Fenton' with a lookup to another node to make the query more dynamic.  This query assumes that the rows are in order, but you could add more complicated conditions using the previous-sibling axis to find the largest number in the list even if it wasn't sorted.  However, this would be slower.

Hope this helps!

Thanks Matt

We knew the code was 'overdone' but couldn't make it work any other way to select the highest number.

Will check out your idea and get back if there's an issue.

Many thanks - The NoCanDoer!

Good luck!

To select the highest, you could either reverse-sort the list and use the same XPath, or change the XPath to this:

/dfs:myFields/dfs:dataFields/AvailableTable/Rows/row[ Allocated = 'OPEN' and Site = 'Fenton' ][last()]/Number

Hi Matt

Have checked out your idea, implemented a 'query' in the script:

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

 and it returns an error:

Object required: 'XDocument.DOM.selectSingleNode(...)'

I'm obvuiously doing something wrong...

 Please help the unititiated :-(

Cheers

This could be happening for a number of reasons.  My first guess is that the query is not finding anything.  You need to check if it finds something, before trying to access .text.

var numberVal = -1; 
var numberNode = Xdocument.dom.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Available_Table/Rows/row[@Allocated = 'OPEN' and @Site = 'Fenton']/@Number");
if( numberNode != null )
     numberVal = numberNode.text;

So, there might be something wrong with your query.  Note that you never need the double-slash // at the root of your query.  You'll only ever have 1 root node.

PS - This is post 1337!!

Hi Matt

Really struggling with this all morning. Have implemented your script and needed to amend to the following to get it to 'run', but it still returns 'Object variable not set' relating to the line 'numberNode = ...'

dim numberVal
dim numberNode

numberVal = -1
numberNode = Xdocument.dom.selectSingleNode("/dfs:myFields/dfs:dataFields/d:Available_Table/Rows/row[@Allocated = 'OPEN' and @Site = 'Fenton']/@Number")
if( [numberNode = null] )then
     numberVal = numberNode.text
end if

The additional [] I've had to add were to get around errors reporting 'Expected ')' not found'

Don't know if I'm makingthings worse by doing it, but I'm one character at a time to resolve the issues.

Hope you can help more. Kind regards and many thanks for your help so far. One other question, what is Post 1337? and how do I find it?

I'm not sure what else to tell you.  The code looks right, you probably just have a problem with the XPath.  Or maybe it is the capitialization of the XDocument.DOM - have you tried stepping through with the debugging or putting in alert statements to watch the state of your variables and method calls?

Sorry, the 1337 references was refering to this: http://en.wikipedia.org/wiki/Leet Just a little geek-humor :)

I'm just in the process of downloading a debugger to try and find where the problems might lie.  A bit worried about which debugger to use?  Is the CSDebugger OK?

Anyway - many thanks for your help so far Matt - I wouldn't be this far down the road without you.  I'm confident I'll resolve the issue - by hook or by crook!  Watch this space ... you never know, NoCanDo might turn into TheResolver!