Macro error - Optional Field - Record already exists

[johnhugh]

Hi,
I'm using Accpac 5.6.
I want to use a macro to copy purchase requisitions including optional fields for every requisition line.
Below is my code and it fails for teh 2nd item with error: "Requisition Line Optional Field - Record already exists"
I reckon it got something to do with the insert or update but I cannot find out how to do it properly.
Would really appreciate if someone can advise on how to add optional fields.

    Dim PTPRH As AccpacCOMAPI.AccpacView
    Dim PTPRHFields As AccpacCOMAPI.AccpacViewFields
    Dim PTPRD As AccpacCOMAPI.AccpacView
    Dim PTPRDFields As AccpacCOMAPI.AccpacViewFields
    Dim PTPRHO As AccpacCOMAPI.AccpacView
    Dim PTPRHOFields As AccpacCOMAPI.AccpacViewFields
    Dim PTPRDO As AccpacCOMAPI.AccpacView
    Dim PTPRDOFields As AccpacCOMAPI.AccpacViewFields
    Dim PTPRC As AccpacCOMAPI.AccpacView
    Dim PTPRCFields As AccpacCOMAPI.AccpacViewFields
    
    mDBLinkCmpRW.OpenView "PT0040", PTPRH
    Set PTPRHFields = PTPRH.Fields
    mDBLinkCmpRW.OpenView "PT0041", PTPRD
    Set PTPRDFields = PTPRD.Fields
    mDBLinkCmpRW.OpenView "PT0816", PTPRHO
    Set PTPRHOFields = PTPRHO.Fields
    mDBLinkCmpRW.OpenView "PT0818", PTPRDO
    Set PTPRDOFields = PTPRDO.Fields
    mDBLinkCmpRW.OpenView "PT0042", PTPRC
    Set PTPRCFields = PTPRC.Fields

    PTPRH.Compose Array(PTPRD, PTPRHO)
    PTPRD.Compose Array(PTPRH, PTPRC, PTPRDO)
    PTPRHO.Compose Array(PTPRH)
    PTPRDO.Compose Array(PTPRD)
    PTPRC.Compose Array(PTPRD)
    '--------------------------------------------------------------------------------------------------------------------------
    PTPRHFields("RQNHSEQ").PutWithoutVerification ("0")            ' Purchase Order Sequence Key    
    temp = PTPRH.Exists
    PTPRH.Init          
    PTPRHFields("RQNHSEQ").PutWithoutVerification ("0")            ' Purchase Order Sequence Key    
    temp = PTPRH.Exists
    PTPRH.Init
   
    PTPRHFields("WORKFLOW").Value = workflow                         ' Workflow
    PTPRHFields("COSTCTR").Value = costCtr
    
    PTPRHFields("PROCESSCMD").PutWithoutVerification ("1")         ' Command
    PTPRH.Process
    
    PTPRHFields("STCODE").Value = shipTo                           ' Ship-To Location
    PTPRHFields("BTCODE").Value = billTo                           ' Bill-To Location   
    PTPRHFields("RQRDDATE").Value = DateSerial(Year(REQRDDate), Month(REQRDDate), Day(REQRDDate))   
      
    For i = 1 To numOfLines
        temp = PTPRD.Exists
        PTPRD.RecordClear
        temp = PTPRD.Exists
        PTPRD.RecordCreate 0
        PTPRDFields("ITEMNO").Value = itemNo(i)
        PTPRDFields("ITEMDESC").Value = itemdesc(i)
        
        'PTPRDFields("PROCESSCMD").PutWithoutVerification ("1")       
        'PTPRDFields.Process
        PTPRDFields("GLACCTFULL") = detGLAccount(i)
        PTPRDFields("REQQTY").Value = quantity(i)                  
        PTPRDFields("UNITCOST").Value = detCosts(i)
        PTPRDFields("RQRDDATE").Value = DateSerial(Year(detRQRDDate(i)), Month(detRQRDDate(i)), Day(detRQRDDate(i)))
        PTPRDFields("VDCODE").Value = detVendor(i)
        PTPRDFields("VENDITEMNO").Value = detVendorItem(i)
        PTPRDFields("LOCATION").Value = detLocation(i)
        PTPRD.Insert
        PTPRDFields("RQNLREV").PutWithoutVerification ("-1")          
        
       [COLOR=red]  [COLOR=red]'add optional field - here it fails for the 2nd item[/color][/color]
[COLOR=red]        PTPRDOFields("OPTFIELD").Value = "CAPEXNO"[/color]
[COLOR=red]        PTPRD.Update[/color]
    Next
    temp = PTPRH.Exists
    PTPRH.Insert

 

[Jay Converse]

This snippet works for me in O/E orders:

dsOpt.Cancel
dsOpt.Fields("OPTFIELD").PutUnconditional sOptField, True

If dsOpt.Read Then
If dsOpt.Fields("OPTFIELD") = sOptField Then ' It will be a different field if it doesn't exist
dsOpt.Fields("VALUE") = sValue
dsOpt.Update
Else
dsOpt.Init
dsOpt.Fields("OPTFIELD") = sOptField
dsOpt.Fields("VALUE") = sValue
dsOpt.Insert
End If
Else ' Doesn't exist
dsOpt.Init
dsOpt.Fields("OPTFIELD") = sOptField
dsOpt.Fields("VALUE") = sValue
dsOpt.Insert
End If
dsHeader.Update

 

[johnhugh]

Fixed.
Problem was that I had the PTPRD.Insert before the optional fields.