Importing Accpac AR Invoice with macro 1

[multisoft]

Our Macro below is suppose to import in AR invoice entries from a csv file into Accpac version 5.4 ERP 500.

However, it only import the first entry in the csv file, and generates the error "'Run-time error: Method

'Value' of Object 'IAccpacviewfield' failed" My real problem is that i cannot get the code to import

multiple entries from the csv file. I also notice that I cannot create additional entry lines on the one

one entry batch created by my macro when I edit the created batch inside accpac. Please, please Could

anyone out there assist me - am about to loose my job!!!!. Thank you in advance.

Isiak
multisoft

Option Explicit
Sub MainSub()

On Error GoTo ACCPACErrorHandler

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 ARINVCPOST2 As AccpacCOMAPI.AccpacView
Dim ARINVCPOST2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0048", ARINVCPOST2
Set ARINVCPOST2Fields = ARINVCPOST2.Fields

Dim ARINVOICE3batch As AccpacCOMAPI.AccpacView
Dim ARINVOICE3batchFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0031", ARINVOICE3batch
Set ARINVOICE3batchFields = ARINVOICE3batch.Fields

Dim ARINVOICE3header As AccpacCOMAPI.AccpacView
Dim ARINVOICE3headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0032", ARINVOICE3header
Set ARINVOICE3headerFields = ARINVOICE3header.Fields

Dim ARINVOICE3detail1 As AccpacCOMAPI.AccpacView
Dim ARINVOICE3detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0033", ARINVOICE3detail1
Set ARINVOICE3detail1Fields = ARINVOICE3detail1.Fields

Dim ARINVOICE3detail2 As AccpacCOMAPI.AccpacView
Dim ARINVOICE3detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0034", ARINVOICE3detail2
Set ARINVOICE3detail2Fields = ARINVOICE3detail2.Fields


Dim ARINVOICE3detail3 As AccpacCOMAPI.AccpacView
Dim ARINVOICE3detail3Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0402", ARINVOICE3detail3
Set ARINVOICE3detail3Fields = ARINVOICE3detail3.Fields



'///////////////////////////////////////////
Dim ARCUSTOMER As AccpacCOMAPI.AccpacView
Dim ARCUSTOMERFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AR0024", ARCUSTOMER
Set ARCUSTOMERFields = ARCUSTOMER.Fields

ARINVOICE3batch.Compose Array(ARINVOICE3header)

ARINVOICE3header.Compose Array(ARINVOICE3batch, ARINVOICE3detail1, ARINVOICE3detail2, ARINVOICE3detail3) ',

ARINVOICE3detail4) 'Isiak

ARINVOICE3detail1.Compose Array(ARINVOICE3header, ARINVOICE3batch)

ARINVOICE3detail2.Compose Array(ARINVOICE3header)

ARINVOICE3detail3.Compose Array(ARINVOICE3header)


Dim strNewBatchID As String
Dim LastJournalid As String
Dim intCounter As Integer
Dim strErrorMsg As String
Dim bErrorOccured As Boolean

bErrorOccured = False
ARINVOICE3batch.Browse "", True

'extract data from file
'get header info first


ARINVOICE3batch.Browse "((BTCHSTTS = 1) OR (BTCHSTTS = 7))", 1
ARINVOICE3detail1.Browse "", 1
ARINVOICE3detail1.Fetch

'create new batch
ARINVOICE3batch.Init 'creates a new batch number

ARINVOICE3batch.Read

'get last batch number
With ARINVOICE3batch
.GoBottom
strNewBatchID = Str(Val(ARINVOICE3batch.Fields("CNTBTCH").Value))
End With

ARINVOICE3batch.Browse "(CNTBTCH =" & strNewBatchID & ")", True
ARINVOICE3batchFields("INVCTYPE") = "2"
ARINVOICE3batch.Update
ARINVOICE3batch.Read
ARINVOICE3header.RecordCreate 0
ARINVOICE3detail1.Cancel

Dim intTransId As Integer
Dim strSrcType As String
Dim strJrnlDesc As String
Dim strOldBatch As String
Dim dTotalBatchAmt As String
Dim strShipCode As String
Dim strInvcDate As String
Dim strTermCode As String
Dim strTaxGrp As String
Dim intInvCount As Integer
Dim strCustID As String


intInvCount = 0
dTotalBatchAmt = 0
intTransId = 0
intCounter = 3
'intLineNumber = intLineNumber - 3
intLineNumber = 3

While ReadData 'This received the records to be imported from a CSV file


DoEvents

If strdata(1) = "1" Then 'it is header info

DoEvents

intInvCount = intInvCount + 1
strJrnlDesc = ""
strSrcType = ""
intCounter = 3

ARINVOICE3header.Init 'creates a new header

While Trim(strBatchHeader(intCounter)) <> ""

If strBatchHeader(intCounter) = "IDCUST" Then
strCustID = strdata(intCounter)
With ARCUSTOMER
.Browse "IDCUST = " & strCustID & "", True
If Not .Fetch Then
MsgBox "Customer information could not be found.", vbOKOnly + vbExclamation, "Information

Missing"
'goto next data
GoTo NextData
End If
End With

End If


If strBatchHeader(intCounter) = "TERMCODE" Then
strTermCode = strdata(intCounter)
Else
strTermCode = ""
End If

If strBatchHeader(intCounter) = "DATEINVC" Then
strInvcDate = FormatDate(strdata(intCounter))
Else
strInvcDate = Format(Date, "yyyy/mm/dd")
End If

ARINVOICE3headerFields("TEXTTRX").Value = Trim(strDocType) '1 '// Invoice.
ARINVOICE3headerFields("IDTRX").Value = Trim(strTransType) '12 '// Summary entered.
ARINVOICE3headerFields("SWMANTX").Value = 0 '// Do not calculate tax.

If Trim(strdata(intCounter)) <> "" Then
If strBatchHeader(intCounter) = "TEXTTRX" Then 'ignore
ElseIf strBatchHeader(intCounter) = "IDTRX" Then 'ignore
ElseIf strBatchHeader(intCounter) = "SWMANTX" Then 'ignore
ElseIf strBatchHeader(intCounter) = "IDCUST" Then
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = strCustID

ElseIf ARINVOICE3headerFields(strBatchHeader(intCounter)).Type = FLD_DATE Then 'handle dates
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = FormatDate(strdata(intCounter))

ElseIf strBatchHeader(intCounter) = "CNTITEM" Then
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = intInvCount
ElseIf strBatchHeader(intCounter) = "IDINVC" Then
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = strdata(intCounter)
ElseIf strBatchHeader(intCounter) = "DATEINVC" Then
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = FormatDate(strdata(intCounter))
ElseIf strBatchHeader(intCounter) = "FISCYR" Then
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = strdata(intCounter)
ElseIf strBatchHeader(intCounter) = "FISCPER" Then
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = strdata(intCounter)

ElseIf strBatchHeader(intCounter) = "RATETYPE" Then
ARINVOICE3headerFields(strBatchHeader(intCounter)).Value = strdata(intCounter)
End If
End If
intCounter = intCounter + 1
Wend

intTransId = 0

ElseIf strdata(1) = "2" Then 'it is Detail 1 info

DoEvents

ARINVOICE3detail1.Init

intCounter = 4
While Trim(strDetail1Header(intCounter)) <> ""

If Trim(strdata(intCounter)) <> "" Then
If strDetail1Header(intCounter) = "CNTLINE" Then
ARINVOICE3detail1Fields(strDetail1Header(intCounter)).Value = intTransId
ElseIf strDetail1Header(intCounter) = "AMTEXTN" Then
ARINVOICE3detail1Fields(strDetail1Header(intCounter)).Value = Format(strdata(intCounter),

"##,###,###,###,###.##")
dTotalBatchAmt = dTotalBatchAmt + Val(Format(strdata(intCounter), "##,###,###,###,###.##"))

'Error occurs here when reading the 2nd entry
ElseIf strDetail1Header(intCounter) = "IDDIST" Then
ARINVOICE3detail1Fields(strDetail1Header(intCounter)).Value = strdata(intCounter) 'Isiak

ElseIf strDetail1Header(intCounter) = "TAXSTTS1" Then
ARINVOICE3detail1Fields(strDetail1Header(intCounter)).Value = strdata(intCounter) 'Isiak

End If
End If
intCounter = intCounter + 1
Wend


intTransId = intTransId - 1

ElseIf strdata(1) = "3" Then 'it is Detail 2 info

DoEvents

intCounter = 2


ElseIf strdata(1) = "4" Then 'it is Detail 3 info

intCounter = 4
While Trim(strDetail3Header(intCounter)) <> ""

If Trim(strdata(intCounter)) <> "" Then
If strDetail3Header(intCounter) = "OPTFIELD" Then
optfieldval = strdata(intCounter)

ARINVOICE3detail3Fields(strDetail3Header(intCounter)).Value = strdata(intCounter)
Else
ARINVOICE3detail3Fields(strDetail3Header(intCounter)).Value = strdata(intCounter)

End If

End If

intCounter = intCounter + 1

Wend

ARINVOICE3detail3.Insert
ARINVOICE3detail1.Insert
ARINVOICE3header.Insert
ARINVOICE3batch.Read

End If



Exit Sub



End Sub

 

[Jay Converse]

What does this CSV file look like?

 

[multisoft]

Hi,

Thanks for the reponse. Here is the csv file i created for testing:

RECTYPE CNTBTCH CNTITEM IDCUST IDINVC TEXTTRX IDTRX INVCDESC IDACCTSET DATEINVC DATEASOF FISCYR FISCPER CODECURN RATETYPE
RECTYPE CNTBTCH CNTITEM CNTLINE IDDIST TEXTDESC QTYINVC AMTCOST AMTEXTN AMTCOGS AMTTXBL TOTTAX TAXSTTS1 IDACCTREV
RECTYPE CNTBTCH CNTITEM CNTPAYM DATEDUE AMTDUE AMTDISC
RECTYPE CNTBTCH CNTITEM OPTFIELD VALUE TYPE
1 551 1 1970 553 1 12 Trim Interface Test 211 TRADE 20060410 20060410 2006 4 USD SP
2 551 1 -1 GEN Debit Notes/Invoices 1 0 10000 0 10000 500 1 50100-200-10
3 551 1 1 20060410 10500 0
4 551 1 LOCATION PT0001 1
1 551 2 1970 554 1 12 Trim Interface Test 211 TRADE 20060410 20060410 2006 4 USD SP
2 551 2 -1 GEN Debit Notes/Invoices 1 0 10000 0 10000 500 1 50100-200-10
3 551 2 1 20060410 10500 0
4 551 2 LOCATION PT0001 1
1 551 2 1970 555 1 12 Trim Interface Test 211 TRADE 20060410 20060410 2006 4 USD SP
2 551 2 -1 GEN Debit Notes/Invoices 1 0 10000 0 10000 500 1 50100-200-10
3 551 2 1 20060410 10500 0
4 551 2 LOCATION PT0001 1

 

[Jay Converse]

But that's a standard Accpac format. You don't need code, you just select File/Import.

 

[multisoft]

I know one can import AR csv file within Accpac. But what we want to achieve is to create an AR invoice batch in Accpac using macro externally. Please look at the code to see if you can assist us.

Best regards

 

[Jay Converse]

Well, it's pretty ugly code, but one obvious thing is the .Inserts need to be inside the Ifs. You need to track the current and previous RECTYPEs so that you're .Inserting the Previous record when it changes.

 

[ettienne]

Agreed, pretty ugly code.

Cut out all this crap:

ARINVOICE3batch.Browse "((BTCHSTTS = 1) OR (BTCHSTTS = 7))", 1
ARINVOICE3detail1.Browse "", 1
ARINVOICE3detail1.Fetch

'create new batch
ARINVOICE3batch.Init 'creates a new batch number

ARINVOICE3batch.Read

'get last batch number
With ARINVOICE3batch
    .GoBottom
    strNewBatchID = Str(Val(ARINVOICE3batch.Fields("CNTBTCH").Value))
End With

ARINVOICE3batch.Browse "(CNTBTCH =" & strNewBatchID & ")", True
ARINVOICE3batchFields("INVCTYPE") = "2"
ARINVOICE3batch.Update
ARINVOICE3batch.Read
ARINVOICE3header.RecordCreate 0
ARINVOICE3detail1.Cancel

and replace with:

ARINVOICE3batch.Init 'creates a new batch number
ARINVOICE3batchFields("INVCTYPE") = "2"
ARINVOICE3batch.Update

ARINVOICE3header.RecordCreate 0

That just cleans things up somewhat, you don't need all that crap in between.

 

[ettienne]

Your .Insert are in the wrong place, you need one after each detail and header record is completed.
Yours seem to be after all the records have been read from the CSV file, after the last Wend.

 

[multisoft]

Ettienne, thanks a million, am just going to do what you said and get back to you immediately.

Regrds

 

[multisoft]

Ettienne, I just made the changes you suggested but still have the problem. It creates a batch with one entry only. please help. Many many thanks

best regrds

 

[Jay Converse]

Post your code again, your loops still aren't right.

 

[ettienne]

Here is some simple clean code to create AR invoices:

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink

Dim ARbatch As AccpacCOMAPI.AccpacView

Dim ARheader As AccpacCOMAPI.AccpacView

Dim ARdetail1 As AccpacCOMAPI.AccpacView

Dim ARdetail2 As AccpacCOMAPI.AccpacView

Dim ARdetail3 As AccpacCOMAPI.AccpacView

Dim ARdetail4 As AccpacCOMAPI.AccpacView



'Open DB

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)



'Open views

mDBLinkCmpRW.OpenView "AR0031", ARbatch

mDBLinkCmpRW.OpenView "AR0032", ARheader

mDBLinkCmpRW.OpenView "AR0033", ARdetail1

mDBLinkCmpRW.OpenView "AR0034", ARdetail2

mDBLinkCmpRW.OpenView "AR0402", ARdetail3

mDBLinkCmpRW.OpenView "AR0401", ARdetail4



'Compose views

ARbatch.Compose Array(ARheader)

ARheader.Compose Array(ARbatch, ARdetail1, ARdetail2, ARdetail3, Nothing)

ARdetail1.Compose Array(ARheader, ARbatch, ARdetail4)

ARdetail2.Compose Array(ARheader)

ARdetail3.Compose Array(ARheader)

ARdetail4.Compose Array(ARdetail1)



'Create batch

ARbatch.RecordCreate 1

ARbatch.Fields("BTCHDESC").PutWithoutVerification "Batch description"

ARbatch.Update



ARheader.RecordCreate 0

ARheader.Fields("IDCUST").Value = "1200"

ARheader.Fields("IDINVC").Value = "INV1234"



ARdetail1.RecordCreate 0

ARdetail1.Fields("IDACCTREV").Value = "9999"

ARdetail1.Fields("AMTEXTN").Value = "100.000"

ARdetail1.Insert



ARheader.Insert



ARheader.RecordCreate 0

ARheader.Fields("IDCUST").Value = "1200"

ARheader.Fields("IDINVC").Value = "INV1235"



ARdetail1.RecordCreate 0

ARdetail1.Fields("IDACCTREV").Value = "9999"

ARdetail1.Fields("AMTEXTN").Value = "120.000"

ARdetail1.Insert



ARheader.Insert

This will run in Sample company.

 

[ettienne]

Another pointer: do not populate the header CNTITEM field or the detail CNTLINE field, let Accpac handle that.

 

[multisoft]

Ettienne MVP,

It worked !!
I must most humbly thank you immensely for your no hold-back assistance. I owe you a carton of beer!! I guess you are an expert in this business. Again thank you.

felix - multisoft