Hi all,
I am making an AR Receipt Entry import to import the receipt in bulk from an excel file. I follow the recorded macro to create it. And I see that the ARPOOP (in recorded macro is using ARRECMAC1detail4) set the APPLY value to "Y" on line 124.
Sub MainSub()
'
' Sage 300 Macro file: D:\Sage 300 Folder\PCI Trading\AR Receipt Entry 22092021.AVB
' Recorded at: Wed Sep 22 14:27:40 2021
'
On Error GoTo ACCPACErrorHandler
' TODO: To increase efficiency, comment out any unused DB links.
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
Dim temp As Boolean
Dim ARRECMAC1batch As AccpacCOMAPI.AccpacView
Dim ARRECMAC1batchFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0041", ARRECMAC1batch
Set ARRECMAC1batchFields = ARRECMAC1batch.Fields
Dim ARRECMAC1header As AccpacCOMAPI.AccpacView
Dim ARRECMAC1headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0042", ARRECMAC1header
Set ARRECMAC1headerFields = ARRECMAC1header.Fields
Dim ARRECMAC1detail1 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0044", ARRECMAC1detail1
Set ARRECMAC1detail1Fields = ARRECMAC1detail1.Fields
Dim ARRECMAC1detail2 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0045", ARRECMAC1detail2
Set ARRECMAC1detail2Fields = ARRECMAC1detail2.Fields
Dim ARRECMAC1detail3 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0043", ARRECMAC1detail3
Set ARRECMAC1detail3Fields = ARRECMAC1detail3.Fields
Dim ARRECMAC1detail4 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail4Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0061", ARRECMAC1detail4
Set ARRECMAC1detail4Fields = ARRECMAC1detail4.Fields
Dim ARRECMAC1detail5 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail5Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0406", ARRECMAC1detail5
Set ARRECMAC1detail5Fields = ARRECMAC1detail5.Fields
Dim ARRECMAC1detail6 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail6Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0170", ARRECMAC1detail6
Set ARRECMAC1detail6Fields = ARRECMAC1detail6.Fields
ARRECMAC1batch.Compose Array(ARRECMAC1header)
ARRECMAC1header.Compose Array(ARRECMAC1batch, ARRECMAC1detail3, ARRECMAC1detail1, ARRECMAC1detail5, ARRECMAC1detail6)
ARRECMAC1detail1.Compose Array(ARRECMAC1header, ARRECMAC1detail2, ARRECMAC1detail4)
ARRECMAC1detail2.Compose Array(ARRECMAC1detail1)
ARRECMAC1detail3.Compose Array(ARRECMAC1header)
ARRECMAC1detail4.Compose Array(ARRECMAC1batch, ARRECMAC1header, ARRECMAC1detail3, ARRECMAC1detail1, ARRECMAC1detail2)
ARRECMAC1detail5.Compose Array(ARRECMAC1header)
ARRECMAC1detail6.Compose Array(ARRECMAC1header)
Dim ARPAYMPOST2 As AccpacCOMAPI.AccpacView
Dim ARPAYMPOST2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0049", ARPAYMPOST2
Set ARPAYMPOST2Fields = ARPAYMPOST2.Fields
ARRECMAC1batch.RecordClear
ARRECMAC1batchFields("CODEPYMTYP").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1headerFields("CODEPYMTYP").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1detail3Fields("CODEPAYM").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1detail1Fields("CODEPAYM").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1detail2Fields("CODEPAYM").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1detail4Fields("PAYMTYPE").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1detail4.Cancel
ARRECMAC1batch.Browse "((CODEPYMTYP = ""CA"") AND ((BATCHSTAT = 1) OR (BATCHSTAT = 7)))", 1
ARRECMAC1batchFields("CODEPYMTYP").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1batchFields("CNTBTCH").PutWithoutVerification ("0") ' Batch Number
ARRECMAC1batch.RecordCreate 1
ARRECMAC1batchFields("PROCESSCMD").PutWithoutVerification ("2") ' Process Command
ARRECMAC1batch.Process
ARRECMAC1detail4.Cancel
temp = ARRECMAC1header.Exists
ARRECMAC1header.RecordCreate 2
ARRECMAC1detail4.Cancel
temp = ARRECMAC1header.Exists
ARRECMAC1headerFields("IDCUST").Value = "AB000002" ' Customer Number
ARRECMAC1detail1.Cancel
ARRECMAC1header.Process
ARRECMAC1detail4.Cancel
ARRECMAC1detail4Fields("PAYMTYPE").Value = "CA" ' Batch Type
ARRECMAC1detail4Fields("CNTBTCH").Value = "110" ' Batch Number
ARRECMAC1detail4Fields("CNTITEM").Value = "0" ' Entry Number
ARRECMAC1detail4Fields("IDCUST").Value = "AB000002" ' ID Customer
ARRECMAC1detail4.Process
ARRECMAC1detail4Fields("CNTITEM").PutWithoutVerification ("0") ' Entry Number
ARRECMAC1detail4Fields("CNTKEY").PutWithoutVerification ("-4") ' Count key
ARRECMAC1detail4.Read
ARRECMAC1detail4Fields("APPLY").Value = "Y" ' Apply
temp = ARRECMAC1detail4.Exists
temp = ARRECMAC1detail4.Exists
ARRECMAC1detail4.Update
ARRECMAC1detail4Fields("CNTITEM").PutWithoutVerification ("0") ' Entry Number
ARRECMAC1detail4Fields("CNTKEY").PutWithoutVerification ("-4") ' Count key
ARRECMAC1detail4.Read
ARRECMAC1header.Insert
ARRECMAC1detail4.Cancel
temp = ARRECMAC1header.Exists
ARRECMAC1detail4Fields("PAYMTYPE").Value = "CA" ' Batch Type
ARRECMAC1detail4Fields("CNTBTCH").Value = "110" ' Batch Number
ARRECMAC1detail4Fields("CNTITEM").Value = "1" ' Entry Number
ARRECMAC1detail4Fields("IDCUST").Value = "AB000002" ' ID Customer
ARRECMAC1detail4.Process
ARRECMAC1batch.Read
ARRECMAC1headerFields("CNTITEM").PutWithoutVerification ("0") ' Entry Number
ARRECMAC1header.RecordCreate 2
ARRECMAC1detail4.Cancel
temp = ARRECMAC1header.Exists
Exit Sub
ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
If Errors Is Nothing Then
MsgBox Err.Description
Else
lCount = Errors.Count
If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next
End If
End Sub
But in my macro code, whenever I set APPLY value at line 114, it have error
Dim date_remitten As Date
Dim date_bus As Date
Dim batch_no As String
Dim customer_code As String
Dim document_date As Date
Dim payment_code As String
Sub InsertARReceipt(customers As Object, bank As String)
'On Error GoTo ACCPACErrorHandler
UserForm1.errorNo = 0
' TODO: To increase efficiency, comment out any unused DB links.
Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)
Dim temp As Boolean
Dim ARRECMAC1batch As AccpacCOMAPI.AccpacView
Dim ARRECMAC1batchFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0041", ARRECMAC1batch
Set ARRECMAC1batchFields = ARRECMAC1batch.Fields
Dim ARRECMAC1header As AccpacCOMAPI.AccpacView
Dim ARRECMAC1headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0042", ARRECMAC1header
Set ARRECMAC1headerFields = ARRECMAC1header.Fields
Dim ARRECMAC1detail1 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0044", ARRECMAC1detail1
Set ARRECMAC1detail1Fields = ARRECMAC1detail1.Fields
Dim ARRECMAC1detail2 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0045", ARRECMAC1detail2
Set ARRECMAC1detail2Fields = ARRECMAC1detail2.Fields
Dim ARRECMAC1detail3 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0043", ARRECMAC1detail3
Set ARRECMAC1detail3Fields = ARRECMAC1detail3.Fields
Dim ARRECMAC1detail4 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail4Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0061", ARRECMAC1detail4
Set ARRECMAC1detail4Fields = ARRECMAC1detail4.Fields
Dim ARRECMAC1detail5 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail5Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0406", ARRECMAC1detail5
Set ARRECMAC1detail5Fields = ARRECMAC1detail5.Fields
Dim ARRECMAC1detail6 As AccpacCOMAPI.AccpacView
Dim ARRECMAC1detail6Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0170", ARRECMAC1detail6
Set ARRECMAC1detail6Fields = ARRECMAC1detail6.Fields
ARRECMAC1batch.Compose Array(ARRECMAC1header)
ARRECMAC1header.Compose Array(ARRECMAC1batch, ARRECMAC1detail3, ARRECMAC1detail1, ARRECMAC1detail5, ARRECMAC1detail6)
ARRECMAC1detail1.Compose Array(ARRECMAC1header, ARRECMAC1detail2, ARRECMAC1detail4)
ARRECMAC1detail2.Compose Array(ARRECMAC1detail1)
ARRECMAC1detail3.Compose Array(ARRECMAC1header)
ARRECMAC1detail4.Compose Array(ARRECMAC1batch, ARRECMAC1header, ARRECMAC1detail3, ARRECMAC1detail1, ARRECMAC1detail2)
ARRECMAC1detail5.Compose Array(ARRECMAC1header)
ARRECMAC1detail6.Compose Array(ARRECMAC1header)
Dim i As Integer
Dim j As Integer
i = 0
Dim latestDate As Date
Dim theDate As Date
Dim h As Integer
ARRECMAC1batchFields("CODEPYMTYP").PutWithoutVerification ("CA") ' Batch Type
ARRECMAC1batchFields("CNTBTCH").PutWithoutVerification ("0") ' Batch Number
ARRECMAC1batch.RecordCreate 1
ARRECMAC1batchFields("IDBANK").Value = bank
ARRECMAC1batchFields("PROCESSCMD").PutWithoutVerification ("2") ' Process Command
ARRECMAC1batch.Process
i = 0
Dim totalAmount As Double
For i = 0 To customers.Count - 1
ARRECMAC1header.RecordCreate 2
ARRECMAC1headerFields("CODEPYMTYP").PutWithoutVerification ("CA")
ARRECMAC1headerFields("IDCUST").Value = customers.Item(i).customerCode 'customers.Item(i).customerCode
For h = 0 To customers.Item(i).documents.Count - 1
theDate = CDate(customers.Item(i).documents(h).documentDate)
If theDate > latestDate Then
latestDate = theDate
End If
Next h
ARRECMAC1headerFields("DATERMIT").Value = latestDate 'Payment Date
date_remitten = latestDate
ARRECMAC1headerFields("DATEBUS").Value = latestDate
date_bus = latestDate
ARRECMAC1headerFields("CODEPAYM").Value = "CASH" ' Payment Code
ARRECMAC1headerFields("PROCESSCMD").PutWithoutVerification ("0") ' Process Command Code
ARRECMAC1header.Process
ARRECMAC1detail4.Read
ARRECMAC1detail4Fields("APPLY").Value = "Y" ' Apply
ARRECMAC1detail4Fields("PAYMTYPE").Value = "CA" ' Batch Type
ARRECMAC1detail4Fields("CNTBTCH").Value = ARRECMAC1headerFields("CNTBTCH").Value ' Batch Number
batch_no = ARRECMAC1headerFields("CNTBTCH").Value
ARRECMAC1detail4Fields("CNTITEM").Value = "0" ' Entry Number
ARRECMAC1detail4Fields("IDCUST").Value = customers.Item(i).customerCode 'customers.Item(i).customerCode ' ID Customer
customer_code = customers.Item(i).customerCode
ARRECMAC1detail4Fields("PROTYPE").PutWithoutVerification ("1") ' Process Type
ARRECMAC1detail4.Process
totalAmount = 0
While ARRECMAC1detail4.Fetch
j = 0
For j = 0 To customers.Item(i).documents.Count - 1
If customers.Item(i).documents(j).paymentType <> "Credit" And customers.Item(i).documents(j).documentNumber = ARRECMAC1detail4Fields("IDINVC").Value Then
ARRECMAC1detail4Fields("PAYMTYPE").Value = "CA"
ARRECMAC1detail4Fields("CNTBTCH").Value = ARRECMAC1headerFields("CNTBTCH").Value
ARRECMAC1detail4Fields("AMTRMIT").Value = customers.Item(i).documents(j).cashAmount
ARRECMAC1detail4Fields("PAYMAMT").Value = customers.Item(i).documents(j).cashAmount
ARRECMAC1detail4.Update
totalAmount = totalAmount + customers.Item(i).documents(j).cashAmount
payment_code = customers.Item(i).documents(j).paymentType
End If
Next j
Wend
ARRECMAC1headerFields("AMTRMIT").Value = totalAmount ' Bank Receipt Amount
ARRECMAC1batchFields("DATEBTCH").PutWithoutVerification (latestDate) ' Batch Date
ARRECMAC1batchFields("DEPDATE").PutWithoutVerification (latestDate) ' Deposit Date
ARRECMAC1batch.Update
ARRECMAC1header.Insert
ARRECMAC1detail4.RecordClear
ARRECMAC1header.RecordClear
Next i
MsgBox "AR Receipt Import completed."
Exit Sub
ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
If Errors Is Nothing Then
Logger.LogDetails date_remitten & "," & date_bus & "," & batch_no & "," & customer_code & "," & Format(Now, "YYYY-MM-DD") & "," & "N:" & Replace(Err.Description, vbLf, ""), document_date, payment_code
Else
lCount = Errors.Count
If lCount = 0 Then
Logger.LogDetails date_remitten & "," & date_bus & "," & batch_no & "," & customer_code & "," & Format(Now, "YYYY-MM-DD") & "," & "NOERROR:" & Replace(Err.Description, vbLf, ""), document_date, payment_code
Else
For lIndex = 0 To lCount - 1
Logger.LogDetails date_remitten & "," & date_bus & "," & batch_no & "," & customer_code & "," & Format(Now, "YYYY-MM-DD") & "," & "Error:" & Replace(Errors.Item(lIndex), vbLf, ""), document_date, payment_code
UserForm1.errorNo = UserForm1.errorNo + 1
Next
Errors.Clear
End If
Resume Next
End If
End Sub
Error: Method 'Value' of object 'IAccpacViewField' failed
Looking at the table ARPOOP, the table is empty. So my question is, should I skip the insert/update for ARPOOP and directly insert to ARTCR instead?
