The code works... sort of... but seems tb acting strangely sometimes.

To wit, I'll get the occasional "Compile Error: Sub or Function not defined"
when I try to execute it a second time.   When that happens, if I wait a few 
minutes and try again it will work.

Also, the timing doesn't feel right...  things don't always seem tb getting
updated in the expected sequence/timeframe.

Bottom Line Question:  Is there some sort of Synch/Asynch parm/prop that I
should be specifying to the SOAP client so that my code pauses until SOAP 
delivers the results of the web service function call?


Here's the code in question (MS Access VBA):
-----------------------------------------------------------------------------
Public Function CallBloomberg_GetSector(ByVal theBatchLimit As Long) As Boolean
' byVal theUserID as string, byVal thePassword as string,  ByVal theServerName
As String) as boolean
4000 debugStackPush mModuleName & ": CallBloomberg_GetSector"
4001 On Error GoTo CallBloomberg_GetSector_err

' PURPOSE: To retrieve sector information from our web service wrapper for the '
'          Bloomberg API for a group of securities
' ACCEPTS: - Maximum number of CUSIPs to process.
'          - UserID needed to access the service.  
'          - Password needed to access the service. 
'          - Name of the server we are hitting.     
' RETURNS: True/False depending on success
' UPDATES: A cache table containing extracted information

4003 Dim myRS               As DAO.Recordset

     Dim mySC               As SoapClient
     Dim myXmlDoc           As MSXML2.DOMDocument60
     Dim myNL               As MSXML2.IXMLDOMNodeList
     Dim myNode             As MSXML2.IXMLDOMNode
     
     Dim S                  As String
     Dim mySecurityList     As String
     Dim curInputKey        As String
     Dim curCusip           As String
     Dim curSecurityName    As String
     Dim curCusipReal       As String
     Dim curSector          As String
     Dim curSectorNumber    As String
     Dim curSubgroup        As String
     Dim curSubgroupNumber  As String
     
     Dim myResultXML        As String  'Service's reply to our request
     Dim myResultParsed     As String
      
     Const myFieldList      As String =
"ID_CUSIP|ID_CUSIP_REAL|NAME|INDUSTRY_SECTOR|INDUSTRY_SECTOR_NUM|INDUSTRY_SUBGROUP|INDUSTRY_SUBGROUP_NUM"
 
     Const theUserID        As String = "xxx"
     Const thePassword      As String = "xxx"
     Const theServerName    As String = "xxx"

    ' ------------------------
    ' Create the work table

4010 DoCmd.Hourglass True
4011 WorkTable_Create "ttblBloombergCusipSector", "zmtblBloombergCusipSector"
4019 Set myRS = CurrentDb.OpenRecordset("ttblBloombergCusipSector",
dbOpenDynaset, dbAppendOnly)

    ' ------------------------
    ' Create the SOAP client that will convey our request to the web service
    
4020 Set mySC = New SoapClient
4021 S = "http://" & theServerName & "/" & "xxx.asmx?wsdl"
4029 mySC.mssoapinit S

    ' ------------------------
    ' Get a list of CUSIPs that still lack Bloomberg Sector info

4030 mySecurityList = pipeDelimCusipsNeed_Sector(theBatchLimit)

    ' ------------------------
    ' Load the XML into a document, parse out the sector info,
    ' and write it to a work table
    ' NB: BBH and IMU files contain only 8-digit CUSIPS whereas
    '     BB returns a check digit as well.   We need to strip off
    '     the check digit...

4040 myResultXML = mySC.CallBloomberg(mySecurityList, myFieldList, theUserID,
thePassword)

4050 Set myXmlDoc = New MSXML2.DOMDocument60
4059 myXmlDoc.loadXML myResultXML
 
4060 Set myNL = myXmlDoc.selectNodes("//SECURITY")
4070 For Each myNode In myNL
4071    curInputKey = myNode.selectSingleNode("NAME").Text
4072    curCusip = myNode.selectSingleNode("API_ID_CUSIP").Text
1073    If Len(curCusip & "") > 7 Then
4074       curSecurityName = myNode.selectSingleNode("API_NAME").Text
4075       curCusipReal = myNode.selectSingleNode("API_ID_CUSIP_REAL").Text
4079       curSector = myNode.selectSingleNode("API_INDUSTRY_SECTOR").Text
4080       curSectorNumber =
myNode.selectSingleNode("API_INDUSTRY_SECTOR_NUM").Text
4081       curSubgroup = myNode.selectSingleNode("API_INDUSTRY_SUBGROUP").Text
4082       curSubgroupNumber =
myNode.selectSingleNode("API_INDUSTRY_SUBGROUP_NUM").Text
4083       With myRS
4084          .AddNew
4085          !BloombergKey = curInputKey
4089          !CUSIP = Left$(curCusip, 8)  'Strip off the check digit
4090          !CusipReal = curCusipReal
4091          !SecurityName = curSecurityName
4092          !Sector = curSector
4093          !SectorNumber = curSectorNumber
4094          !Subgroup = curSubgroup
4095          !subgroupnumber = curSubgroupNumber
4096          .Update
4099       End With
4199    End If
4299 Next myNode

    ' ------------------------
    ' Join the work table to the permanent cache
    ' and add recs from work table that don't exist
    ' in cache
    
4300 CurrentDb.Execute "qryBloombergAppend_Sector", dbFailOnError

4990 DoCmd.Hourglass False
4999 CallBloomberg_GetSector = True

CallBloomberg_GetSector_xit:
 DebugStackPop
 On Error Resume Next
 myRS.Close
 Set myRS = Nothing
 Set mySC = Nothing
 Set myNode = Nothing
 Set myNL = Nothing
 Set myXmlDoc = Nothing
 Exit Function
 
CallBloomberg_GetSector_err:
 BugAlert True, "curInputKey='" & curInputKey & "," & vbCrLf & "ResultXML='" &
myResultXML & "'."
 Resume CallBloomberg_GetSector_xit
End Function
-----------------------------------------------------------------------------
-- 
PeteCresswell