Why doesn't .Insert and .Update work?

[stemar5000]

I am using ACCPAC Advantage Series Enterprise Edition v5.0A, Pervasive SQL, Windows XP.
I have written a VBA macro that uses the A/R module. I have looked in the ACCPAC System Manager User Guide to understand the behavior of each ACCPAC VBA command. I am using the same logic as in the book, i.e.: 1. read the batch; 2.read the header; 3. read and update or insert the detail1, detail2, detail3, & detail4; 4. update or insert the header; 5. update the batch.
Through lots of testing, I have only been able to make '.Update' work with the header but not with any detail tables and the '.Insert' command doesn't work at all.
My code is not super long and it's easy to understand for someone who worked with ACCPAC objects in VBA before. The code imports new data to ACCPAC and then export the ACCPAC data in a CSV file.
Is there someone who would be willing to look at my code and see where it needs fixing? I could send you my code through email as it may be too long for posting even though it's not super long.
I'm open to any suggestions. Thank you very much,
-Steven

 

[zemp]

Have you composed the views properly? This allows them to talk to each other properly. The details must be related to a header, header to a batch (if one exists), etc..

Post your code that declares, sets and composes your views. We'll have a look at that first.

Things also need to be done in a logical order. Open and find the header you need (create and insert if it does not exist), then insert the detail line, update the header, repeat as necessary.

zemp

 

[stemar5000]

Thanks a million for helping me.

In the first part of the code, I setup two-dimentional arrays to make up tables with rows of records and columns of fields. The 'variable(row, 0)' is the column label.

Later in the code, I will import data from the ACCPAC tables and fill these arrays. The part about filling arrays work. Actually everything works except the .Update and .Insert commands.

So after having Dimmed a lot of stuff at the very beginning, I go:

'Redim arrays to match ACCPAC tables: variable(row, col)
ReDim vARbatch(1 To cARbatchFieldCount, 0 To 500)
ReDim vARheader(1 To cARheaderFieldCount, 0 To 500)
ReDim vARdetail1(1 To cARdetail1FieldCount, 0 To 500)
ReDim vARdetail2(1 To cARdetail2FieldCount, 0 To 500)
ReDim vARdetail3(1 To cARdetail3FieldCount, 0 To 500)
ReDim vARdetail4(1 To cARdetail4FieldCount, 0 To 500)

Then I go:

'Fill arrays with ACCPAC variable names
vARbatch(1, 0) = "CODEPYMTYP" 'key field
vARbatch(2, 0) = "CNTBTCH" 'key field
vARbatch(3, 0) = "DATEBTCH"
vARbatch(4, 0) = "BATCHDESC"
...

...for each vARbatch(), vARheader(), vARdetail1(), vARdetail2(), vARdetail3(), vARdetail4() arrays with the proper ACCPAC variable names.

Then I go:

'Set ACCPAC objects
On Error GoTo ACCPACErrorHandler

Dim Session As ACCPACXAPILib.xapiSession
Dim ARRECMAC1batch As ACCPACXAPILib.xapiView
Dim ARRECMAC1header As ACCPACXAPILib.xapiView
Dim ARRECMAC1detail1 As ACCPACXAPILib.xapiView
Dim ARRECMAC1detail2 As ACCPACXAPILib.xapiView
Dim ARRECMAC1detail3 As ACCPACXAPILib.xapiView
Dim ARRECMAC1detail4 As ACCPACXAPILib.xapiView

'Set ACCPAC views
Set Session = CreateObject("ACCPAC.xapiSession")
Session.Open cUSER, cPASS, cDB, Date, 0

Set ARRECMAC1batch = Session.OpenView("AR0041", "AR")
Set ARRECMAC1header = Session.OpenView("AR0042", "AR")
Set ARRECMAC1detail1 = Session.OpenView("AR0044", "AR")
Set ARRECMAC1detail2 = Session.OpenView("AR0045", "AR")
Set ARRECMAC1detail3 = Session.OpenView("AR0043", "AR")
Set ARRECMAC1detail4 = Session.OpenView("AR0061", "AR")

'Compose views
ARRECMAC1batch.Compose Array(ARRECMAC1header)
ARRECMAC1header.Compose Array(ARRECMAC1batch, ARRECMAC1detail3, ARRECMAC1detail1)
ARRECMAC1detail1.Compose Array(ARRECMAC1header, ARRECMAC1detail2)
ARRECMAC1detail2.Compose Array(ARRECMAC1detail1)
ARRECMAC1detail3.Compose Array(ARRECMAC1header)
ARRECMAC1detail4.Compose Array(ARRECMAC1batch, ARRECMAC1header, _
    ARRECMAC1detail3, ARRECMAC1detail1, ARRECMAC1detail2)

Then I start importing to the arrays.

So is the Compose Views part correct? I may have made a mistake there, yet I followed the ACCPAC book and the ACCPAC sample code.

The next part is the import routine and filling the arrays, and it's in that part that I use .Insert and .Update.

Thank you very much,
-Steven

 

[zemp]

The compositions are correct. So far the code looks good.

To insert a detail you first have to have an open batch and a header. Below is some pseudo code that might help.

Browse for batch
if fetched then
Init header or browse and fetch
add or change all fields..
init detail
add all detail fields...
insert detail
insert header or update
update batch.

If you are trying to use detail4 (AR0061) then things will be different. This is kind of a temporary superview and does not exist as a table. It is hard to explain, but you don't necessarily init this view and you have to process it sometimes. The process will update the view with specific information so that you can update other views. Trial an error is the only consistant methodology I can recommend with ACCPAC.

In any case post the next section of your code and we will have a look.


zemp

 

[stemar5000]

So next I use CSV files that were previously exported from ACCPAC to fill the arrays. There is one CSV per ACCPAC table (header, detail1, detail2, detail3). (You're right about detail4 and I discovered through my testing that I don't need to mess with it.)

'Read header CSV file
vInputFile = cCSVheader
vFullInputFile = cDRIVE & vInputFile
Open vFullInputFile For Input As #10

vRecNo = 0
Do Until EOF(10)
    Input #10, vARheader(1, vRecNo), vARheader(2, vRecNo), vARheader(3, vRecNo), vARheader(4, vRecNo), _
        vARheader(5, vRecNo), vARheader(6, vRecNo), vARheader(7, vRecNo), _
        vARheader(9, vRecNo), vARheader(10, vRecNo), vARheader(11, vRecNo), vARheader(12, vRecNo), _
        vARheader(13, vRecNo), vARheader(14, vRecNo), vARheader(15, vRecNo), vARheader(16, vRecNo), _
        vARheader(17, vRecNo), vARheader(18, vRecNo), vARheader(19, vRecNo), vARheader(20, vRecNo), _
        vARheader(21, vRecNo), vARheader(22, vRecNo), vARheader(23, vRecNo), _
        vARheader(25, vRecNo), vARheader(30, vRecNo), vARheader(31, vRecNo), vARheader(32, vRecNo), _
        vARheader(33, vRecNo), vARheader(34, vRecNo), vARheader(35, vRecNo), vARheader(36, vRecNo), _
        vARheader(37, vRecNo), vARheader(39, vRecNo), vARheader(40, vRecNo), _
        vARheader(41, vRecNo), vARheader(42, vRecNo), vARheader(43, vRecNo), vARheader(44, vRecNo), _
        vARheader(45, vRecNo), vARheader(46, vRecNo), vARheader(47, vRecNo), vARheader(48, vRecNo), _
        vARheader(49, vRecNo), vARheader(50, vRecNo)
    vRecNo = vRecNo + 1
    If vRecNo = 500 Then ReDim Preserve vARheader(1 To cARheaderFieldCount, 0 To 1000)
    If vRecNo = 1001 Then Exit Do 'limit of records in one batch CSV file is set at 1000 incl. header row
Loop

Close #10
vRECNOheader = vRecNo - 1

...and so I do this for each of the 4 tables: header, detail1, detail2, and detail3 and I fill the 4 arrays vARheader(), vARdetail1(), vARdetail2(), vARdetail3().

Then the update part...

'Batch
ARRECMAC1batch.Browse "CNTBTCH = " & vCurrentBatchNo & "", True
If ARRECMAC1batch.Fetch Then
    For vRow = 1 To vRECNOheader 'record by record (row by row)
        'Check if record exists
        ARRECMAC1header.Fields("CODEPYMTYP").Value = "CA"
        ARRECMAC1header.Fields("CNTBTCH").Value = vCurrentBatchNo
        ARRECMAC1header.Fields("CNTITEM").Value = vRow
        If ARRECMAC1header.Exists Then
            ARRECMAC1header.Read
        
            'Put values in header variables
            For vCol = 4 To cARheaderFieldCount 'start at 4 to avoid updating key fields
                If vCol <> 8 And vCol <> 24 And vCol <> 26 And vCol <> 27 And vCol <> 28 And vCol <> 29 And vCol <> 38 Then 'avoid reserved variables
                    ARRECMAC1header.Fields(vARheader(vCol, 0)).Value = vARheader(vCol, vRow)
                End If
            Next vCol
            
            'Put values in detail1 variables
            For vCol = 5 To cARdetail1FieldCount 'start at 5 to avoid updating key fields
                ARRECMAC1detail1.Fields(vARdetail1(vCol, 0)).Value = vARdetail1(vCol, vRow)
            Next vCol
            ARRECMAC1detail1.Update

            'Put values in detail2 variables
            For vCol = 6 To cARdetail2FieldCount 'start at 6 to avoid updating key fields
                ARRECMAC1detail2.Fields(vARdetail2(vCol, 0)).Value = vARdetail2(vCol, vRow)
            Next vCol
            ARRECMAC1detail2.Update
            
            'Put values in detail3 variables
            For vCol = 5 To cARdetail3FieldCount 'start at 5 to avoid updating key fields
                    ARRECMAC1detail3.Fields(vARdetail3(vCol, 0)).Value = vARdetail3(vCol, vRow)
            Next vCol
            ARRECMAC1detail3.Update
            
            'Update header
            ARRECMAC1header.Update
        Else

...then the insert part

            'Put values in header variables
            ARRECMAC1header.Init
            For vCol = 1 To cARheaderFieldCount
                If vCol <> 8 And vCol <> 24 And vCol <> 26 And vCol <> 27 And vCol <> 28 And vCol <> 29 And vCol <> 38 Then 'avoid reserved variables
                    ARRECMAC1header.Fields(vARheader(vCol, 0)).Value = vARheader(vCol, vRow)
                End If
            Next vCol
            
            'Put values in detail1 variables
            ARRECMAC1detail1.Init
            For vCol = 1 To cARdetail1FieldCount
                ARRECMAC1detail1.Fields(vARdetail1(vCol, 0)).Value = vARdetail1(vCol, vRow)
            Next vCol
            ARRECMAC1detail1.Insert

            'Put values in detail2 variables
            ARRECMAC1detail1.Init
            For vCol = 1 To cARdetail2FieldCount
                ARRECMAC1detail2.Fields(vARdetail2(vCol, 0)).Value = vARdetail2(vCol, vRow)
            Next vCol
            ARRECMAC1detail2.Insert
            
            'Put values in detail3 variables
            ARRECMAC1detail1.Init
            For vCol = 1 To cARdetail3FieldCount
                ARRECMAC1detail3.Fields(vARdetail3(vCol, 0)).Value = vARdetail3(vCol, vRow)
            Next vCol
            ARRECMAC1detail3.Insert
            
            'Insert header
            ARRECMAC1header.Insert
        End If
            
    Next vRow
    
Else  'if vCurrentBatchNo does not exist in ACCPAC dB

    MsgBox "vCurrentBatchNo = " & vCurrentBatchNo & " Batch number does not exist in ACCPAC dB.", _
        vbCritical, cMSGTITLE
    Exit Sub

End If

ARRECMAC1batch.Update

Then the parts after that all work.
In my testing, I found that I could make .Update work only on the header table and if I didn't update detail1, detail2, & detail3. The .Insert command doesn't work on anything.
I am grateful for your help.
-Steven

 

[zemp]

First off I would get rid of the .exists in your initial 'If' statement and just use the .read. The .read will give you the same result and load the view with the logical record that you wanted. If it fails then the header does not exist and you can create a new one.

Your update works on the header because you have read the logical record into the view. You have not done this for the details. The view is not looking at a detail record when you ask it to update. As a result it does not know which record to update.

To update you need to get the record into the view, either with a browse and fetch or by assign key field values and using read. Then assign changes to the fields and update.

So for your details you need to tell the view which record to update.

If the details don't exist you can also insert them into the existing header.


Now for the inserts. It doesn't look like you are entering any data for the headers key fields. When you use Init you clear all the field values and get the view ready to accept new header values. So the values you entered before the inital .read in the If statement are wiped out. Now the view does not know how to relate the header to the batch and all inserts will fail. Reassign the key header fields.

One other note, be careful with using variables for the field names. the ACCPAC xAPI can be very touchy about this sort of thing. You may be better off acutally typing the field names.

I hope that I got some of the issues resolved in this first pass.

zemp

 

[stemar5000]

Hi again,

The update part now works so thank you very much for your help.

The insert part does not work after I made these changes. Would you please tell me what to fix in the revised insert part?

        Else
            
            'Put values in header variables
            ARRECMAC1header.Init
            ARRECMAC1header.Fields("CODEPYMTYP").Value = "CA"
            ARRECMAC1header.Fields("CNTBTCH").Value = vCurrentBatchNo
            ARRECMAC1header.Fields("CNTITEM").Value = vRow
            For vCol = 4 To cARheaderFieldCount 'start at 4 to avoid updating key fields
                If vCol <> 8 And vCol <> 24 And vCol <> 26 And vCol <> 27 And vCol <> 28 And vCol <> 29 And vCol <> 38 Then 'avoid reserved variables
                    ARRECMAC1header.Fields(vARheader(vCol, 0)).Value = vARheader(vCol, vRow)
                End If
            Next vCol
            
            'Put values in detail1 variables
            ARRECMAC1detail1.Init
            ARRECMAC1detail1.Fields("CODEPAYM").Value = "CA"
            ARRECMAC1detail1.Fields("CNTBTCH").Value = vCurrentBatchNo
            ARRECMAC1detail1.Fields("CNTITEM").Value = vRow
            ARRECMAC1detail1.Fields("CNTLINE").Value = 20 'I DON'T UNDERSTAND THIS VARIABLE***
            For vCol = 5 To cARdetail1FieldCount 'start at 5 to avoid updating key fields
                ARRECMAC1detail1.Fields(vARdetail1(vCol, 0)).Value = vARdetail1(vCol, vRow)
            Next vCol
            ARRECMAC1detail1.Insert
            
            'Put values in detail2 variables
            ARRECMAC1detail2.Init
            ARRECMAC1detail2.Fields("CODEPAYM").Value = "CA"
            ARRECMAC1detail2.Fields("CNTBTCH").Value = vCurrentBatchNo
            ARRECMAC1detail2.Fields("CNTITEM").Value = vRow
            ARRECMAC1detail2.Fields("CNTLINE").Value = 20 'I DON'T UNDERSTAND THIS VARIABLE***
            ARRECMAC1detail2.Fields("CNTSEQ").Value = vRow 'I DON'T UNDERSTAND THIS VARIABLE***
            For vCol = 6 To cARdetail2FieldCount 'start at 6 to avoid updating key fields
                ARRECMAC1detail2.Fields(vARdetail2(vCol, 0)).Value = vARdetail2(vCol, vRow)
            Next vCol
            ARRECMAC1detail2.Insert
            
            'Put values in detail3 variables
            ARRECMAC1detail3.Init
            ARRECMAC1detail3.Fields("CODEPAYM").Value = "CA"
            ARRECMAC1detail3.Fields("CNTBTCH").Value = vCurrentBatchNo
            ARRECMAC1detail3.Fields("CNTITEM").Value = vRow
            ARRECMAC1detail3.Fields("CNTLINE").Value = 20 'I DON'T UNDERSTAND THIS VARIABLE***
            For vCol = 5 To cARdetail3FieldCount 'start at 5 to avoid updating key fields
                ARRECMAC1detail3.Fields(vARdetail3(vCol, 0)).Value = vARdetail3(vCol, vRow)
            Next vCol
            ARRECMAC1detail3.Insert
            
            'Insert header
            ARRECMAC1header.Insert
            
        End If

So I am doing ...
header.init
header filling data
details.init
details filling data
details.insert
header.insert

Plus, do you know what the variables CNTLINE and CNTSEQ do? They must be important since they are key variables.

Thank you,

-Steven

 

[zemp]

I think that you are overloading the header with too many different types of detail lines. Which detail you use (detail1, detail2, detail3) will depend on the remit type (Transaction type in the header) of the receipt. You cannot add a detail for a Misc receipt to a remit type that is a prepayment.

Record and check macros to see which detail type is used by each transaction type. Then you will need to use the header RMITTYPE to determine which detail to add to. Sometimes its two types but usually only one.

I think you can ignore the CNTLINE and CNTSEQ fields. Yes thay are keys but the view will fill them in for you properly if they are not supplied.

Look for a code similar to this ...
header.init
header filling data
select case RMITTYPE 'tranasction type
case 1
detail4.init
detail4 filling data
detail4.insert
case 2
detail1init
detail1 filling data
detail1.insert
case 5
detail3.init
detail3 filling data
detail3.insert
end select
header.insert

ensure that you have all the various cases covered.


zemp

 

[stemar5000]

Thank you very much for all your help.
My client has decided to postpone this project because we are not going to make the deadline. I have not redone the Insert part yet but I understand the logic in your last post. My client is now looking specifically for an ACCPAC consultant (if you happen to know a resource please pass it on and I will pass it on to my client).
Thank again I'm grateful for your help. And I hope this post was useful to a few TekTips users just like other posts have been useful to me.
-Steven

 

[zemp]

That's the business we are in. ACCPAC consulting and customization.

Where are you and/or your client located?

If you post your email I can give you more details.

zemp

 

[stemar5000]

Hi again,

I talked to my client yesterday re: ACCPAC consulting and they finally decided to postpone this project.

But if they decided to get back to it, I will post again. Thanks again for all your help.

-Steven