InfoPath Dev

All,

I am a new to InforPath and code behind it and I need a little help.  I have an InfoPath 2003 for with a repeating table with 4 columns.  The first field is the employee ID (Prefix).  The script I have takes this ID, through an OnAfterChange event, and finds the First name, Last name and unit number from active directory and populates the appropriate fields in the table.  The script works great for the first row but does not work on the following rows.  What can I do to make this script execute for each row?  Below is the code used thats works for the first row but not the following rows. 

Sub msoxd_my_Prefix_OnAfterChange(eventObj)
 
    empid = XDocument.DOM.selectSingleNode("//my:Prefix").Text
    On Error Resume Next
    Const ADS_SCOPE_SUBTREE = 2
 
    Set oRootDSE = GetObject("LDAP://rootDSE")
    Set oConnection = CreateObject("ADODB.Connection")
    Set oCommand =   CreateObject("ADODB.Command")
    oConnection.Provider = "ADsDSOObject"
    oConnection.Open "Active Directory Provider"
    Set oCommand.ActiveConnection = oConnection
 
    oCommand.CommandText = "<LDAP://" & oRootDSE.Get("defaultNamingContext") & _
         ">;(&(objectCategory=User)(sAMAccountName=" & empid & "));givenname, sn, & _
         department;subtree"
        
    Set oRecordSet = oCommand.Execute
    XDocument.DOM.selectSingleNode("//my:FirstName").Text = oRecordSet.fields & _
          ("givenname")
    XDocument.DOM.selectSingleNode("//my:LastName").Text = oRecordSet.fields("sn")
    XDocument.DOM.selectSingleNode("//my:UnitCode").Text = left(oRecordSet.fields & _
          ("department"), 5)
 
    oConnection.Close
    Set oRecordSet = Nothing
    Set oCommand = Nothing
    Set oConnection = Nothing
    Set oRootDSE = Nothing
 
    If eventObj.IsUndoRedo Then
	' An undo or redo operation has occurred and the DOM is read-only.
           Exit Sub
    End If
 
End Sub

What you want to do is easy enough.  You'll just need to use a loop.  I or someone else here can show you how do to that, but before that, could you describe your data source with a bit more detail? i.e., could you give the full paths from the top node down to your my:Prefix, my:FirstName, my:LastName and my:UnitCode nodes?

I think this is what you are looking for...

<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:d="http://schemas.microsoft.com/office/infopath/2003/ado/dataFields" xmlns:s0="http://microsoft.com/webservices/SharePointPortalServer/UserProfileService" xmlns:s1="http://microsoft.com/wsdl/types/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:http="http://schemas.xmlsoap.org/wsdl/http/" xmlns:ns1="http://schemas.xmlsoap.org/wsdl/" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-06-01T21:36:18" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003">
 <my:Global>
     <my:IsSubmitted>false</my:IsSubmitted>
     <my:FileName></my:FileName>
     <my:RequestDate xsi:nil="true"></my:RequestDate>
 </my:Global>
 <my:RequestorInfo>
  <my:Requestor></my:Requestor>
  <my:Phone></my:Phone>
  <my:Unit></my:Unit>
  <my:Email></my:Email>
  <my:Title></my:Title>
  <my:ReqLocation></my:ReqLocation>
  <my:MgmtCode></my:MgmtCode>
  <my:CoSA></my:CoSA>
  <my:Manager></my:Manager>
 </my:RequestorInfo>
 <my:Data>
  <my:Text></my:Text>
  <my:RichText></my:RichText>
  <my:DropDown></my:DropDown>
  <my:List></my:List>
  <my:DatePicker xsi:nil="true"></my:DatePicker>
  <my:ShowSection>true</my:ShowSection>
  <my:StartDate></my:StartDate>
  <my:DSAListing></my:DSAListing>
  <my:ClassType>1</my:ClassType>
  <my:ClassLocation></my:ClassLocation>
  <my:Room></my:Room>
  <my:ClassCode></my:ClassCode>
  <my:AllianceType></my:AllianceType>
  <my:AlliancePartner>false</my:AlliancePartner>
  <my:StudentList>
   <my:Prefix></my:Prefix>
   <my:FirstName></my:FirstName>
   <my:LastName></my:LastName>
   <my:UnitCode></my:UnitCode>
   <my:EmpType></my:EmpType>
  </my:StudentList>
 </my:Data>
</my:myFields>

Yes, thank you.  I just noticed that your code is in an OnAfterChange event handler, so you don't need a loop.

Please try the following:

Replace

    empid = XDocument.DOM.selectSingleNode("//my:Prefix").Text

with

    empid = eventObj.Site.text

And replace

    XDocument.DOM.selectSingleNode("//my:FirstName").Text = oRecordSet.fields & _
          ("givenname")
    XDocument.DOM.selectSingleNode("//my:LastName").Text = oRecordSet.fields("sn")
    XDocument.DOM.selectSingleNode("//my:UnitCode").Text = left(oRecordSet.fields & _
          ("department"), 5)

With

    eventObj.Site.selectSingleNode("../my:FirstName").Text = oRecordSet.fields & _
          ("givenname")
    eventObj.Site.selectSingleNode("../my:LastName").Text = oRecordSet.fields("sn")
    eventObj.Site.selectSingleNode("../my:UnitCode").Text = left(oRecordSet.fields & _
          ("department"), 5)

 That should do what you want it to do.

Just as a comment, the auto-generated part:

    If eventObj.IsUndoRedo Then
' An undo or redo operation has occurred and the DOM is read-only.
           Exit Sub
    End If
 
should really be at the top of the subroutine, not at the bottom.

Worked great!!

Thank you so much!!!

Jimmy,

Now that my AD query is working in the repeating table, I want to be able to pull some data out of the table.  I have looked at so many entries on this, it just gets confusing. 

What I would like to do is iterate through the repeating table, pulling each row out as assigning them to a single value to pass to a custom email message. 

ie. StudentName = LastName + ", " FirstName + " -- " ID

The code I have looks like this but I just cant get it to work past the first entry in the table.  It does know that there are two entries in the table though so thats helpful.

dim firstname, lastname, unit
Dim objXMLNodes, oItem
set objXMLNodes = XDocument.DOM.selectNodes("//my:StudentList") 

If objXMLNodes.length > 0 Then
 For Each oItem In objXMLNodes
    firstname = oItem.selectSingleNode("//my:FirstName").text
    lastname = oItem.selectSingleNode("//my:LastName").text
    unit = oItem.selectSingleNode("//my:UnitCode").text
    emailmessage = emailmessage + "Student -- " + lastname + ", " & _
       + firstname + " -- " + unit + chr(10)
  next
End If

//my:FirstName, //my:LastName, etc. are absolute paths that resolve to the first node in the entire data source with that name.  Please try replacing

    firstname = oItem.selectSingleNode("//my:FirstName").text
    lastname = oItem.selectSingleNode("//my:LastName").text
    unit = oItem.selectSingleNode("//my:UnitCode").text

with

    firstname = oItem.selectSingleNode("my:FirstName").text
    lastname = oItem.selectSingleNode("my:LastName").text
    unit = oItem.selectSingleNode("my:UnitCode").text

That did it.  Thanks again!