Thanks for the reply, but I get an error after changing the suggested line. The error is as follows
<error>
Object doesn't support this property or method: 'eventObj.Site'
</error>
In my current version, when the button is pushed, the text value is there in the MsgBox. Thats good. The problem is that when I create a new repeating section (in preview mode), and push the button in the second section, the value in the MsgBox is from the first section, not the newly created second section.
Ill post my code so you can take a look, it is quite possible that im totally doing it wrong.
<code>
' This file contains functions for data validation and form-level events.
' Because the functions are referenced in the form definition (.xsf) file,
' it is recommended that you do not modify the name of the function,
' or the name and number of arguments.
' The following line is created by Microsoft Office InfoPath to define the prefixes
' for all the known namespaces in the main XML data file.
' Any modification to the form files made outside of InfoPath
' will not be automatically updated.
'<namespacesDefinition>
XDocument.DOM.setProperty "SelectionNamespaces", "xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:o=""urn:schemas-microsoft-com:office:office"" xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet"" xmlns:html=""http://www.w3.org/TR/REC-html40"" xmlns:my=""http://schemas.microsoft.com/office/infopath/2003/myXSD/2009-02-23T17:05:31"" xmlns:xd=""http://schemas.microsoft.com/office/infopath/2003"" xmlns:xhtml=""http://www.w3.org/1999/xhtml"""
'</namespacesDefinition>
'=======
' The following function handler is created by Microsoft Office InfoPath.
' Do not modify the name of the function, or the name and number of arguments.
'=======
Sub CTRL61_9_OnClick(eventObj)
' Write your code here
'////// Set the environment //////
Dim objFSO, txtString, printername, i, intHPMonthlyVolume, intPrinterMonthlyVolume, intPrinterPageCount
Dim intMonthsInProduction, dateLeaseBegin, intPercentUsage, serialnumber, strText
i = 2
intMonthsInProduction = 0
intHPMonthlyVolume = ""
strText = ""
Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
'////// The lines below set the feilds in the document to update //////
Set PrinterMonthlyVolume = XDocument.DOM.SelectSingleNode("my:myFields/my:Multiple_Printer_Info/my:Multiple_Recomended_Monthly_Print_Volume")
Set PageCount = XDocument.DOM.SelectSingleNode("my:myFields/my:Multiple_Printer_Info/my:Multiple_Average_Monthly_Page_Count")
'////// The location of the SNMPGet application //////
snmpgetpath = "h:\snmpget\snmpget.exe "
printername = XDocument.DOM.SelectSingleNode("my:myFields/my:Multiple_Printer_Info/my:Multiple_Printer_Name").text
'printername = eventObj.Site.SelectSingleNode("my:myFields/my:Multiple_Printer_Info/my:Multiple_Printer_Name").text
'////// FOR TESTING //////
Msgbox(printername)
'////// END TESTING //////
'////// Below are the SNMP Strings which SNMPGet uses to gather the data from the printer //////
strSerialNumberString = ".1.3.6.1.2.1.43.5.1.1.17.1"
strModelNameString = ".1.3.6.1.2.1.25.3.2.1.3.1"
strPageCountString = ".1.3.6.1.4.1.11.2.3.9.4.2.1.1.16.1.9.0"
'////// This is the alternate SNMP String as not all the printers have the same MiB //////
strPageCountString2 = ".1.3.6.1.2.1.43.10.2.1.4.1.1"
'////// Now get the Serial Number from the printer using SNMPGet, PrinterName and Serial Number string /////
Set CommandString = objShell.Exec(snmpgetpath + printername + strSerialNumberString)
Do While Not CommandString.StdOut.AtEndOfStream
'////// Using STDOUT to read the output //////
strText = CommandString.StdOut.ReadLine()
Loop
'////// If unable to locate the printer on the network, the string will be blank, exit the sub. //////
If strText = "" Then
MsgBox("Unable to locate printer")
Exit Sub
End If
'////// Since the Serial Number has been pulled, parse the data to get rid of the garbage //////
intTemp = InstrRev(strText, ":")
strText = Trim(Mid(strText,intTemp + 2, Len(strText)))
serialnumber = strText
'////// Now that we have the Serial Number //////
'////// Load the excel file into memory //////
'////// Create the excel object //////
Set objExcel = CreateObject("Excel.Application")
'////// Below is the locaiton of the printer excel spreadsheet //////
strPrinterFile = "N:\{folder on network drive}\Printers - Scripting Only.xls"
'////// Open the excel spreadsheet //////
objExcel.WorkBooks.Open strPrinterFile, false, true
'////// Get the first worksheet in the workbook //////
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'////// Pull all the data from the worksheet into an array to be searched later //////
arrData = objExcel.Cells(5,6).CurrentRegion.Value
'////// Close out the excel object and program //////
objExcel.ActiveWorkbook.Close
objExcel.Application.Quit
'////// Search the 5th column for the Serial Number //////
For i = 1 to Ubound(arrData)
If Instr(Trim(arrData(i,5)), Trim(serialnumber.text)) > 0 Then
'////// When found, the Lease Expiry date will be in the 6th column //////
leaseexpiry.text = arrData(i,6)
'////// The HP Suggested Monthly Print Volume will be in the 7th column //////
intHPMonthlyVolume = arrData(i,7)
End If
Next
'////// This is the check to see if the printer information was found in the spreadsheet //////
'////// If the serial number was not found, or the HP Suggested Monthly Print Volume //////
'////// was not specified in the spreadsheet, then the intHPMonthlyVolume will be null //////
'////// and the script will exit. //////
If intHPMonthlyVolume = "" Then
MsgBox("Unable to locate all the printer information in the spreadsheet")
Exit Sub
End If
'////// The lease on printers is 5 years, or 60 months //////
'////// Subtract 60 months from the date the lease expires on to get the install date //////
dateLeaseBegin = DateAdd("m", -60, leaseexpiry.text)
'////// Now that we have the install date, we can get the number of months the //////
'////// printer has been in production //////
intMonthsInProduction = DateDiff("m", dateLeaseBegin, Date)
'////// Get the printer page count from reading the SNMP String //////
Set CommandString = objShell.Exec(snmpgetpath + printername + strPageCountString)
Do While Not CommandString.StdOut.AtEndOfStream
'////// Using STDOUT to read the output //////
strText = CommandString.StdOut.ReadLine()
Loop
'////// Since the Page Count has been pulled, parse the data to get rid of the garbage //////
intTemp = InstrRev(strText, ":")
strText = Trim(Mid(strText,intTemp + 2, Len(strText)))
'////// If the page count number is larger than 10,000,000 then its incorrect, use the //////
'////// secondary string to get the page count. Some printer MiBs have a serial //////
'////// number or other printer information in the string so the alternate must be used//////
If Len(strText > 8 ) Then
Set CommandString = objShell.Exec(snmpgetpath + printername + strPageCountString2)
Do While Not CommandString.StdOut.AtEndOfStream
'////// Using STDOUT to read the output //////
strText = CommandString.StdOut.ReadLine()
Loop
End If
'////// Since the Page Count has been pulled, parse the data to get rid of the garbage //////
intTemp = InstrRev(strText, ":")
strText = Trim(Mid(strText,intTemp + 2, Len(strText)))
intPrinterPageCount = strText
'////// Get the printers actual pages per month //////
intPrinterMonthlyVolume = intPrinterPageCount / intMonthsInProduction
PageCount.text = Round(intPrinterMonthlyVolume,0)
'////// Get the printers actual percentage of HP's Maximum Monthly Volume //////
intPercentUsage = ( intPrinterMonthlyVolume / intHPMonthlyVolume ) * 100
'////// Post the acutal usage to the form //////
PrinterMonthlyVolume.text = Round(intPercentUsage,1)
End Sub