I've written a macro for importing a series of invoices one at a time. It works for the first invoice, but in the next iteration I get "error -2147417848. Automation error. The object invoked has disconnected from its clients." This occurs on the line:
vwOrder.Insert
I figure I must not be initializing the view correctly for starting to add a second new record, but I don't know the correct methods I should call to do that.
What makes the error worse is it doesn't get trapped by my error handler, the macro just stops responding and have to kill the process.
vwOrder.Insert
I figure I must not be initializing the view correctly for starting to add a second new record, but I don't know the correct methods I should call to do that.
What makes the error worse is it doesn't get trapped by my error handler, the macro just stops responding and have to kill the process.
Code:
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
' ?????????? Do I need to call vwOrder.RecordGenerate ?????????????
'vwOrder.RecordGenerate False
' When I had this enabled I used to get error
' "Order. Record already exists." on the first iteration.
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
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
[COLOR=green]
'Error is here, on the second iteration[/color]
[COLOR=red]
vwOrder.Insert[/color]
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
Errors.Clear
End If
End If
End Sub
Private Sub InsertFieldValue(viewField As AccpacViewField, StringValue As String)
'Purpose: Inserts a value in an AccPac field if the value is not a zero-length string
If Len(StringValue) > 0 Then
viewField.PutWithoutVerification StringValue
End If
End Sub