x Want to participate? Join the group to interact
Options
  • State Suggested Answer
  • Replies 21 replies
  • Answers 1 answer
  • Subscribers 86 subscribers
  • Views 6068 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
92
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
963
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
mroman

<Error: 0 in Method WRITE>

SUGGESTED
Posted By

Hi Forum,

I am getting an  <Error: 0  in Method WRITE> message when trying to update a component in Bill Detail table.

I've tried setting key values, SetKey(), Setkey(bmComponentkey)... with no luck.

Any suggestions?... here is the script

scrapPercentage = 0.000 : strItemCode = "" : oLines = 0 : bmItemCode = "": strBillNo = "": strBillRev = "": LineBillNo = "": LineRev = "": strLinesKey = ""
kBillNo = "" : kRevision = "" : kLineKey = "": kLineKeySeqNo = "": TotStdCost = 0: StdCost = 0: need = 0 : Rev = "" : bmItemCode = "" : LineBillNo = "" : LineKey = "" : CompRevision = "" : bmComponentKey = ""

'--------------------------------------------------------------------------
Dim oscript, oss, retval, soPrint

Set oscript = CreateObject("ProvideX.Script")
oscript.Init ("\\SAGE\Acctg\Version 2016\Sage 100 Advanced\MAS90\Home\")

Set oss = oscript.NewObject("SY_SESSION")
retval = oss.nSetUser("k4s", "k4SyStem")
retval = oss.nSetCompany("TST")
retval = oss.nSetModule("B/M")
retval = oss.nSetDate("B/M", "20180516")

'----------------------------------------------------------------------------
'sCompany = oSession.CompanyCode

'if sCompany = "FSC" then
'sComponent = "140-000007" : nPercent = 1.00

'-----------------------------------------------------------------------------
'Set excel object

Dim objExcel
Dim objWkbk

Set objExcel = CreateObject("Excel.Application")
Set objWkbk=objExcel.Workbooks.Open("C:\Apps\components\SCRAP.csv")
RowCount = objExcel.ActiveWorkbook.Sheets(1).UsedRange.Rows.count
MsgBox "Number of rows used in the excel sheet " & RowCount
'retMsg = oScript.AsObject(oScript.UI).MessageBox("",sMsg)


'-----------------------------------------------------------------------------
'Set Sage 100 objects

retVal = oss.nSetProgram(oss.nLookupTask("BM_Bill_ui"))
Set oBomItem = oScript.NewObject("BM_Bill_bus", oSS)
Set oLines = oScript.NewObject("BM_BillDetail_bus", oSS)

'-----------------------------------------------------------------------------
'Read Excel file (csv) with components to update and Process Sage 100 items

nextCustomerLine = "" : tmpItem = "" : tmpScrap = ""

'Do Until objExcel.cells(InitRow,1).Value = ""
for InitRow = 2 to RowCount

if objExcel.cells(InitRow,1).Value = "" then objExcel.Quit

tmpItem = objExcel.cells(InitRow,2).Value
tmpScrap = objExcel.cells(InitRow,5).Value

MsgBox "Item and Scrap: " & tmpItem & " " & tmpScrap
'retMsg = oSession.AsObject(oSession.UI).MessageBox("",sMsg)

sComponent = tmpItem

retVal = oLines.nSetBrowseIndex("kWHEREUSED", "kWHEREUSED")
'retVal = oLines.nSetKeyValue("ComponentItemCode$",sComponent)
retVal = oLines.nSetKey(sComponent)
retVal = oLines.nFind(sComponent)
retVal = oLines.nSetBrowseFilter(sComponent)
oLines.nMoveNext()

Do While Not oLines.nEOF
retVal = oLines.nGetValue("BillNo$", LineBillNo)
retVal = oLines.nGetValue("LineKey$", LineKey)
retVal = oLines.nGetValue("Revision$", Rev)
retVal = oLines.nGetValue("ComponentItemCode$", bmItemCode)
retVal = oLines.nGetValue("ComponentRevision$", CompRevision)
bmComponentKey = bmItemCode&LineBillNo&Rev&CompRevision&LineKey
'retVal = oLines.nSetKey(bmComponentKey)


MsgBox "sComponent: " & sComponent & " bmItemCode: " & bmItemCode & LineBillNo & " " & Rev & " " & CompRevision & " " & LineKey
'retMsg = oSession.AsObject(oSession.UI).MessageBox("",sMsg)

If sComponent <> bmItemCode then
exit do
else
'Set Key Values to write back current record
'retVal = oLines.nSetKeyValue("ComponentItemCode$", bmItemCode)
'retVal = oLines.nSetKeyValue("BillNo$", LineBillNo)
'retVal = oLines.nSetKeyValue("Revision$", Rev)
'retVal = oLines.nSetKeyValue("ComponentRevision$", CompRevision)
'retVal = oLines.nSetKeyValue("LineKey$", LineKey)
retVal = oLines.nSetKey(bmComponentKey)
retVal = oLines.nSetValue("ScrapPercent", nPercent)
retVal = oLines.nWrite()
'MsgBox "Write retVal = " & retVal & " nPercent value: " & nPercent
'MsgBox "Component: " & sComponent & ", % value: " & nPercent

End If
oLines.nMoveNext()
msgbox "next record"
Loop
MsgBox "Finished Processing LineBillNo Value: " & LineBillNo & " - " & LineRev & " Component: " & bmItemCode
'retVal = oSession.AsObject(oSession.UI).MessageBox("",sMsg)

Next

  • 0

    I forgot to point where the error occurs..

    'Set Key Values to write back current record
    'retVal = oLines.nSetKeyValue("ComponentItemCode$", bmItemCode) 
    'retVal = oLines.nSetKeyValue("BillNo$", LineBillNo)
    'retVal = oLines.nSetKeyValue("Revision$", Rev)
    'retVal = oLines.nSetKeyValue("ComponentRevision$", CompRevision)
    'retVal = oLines.nSetKeyValue("LineKey$", LineKey)
    retVal = oLines.nSetKey(bmComponentKey)
    retVal = oLines.nSetValue("ScrapPercent", nPercent)
    retVal = oLines.nWrite() ----- >>>> right here is where i get the error message.

    Regards,

  • 0 in reply to mroman

    Anybody that can give me a hint on to update the BM Component ScarpPercent field ????

    I have not seen anything on the web that has to do with updating BOM's components

    Regards,

  • 0 in reply to mroman

    Hi,

    Now i am getting "missing or invalid header"....

    Any clues anybody ???

    Regards,

  • 0 in reply to mroman

    I had to cut and past your code into notepad and strip out all the commented out lines so I could figure out what was actually being used.

    This is my best guess.

    In any event, one thing that stands out to me is that you have used nFind to try to get to the desired record.  My notes suggest that using the Find directive is read only.  nSetkey should be used if you want to write to the record.  And to be perfectly honest that may have been what was holding up another project of mine.  SetKey seems to put the record in edit mode?

    Seems like my projects use this rough order.

    SetBrowseIndex

    SetBrowseFilter

    MoveFirst     (you don't seem to have this?)

    Do

         (code)

       MoveNext

    Loop

    Hope this helps.

  • 0 in reply to TomTarget

    I always thought you had to edit lines through the header object... but perhaps that doesn't matter as much for a BoM recipe.

    Set oLines = oSession.AsObject(oBusObj.Lines)

  • 0 in reply to TomTarget

    Thanks Tom,

    I guess I have to post the latest code after a few changes that I got from Kent.

    After those changes I am getting the message "missing or invalid header"

    Regards,

  • 0 in reply to Kevin M

    Hi Kevin,

    You are right about the edit lines thru the header.

    Those are the changes I did to the code.

    but I still don't know what I am doing wrong.

    I'll post the latest code later on.

    Regars,

  • 0 in reply to mroman

    here is the latest code.  with this code I get the message ...

    "The item code you have entered(abc-123) is the same as the bill number for this entry"

    Code:

    scrapPercentage = 0.000 : strItemCode = "" : oLines = 0 : bmItemCode = "": strBillNo = "": strBillRev = "": LineBillNo = "": LineRev = "": strLinesKey = ""
    kBillNo = "" : kRevision = "" : kLineKey = "": kLineKeySeqNo = "": TotStdCost = 0: StdCost = 0: need = 0 : Rev = "" : bmItemCode = "" : LineBillNo = "" : LineKey = "" : CompRevision = "" : bmComponentKey = ""
    '--------------------------------------------------------------------------
    Dim oscript, oss, retval, soPrint
    Set oscript = CreateObject("ProvideX.Script")
    'oscript.Init ("\\SAGE\Acctg\Version 2016\Sage 100 Advanced\MAS90\Home\")
    oscript.Init ("C:\Sage\Sage 100 Standard ERP\MAS90\Home\")

    Set oss = oscript.NewObject("SY_SESSION")
    retval = oss.nSetUser("k4s", "k4SyStem")
    retval = oss.nSetCompany("XYZ")
    retval = oss.nSetModule("B/M")
    retval = oss.nSetDate("B/M", "20180606")
    '----------------------------------------------------------------------------
    'sCompany = oSession.CompanyCode
    'if sCompany = "FSC" then
    'sComponent = "140-000007" : nPercent = 1.00
    '-----------------------------------------------------------------------------
    'Set excel object
    Dim objExcel
    Dim objWkbk
    Set objExcel = CreateObject("Excel.Application")
    Set objWkbk=objExcel.Workbooks.Open("C:\Akatz\Fourstar\SCRAP.csv")
    RowCount = objExcel.ActiveWorkbook.Sheets(1).UsedRange.Rows.count
    MsgBox "Number of rows used in the excel sheet " & RowCount
    'retMsg = oScript.AsObject(oScript.UI).MessageBox("",sMsg)

    '-----------------------------------------------------------------------------
    'Set Sage 100 objects
    retVal = oss.nSetProgram(oss.nLookupTask("BM_Bill_ui"))
    Set oBomItem = oScript.NewObject("BM_Bill_bus", oss)
    '-----------------------------------------------------------------------------
    'Read Excel file (csv) with components to update and Process Sage 100 items
    nextCustomerLine = "" : tmpItem = "" : tmpScrap = ""
    'Do Until objExcel.cells(InitRow,1).Value = ""
    for InitRow  = 2 to RowCount
     if objExcel.cells(InitRow,1).Value = "" then objExcel.Quit
     
        tmpItem = objExcel.cells(InitRow,2).Value
        tmpScrap = objExcel.cells(InitRow,5).Value
       
     MsgBox "Item and Scrap: " & tmpItem & "  " & tmpScrap
      
     sComponent = tmpItem
     nPercent = tmpScrap
      retVal = oBomItem.oLines.nSetBrowseIndex("kWHEREUSED", "kWHEREUSED")
      'retVal = oBomItem.oLines.nSetKeyValue("ComponentItemCode$",sComponent)
      retVal = oBomItem.oLines.nSetKey(sComponent)
      retVal = oBomItem.oLines.nFind(sComponent)
      'retVal = oBomItem.oLines.nSetBrowseFilter(sComponent)
      oBomItem.oLines.nMoveNext()
    Do While Not oBomItem.oLines.nEOF
     retVal = oBomItem.oLines.nGetValue("BillNo$", LineBillNo)
     retVal = oBomItem.oLines.nGetValue("LineKey$", LineKey)
     retVal = oBomItem.oLines.nGetValue("Revision$", Rev)
     retVal = oBomItem.oLines.nGetValue("ComponentItemCode$", bmItemCode)
     retVal = oBomItem.oLines.nGetValue("ComponentRevision$", CompRevision)
     bmComponentKey = bmItemCode&LineBillNo&Rev&CompRevision&LineKey
     'retVal = oBomItem.oLines.nSetKey(bmComponentKey)
     
     'Read BOM line with primary key
     
     bmKey = LineBillNo&Rev&LineKey
     
      retVal = oBomItem.nSetKeyValue("BillNo$", LineBillNo)
      retVal = oBomItem.nSetKeyValue("Revision$", Rev)
      retVal = oBomItem.nSetKeyValue("LineKey$", LineKey)
      retVal = oBomItem.nSetKey()
      'retVal = oBomItem.nFind()
         
     'MsgBox  "sComponent: " & sComponent & "   bmItemCode: " & bmItemCode & " " & LineBillNo & "  "  & Rev & "  " & CompRevision & "  " & LineKey
     'retMsg = oSession.AsObject(oSession.UI).MessageBox("",sMsg)
     
     If sComponent <> bmItemCode  then      
        exit do
      End If   
     'Set Key Values to write back current record
      'retVal = oBomItem.oLines.nSetBrowseIndex("kWHEREUSED", "kWHEREUSED")
      retVal = oBomItem.oLines.nSetKeyValue("ComponentItemCode$", bmItemCode) 
      retVal = oBomItem.oLines.nSetKeyValue("BillNo$", LineBillNo)
      retVal = oBomItem.oLines.nSetKeyValue("Revision$", Rev)
      retVal = oBomItem.oLines.nSetKeyValue("ComponentRevision$", CompRevision)
      retVal = oBomItem.oLines.nSetKeyValue("LineKey$", LineKey)
      retVal = oBomItem.oLines.nSetKey()
      
      MsgBox  "sComponent: " & sComponent & "   bmItemCode: " & bmItemCode & " - " & LineBillNo & "  "  & Rev & "  " & CompRevision & "  " & LineKey
      MsgBox(oBomItem.oLines.sLastErrorMsg & vbCRLF & " - " & bmItemCode)
      
     
     
      retVal = oBomItem.oLines.nSetValue("ScrapPercent", nPercent)
        if retVAL = 0 Then
        MsgBox(oBomItem.oLines.sLastErrorMsg & vbCRLF & "scrap value")
        end if
      'retVal = oBomItem.oLines.nMoveNext()
      
      retVal = oBomItem.oLines.nWrite()
      
      if retVal <> 1 Then
        MsgBox(oBomItem.oLines.sLastErrorMsg & vbCRLF & "BOM Lines")
       MsgBox "Component: " & bmItemCode & ",  % value: " & nPercent
      end if  
      
     
     retVal = oBomItem.nWrite()
     
      if retVal <> 1 Then
        MsgBox(oBomItem.oLines.sLastErrorMsg & vbCRLF & "BOM Main")
       'MsgBox "Component: " & sComponent & ",  % value: " & nPercent
      end if 
      
     retVal = oBomItem.oLines.nMoveNext()
     msgbox "next record"
     MsgBox  "Finished Processing LineBillNo Value: " & LineBillNo & " - " & LineRev & "   Component: " & bmItemCode
    Loop
      'MsgBox  "Finished Processing LineBillNo Value: " & LineBillNo & " - " & LineRev & "   Component: " & bmItemCode
      'retVal = oSession.AsObject(oSession.UI).MessageBox("",sMsg)
      bmItemCode = "" : sComponent = "" 
    Next
    Regards,

  • 0 in reply to mroman

    Hi Forum,

    I am still having issues trying to run the script successfully.  I am getting an error on the 

    retVal = oBomItem.oLines.nMoveNext()

    Is not moving to the next record...

    The message is "The file is empty or does not match filter."

    First i am getting retVal = 0 when setting the key on oBomItem.oLines

    Any Ideas?...

    Regards,

    Manuel Roman

  • 0 in reply to mroman

    retVal = oLines.nMoveNext()

*Community Hub is the new name for Sage City