Record has been modified by another program

[JoeAtWork]

Hello AccPac Gurus!

I've written a macro for importing invoices from an XML file. During testing, out of about 50 invoices only one failed to import. The log file showed this in the AccPac Errors collection:

"Options. Record has been modified by another program."

They did this on a test system - no other users or programs should have been in the system.

I imported the same file without getting the error. Because of that, and the fact that all other invoices imported, I'm looking at it as some kind of fluke, and thinking the way to handle it is to try to import the invoice a couple more times before giving up (possibly putting a little pause in between).

Any ideas what might cause the error, and the best way to handle it?

Here's my code (sorry it's so bloody long, but it's hard to limit it in AccPac, isn't it)

Private Sub ImportInvoices()
'This subroutine imports the invoices to AccPac.  The code is adapted from a recorded macro
'that followed these manual steps:
'1. Create the Order
'2. Post it - had to override credit limit check by providing the appropriate username password
'3. Pressed "Ship All" so that the "Create Invoice" checkbox would appear
'4. On the Totals tab, entered the Invoice Number, left the Shipment Number as it was,
'   i.e. "*** NEW ***"
'5. Post it

On Error GoTo ACCPACErrorHandler

Dim objInvoice As clsInvoice
Dim objDetail As clsInvoiceDetail
Dim bolProcessCheckCredit As Boolean

Dim temp As Boolean
Dim vwOrder As AccpacCOMAPI.AccpacView
Dim vwOrderFields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0520", vwOrder
Set vwOrderFields = vwOrder.Fields

Dim vwDetails As AccpacCOMAPI.AccpacView
Dim vwDetailsFields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0500", vwDetails
Set vwDetailsFields = vwDetails.Fields

Dim OEORD1detail2 As AccpacCOMAPI.AccpacView
Dim OEORD1detail2Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0740", OEORD1detail2
Set OEORD1detail2Fields = OEORD1detail2.Fields

Dim OEORD1detail3 As AccpacCOMAPI.AccpacView
Dim OEORD1detail3Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0180", OEORD1detail3
Set OEORD1detail3Fields = OEORD1detail3.Fields

Dim OEORD1detail4 As AccpacCOMAPI.AccpacView
Dim OEORD1detail4Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0680", OEORD1detail4
Set OEORD1detail4Fields = OEORD1detail4.Fields

Dim OEORD1detail5 As AccpacCOMAPI.AccpacView
Dim OEORD1detail5Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0526", OEORD1detail5
Set OEORD1detail5Fields = OEORD1detail5.Fields

Dim OEORD1detail6 As AccpacCOMAPI.AccpacView
Dim OEORD1detail6Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0522", OEORD1detail6
Set OEORD1detail6Fields = OEORD1detail6.Fields

Dim OEORD1detail7 As AccpacCOMAPI.AccpacView
Dim OEORD1detail7Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0501", OEORD1detail7
Set OEORD1detail7Fields = OEORD1detail7.Fields

Dim OEORD1detail8 As AccpacCOMAPI.AccpacView
Dim OEORD1detail8Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0502", OEORD1detail8
Set OEORD1detail8Fields = OEORD1detail8.Fields

Dim OEORD1detail9 As AccpacCOMAPI.AccpacView
Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0504", OEORD1detail9
Set OEORD1detail9Fields = OEORD1detail9.Fields

Dim vwDetails0 As AccpacCOMAPI.AccpacView
Dim vwDetails0Fields As AccpacCOMAPI.AccpacViewFields
m_DBLinkWrite.OpenView "OE0503", vwDetails0
Set vwDetails0Fields = vwDetails0.Fields

vwOrder.Compose Array(vwDetails, OEORD1detail4, OEORD1detail3, OEORD1detail2, OEORD1detail5, OEORD1detail6)

vwDetails.Compose Array(vwOrder, OEORD1detail7, vwDetails0, OEORD1detail8)

OEORD1detail2.Compose Array(vwOrder)

OEORD1detail3.Compose Array(vwOrder, vwDetails)

OEORD1detail4.Compose Array(vwOrder, vwDetails)

OEORD1detail5.Compose Array(vwOrder)

OEORD1detail6.Compose Array(vwOrder)

OEORD1detail7.Compose Array(vwDetails)

OEORD1detail8.Compose Array(vwDetails, OEORD1detail9)

OEORD1detail9.Compose Array(OEORD1detail8)

vwDetails0.Compose Array(vwDetails)


'Loop through all invoices for the current customer
For Each objInvoice In m_Customer.Invoices

    m_lngInvoicesCount = m_lngInvoicesCount + 1
    
    'Initialize a new order
    vwOrder.Order = 0
    vwOrder.Fields("ORDUNIQ") = 0
    vwOrder.Init
    
    InsertFieldValue vwOrderFields("ORDNUMBER"), objInvoice.OrderNo     ' Order Number
    
    vwOrderFields("CUSTOMER").Value = m_Customer.CustNo                 ' Customer Number
    
    vwOrderFields("PROCESSCMD").PutWithoutVerification ("1")            ' Process Command
    
    vwOrderFields("SHIPTO").Value = m_Customer.ShipToNo                 ' Ship-To Location Code
    vwOrderFields("GOFCALCTAX").PutWithoutVerification ("1")            ' Perform Forced Tax Calculation
    InsertFieldValue vwOrderFields("PONUMBER"), objInvoice.PONumber     ' Purchase Order Number
    
    'Enter Location - if not supplied, uses a default
    InsertFieldValue vwOrderFields("LOCATION"), objInvoice.Location
    
    'Loop through invoice details
    For Each objDetail In objInvoice.Details
        vwDetails.RecordGenerate False
        
        vwDetailsFields("ITEM").Value = objDetail.Item                  ' Item
        
        vwDetailsFields("QTYORDERED").Value = objDetail.QtyOrdered      ' Quantity Ordered
        vwDetailsFields("PRIUNTPRC").Value = objDetail.UnitPrice
        
        'Only override the item description if one was provided in the XML
        If Len(objDetail.Description) > 0 Then
            vwDetailsFields("DESC").Value = objDetail.Description
        End If
        
        vwDetails.Insert
    Next
    '
        
    vwOrderFields("GOCALCTAX").PutWithoutVerification ("1")             ' Perform Tax Calculation
    InsertFieldValue vwOrderFields("REFERENCE"), objInvoice.Reference   ' Order Reference
    InsertFieldValue vwOrderFields("DESC"), objInvoice.Description      ' Order Description
    
    vwOrderFields("APPROVEBY").Value = m_OverrideUserName               ' Authorizing User ID
    ' User Can Approve Credit Lift
    vwOrderFields("GOAPPROSEC").Value = "1"
    ' Authorizing User Password
    vwOrderFields("APPPASSWRD").PutWithoutVerification (m_OverrideUserPassword)
    
    vwOrder.Process     'First place where a Process command is required
    
    vwOrderFields("GOCHKCRDT").Value = "1"                          ' Perform Credit Limit Check
    bolProcessCheckCredit = True
    vwOrder.Process    'Causes automation error, but seems to be necessary
    bolProcessCheckCredit = False
    
    vwOrderFields("GOSHIPALL").PutWithoutVerification ("1")         ' Perform Ship All
    
    vwOrder.Process
    
    vwOrderFields("INVPRODUCE").Value = "1"                         ' Invoice Will Be Produced
    
    vwDetails.Read
    vwOrderFields("GOCALCTAX").PutWithoutVerification ("1")         ' Perform Tax Calculation
    vwOrder.Process
    
    vwOrderFields("INVNUMBER").Value = objInvoice.InvoiceNo         ' Invoice Number
    
    'Date fields need to be converted from their XML format of "yyyy-mm-dd" to make sure
    'month/day are interpreted correctly
    If IsDate(objInvoice.OrderDate) Then
        vwOrderFields("ORDDATE").Value = ConvertXMLDate(objInvoice.OrderDate)
    End If
    If IsDate(objInvoice.InvoiceDate) Then
        vwOrderFields("INVDATE").Value = ConvertXMLDate(objInvoice.InvoiceDate)
    End If

    vwOrder.Insert
    
    m_lngInvoicesAdded = m_lngInvoicesAdded + 1
    
    WriteToLog Now, "SUCCEED", "Import Invoice", "Invoice was imported", _
     m_Customer.CustNo, m_Customer.ShipToNo, objInvoice.InvoiceNo
     
NextInvoice:
     
Next


Exit Sub

ACCPACErrorHandler:
    Dim strError As String
    Dim lCount As Long
    Dim lIndex As Long
    Dim strInvoiceNo As String
    
    'If error occurred when processing the "Check customer credit limit", just clear the Errors
    'collection and proceed.  Testing has shown that an "automation" error always appears but it
    'does not seem to be valid, and the invoice still gets created if we resume the code.
    If bolProcessCheckCredit Then
        Errors.Clear
        Resume Next
    ElseIf Errors Is Nothing Then
        strError = "Error # " & Err.Number & ": " & Err.Description
        If Not (objInvoice Is Nothing) Then
           strInvoiceNo = objInvoice.InvoiceNo
        End If
         WriteToLog Now, "FAIL", "Import Invoice", strError, m_Customer.CustNo, _
          m_Customer.ShipToNo, strInvoiceNo
    Else
        lCount = Errors.Count
        
        If Not (objInvoice Is Nothing) Then
           strInvoiceNo = objInvoice.InvoiceNo
        End If

        If lCount = 0 Then
            strError = "Error # " & Err.Number & ": " & Err.Description
            WriteToLog Now, "FAIL", "Import Invoice", strError, m_Customer.CustNo, _
             m_Customer.ShipToNo, strInvoiceNo
        Else
            strError = "AccPac errors:"
            
            For lIndex = 0 To lCount - 1
                strError = strError & " " & (lIndex + 1) & ". " & Errors.Item(lIndex) & ";"
            Next
            
            WriteToLog Now, "FAIL", "Import Invoice", strError, m_Customer.CustNo, _
             m_Customer.ShipToNo, strInvoiceNo
            
            'Determine if error was that the invoice number already existed
            If InStr(1, strError, "Invoice number") > 0 And InStr(1, strError, "already exists") > 0 Then
                m_lngDuplicateInvNos = m_lngDuplicateInvNos + 1
            End If
            
            Errors.Clear
        End If
    
    End If

    m_lngInvoicesErrors = m_lngInvoicesErrors + 1
    
    'Move on to the next invoice (there may be valid records after the one that caused the error)
    Resume NextInvoice
    
End Sub

 

[Jay Converse]

At what line do you get the error?