Accounts Payable invoice from code 3

[petermeachem]

We are going to be inserting invoices to us from suppliers. I have no idea how to do this and can't find any samples. I have code for inserting customers and invoices to same, so I suppose it is pretty similar but ends up in different tables. We have a rather old version, 5.0A.
We are actually moving to a new accounts system in the new year, so this is a temporary solution.
Ideally I will be coding this in Access, if it makes any difference.
I would really appreciate some help. Our support has run out too and we won't be buying any more.

 

[Jay Converse]

Record a macro that adds an invoice, that will get you started.

 

[petermeachem]

I didn't know about that, more than gets me started. I have a couple of questions.
1) How do I get the next batch number?
2) We stopped short of actually posting it. Our last line is

APINVOICE1detail1.Read
APINVOICE1detail1Fields("AMTDIST").PutWithoutVerification ("1000.000")   ' Distributed Amount
APINVOICE1detail1.Update

Do I need to put something on the end?

 

[DjangMan]

On the end of what?

Those three lines of code are going to: make the detail line that you asked for the current record, update the distribution amount to 1000 and then update the 'in-memory' version of the detail line with the changes you made.

The next batch number is determined by Accpac automatically. You should see a line of code with the batch view that calls .init or sets the batch # to 0 and then calls .init. After that you can read the batch # from the view.

 

[petermeachem]

I'd best post the whole macro, sorry it's a bit long. As you can see, the batch number 4132 is embedded.

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 APINVOICE1batch As AccpacCOMAPI.AccpacView
Dim APINVOICE1batchFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0020", APINVOICE1batch
Set APINVOICE1batchFields = APINVOICE1batch.Fields
Dim APINVOICE1header As AccpacCOMAPI.AccpacView
Dim APINVOICE1headerFields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0021", APINVOICE1header
Set APINVOICE1headerFields = APINVOICE1header.Fields
Dim APINVOICE1detail1 As AccpacCOMAPI.AccpacView
Dim APINVOICE1detail1Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0022", APINVOICE1detail1
Set APINVOICE1detail1Fields = APINVOICE1detail1.Fields
Dim APINVOICE1detail2 As AccpacCOMAPI.AccpacView
Dim APINVOICE1detail2Fields As AccpacCOMAPI.AccpacViewFields
mDBLinkCmpRW.OpenView "AP0023", APINVOICE1detail2
Set APINVOICE1detail2Fields = APINVOICE1detail2.Fields
APINVOICE1batch.Compose Array(APINVOICE1header)
APINVOICE1header.Compose Array(APINVOICE1batch, APINVOICE1detail1, APINVOICE1detail2)
APINVOICE1detail1.Compose Array(APINVOICE1header, APINVOICE1batch)
APINVOICE1detail2.Compose Array(APINVOICE1header)

APINVOICE1batch.Init
APINVOICE1header.Init
APINVOICE1detail1.Browse "", 1
APINVOICE1detail1.Fetch

APINVOICE1detail1Fields("CNTBTCH").PutWithoutVerification ("4132")    ' Batch Number
APINVOICE1detail1Fields("CNTITEM").PutWithoutVerification ("1")       ' Entry Number
APINVOICE1detail1Fields("CNTLINE").PutWithoutVerification ("0")       ' Line Number

APINVOICE1detail1.Init
APINVOICE1detail2.Browse "", 1
APINVOICE1detail2.Fetch

APINVOICE1detail2Fields("CNTBTCH").PutWithoutVerification ("4132")    ' Batch Number
APINVOICE1detail2Fields("CNTITEM").PutWithoutVerification ("1")       ' Entry Number
APINVOICE1detail2Fields("CNTPAYM").PutWithoutVerification ("0")       ' Payment Number

APINVOICE1detail2.Init
APINVOICE1batchFields("BTCHDESC").PutWithoutVerification ("Test commissions")   ' Description
APINVOICE1batch.Update

APINVOICE1headerFields("IDVEND").Value = "MSG001"                     ' Vendor Number
APINVOICE1headerFields("INVCDESC").PutWithoutVerification ("Monthly invoice")   ' Invoice Description
APINVOICE1headerFields("PROCESSCMD").PutWithoutVerification ("0")     ' Process Command Code

APINVOICE1header.Process
APINVOICE1detail2.Browse "", 1
APINVOICE1detail2.Fetch

APINVOICE1detail2Fields("CNTBTCH").PutWithoutVerification ("4132")    ' Batch Number
APINVOICE1detail2Fields("CNTITEM").PutWithoutVerification ("1")       ' Entry Number
APINVOICE1detail2Fields("CNTPAYM").PutWithoutVerification ("0")       ' Payment Number

APINVOICE1detail1.Init
APINVOICE1detail1Fields("IDGLACCT").Value = "0700-0701"               ' G/L Account
APINVOICE1detail1.Insert
APINVOICE1detail1.Browse "", 1

APINVOICE1detail1Fields("CNTLINE").PutWithoutVerification ("-1")      ' Line Number

APINVOICE1detail1.Read
APINVOICE1headerFields("IDINVC").Value = "GENERATED"                  ' Document Number
APINVOICE1headerFields("AMTGROSTOT").Value = "1000.000"               ' Document Total Including Tax

APINVOICE1detail1.Read
APINVOICE1detail1Fields("AMTDIST").PutWithoutVerification ("1000.000")   ' Distributed Amount
APINVOICE1detail1.Update

 

[DjangMan]

The line:

APINVOICE1batch.Init

created the batch number. You'd replace the hardcoded field value with:

APINVOICE1detail1Fields("CNTBTCH").PutWithoutVerification (APINVOICE1batch.fields("CNTBTCH").value)

 

[petermeachem]

That's brilliant, thanks so much.

 

[petermeachem]

Not quite working. I added and modified the start like so

 Dim Session As New AccpacCOMAPI.AccpacSession

        Session.Init "", "AS", "AS1000", "50A"
        Session.Open "ADMIN", "password", "DUNLTD", Now(), 0, ""

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkCmpRW = Session.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
Set mDBLinkSysRW = Session.OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READWRITE)

it runs through without errors, and creates teh batch ok, but there is no data in it. Any idea why?

 

[DjangMan]

Make sure that after you add your details that you call the .update method for the Header View.

 

[petermeachem]

You are going to have to spell it out I'm afraid

APINVOICE1detail1.Read
APINVOICE1detail1Fields("AMTDIST").PutWithoutVerification ("0.010")   ' Distributed Amount
APINVOICE1detail1.Update

APINVOICE1header.Update

this errors on teh APINVOICE1header.Update line. Automation error, Unspecified error.

 

[Jay Converse]

You can't update the header before you .Insert it.

 

[ettienne]

Your code needs to be cleaned up, it's all over the place.
The basic flow for batch-header-detail processing is:

Open views
Compose views

Batch.Init
Put Batch field values
Batch.Update

Header.Init
Put Header field values
Detail.Init
Put Detail field values
Detail.Insert
Header.Insert

Close views

 

[ettienne]

Note that you do a Batch.Update but you do Header.Insert and Detail.Insert

 

[Jay Converse]

And that's because a Batch.Init automatically creates a new record, but the others don't. In later versions, .Init is deprecated to .RecordGenerate, which is more explicit with .RecordGenerate {true/false}.

 

[petermeachem]

Thank you so much, all sorted now. I couldn't have done it without your help