Update ORDER Entry doens't update record

[bryan1207]

Hi I just want to ask what is the problem in my code. When updating the Order entry it doens't update the real Order number, instead it update the next record.
I'm updating the following fields:

            OEORD1headerFields.FieldByName("PONUMBER").Value = PONUMBER                        ' Purchase Order Number
            OEORD1headerFields.FieldByName("SHIPTRACK").Value = SHIPTRACK                     ' Shipment Tracking Number

            OEORD1headerFields.FieldByName("SHIPVIA").Value = SHIPVIA                         ' Ship-Via Code 
            OEORD1headerFields.FieldByName("REFERENCE").PutWithoutVerification(REFERENCE)       ' Order Reference

Below is my code:
I have suspected with this line:

OEORD1headerFields.FieldByName("ORDNUMBER").PutWithoutVerification(ORDERNO)

See code in full.

Public Function fSAGEUpdateSO(ByVal sSystemConnection As String, ByRef ORDERNO As String, ByVal SHIPVIA As String, ByVal SHIPTRACK As String, ByVal PONUMBER As String, ByVal REFERENCE As String, ByRef sError As String) As Boolean
        Try
            'LINENUM = "-1"

            ' ================================
            ' DECLARE ENVIRONMENT
            ' ================================ 
            Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
            mDBLinkCmpRW = sSageSession.OpenDBLink(AccpacCOMAPI.tagDBLinkTypeEnum.DBLINK_COMPANY, AccpacCOMAPI.tagDBLinkFlagsEnum.DBLINK_FLG_READWRITE) 
            Dim mDBLinkSysRW As AccpacCOMAPI.AccpacDBLink
            mDBLinkSysRW = sSageSession.OpenDBLink(AccpacCOMAPI.tagDBLinkTypeEnum.DBLINK_SYSTEM, AccpacCOMAPI.tagDBLinkFlagsEnum.DBLINK_FLG_READWRITE)

            Dim temp As Boolean
            Dim OEORD1header As AccpacCOMAPI.AccpacView
            Dim OEORD1headerFields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0520", OEORD1header)
            OEORD1headerFields = OEORD1header.Fields

            Dim OEORD1detail1 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail1Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0500", OEORD1detail1)
            OEORD1detail1Fields = OEORD1detail1.Fields

            Dim OEORD1detail2 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail2Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0740", OEORD1detail2)
            OEORD1detail2Fields = OEORD1detail2.Fields

            Dim OEORD1detail3 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail3Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0180", OEORD1detail3)
            OEORD1detail3Fields = OEORD1detail3.Fields

            Dim OEORD1detail4 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail4Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0526", OEORD1detail4)
            OEORD1detail4Fields = OEORD1detail4.Fields

            Dim OEORD1detail5 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail5Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0522", OEORD1detail5)
            OEORD1detail5Fields = OEORD1detail5.Fields

            Dim OEORD1detail6 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail6Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0508", OEORD1detail6)
            OEORD1detail6Fields = OEORD1detail6.Fields

            Dim OEORD1detail7 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail7Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0507", OEORD1detail7)
            OEORD1detail7Fields = OEORD1detail7.Fields

            Dim OEORD1detail8 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail8Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0501", OEORD1detail8)
            OEORD1detail8Fields = OEORD1detail8.Fields

            Dim OEORD1detail9 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0502", OEORD1detail9)
            OEORD1detail9Fields = OEORD1detail9.Fields

            Dim OEORD1detail10 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail10Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0504", OEORD1detail10)
            OEORD1detail10Fields = OEORD1detail10.Fields

            Dim OEORD1detail11 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail11Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0506", OEORD1detail11)
            OEORD1detail11Fields = OEORD1detail11.Fields

            Dim OEORD1detail12 As AccpacCOMAPI.AccpacView
            Dim OEORD1detail12Fields As AccpacCOMAPI.AccpacViewFields
            mDBLinkCmpRW.OpenView("OE0503", OEORD1detail12)
            OEORD1detail12Fields = OEORD1detail12.Fields

            OEORD1header.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail1, Nothing, OEORD1detail3, OEORD1detail2, OEORD1detail4, OEORD1detail5})

            OEORD1detail1.Compose(New AccpacCOMAPI.AccpacView() {OEORD1header, OEORD1detail8, OEORD1detail12, OEORD1detail9, OEORD1detail6, OEORD1detail7})

            OEORD1detail2.Compose(New AccpacCOMAPI.AccpacView() {OEORD1header})

            OEORD1detail3.Compose(New AccpacCOMAPI.AccpacView() {OEORD1header, OEORD1detail1})

            OEORD1detail4.Compose(New AccpacCOMAPI.AccpacView() {OEORD1header})

            OEORD1detail5.Compose(New AccpacCOMAPI.AccpacView() {OEORD1header})

            OEORD1detail6.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail1})

            OEORD1detail7.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail1})

            OEORD1detail8.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail1})

            OEORD1detail9.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail1, OEORD1detail10, OEORD1detail11})

            OEORD1detail10.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail9})

            OEORD1detail11.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail9})

            OEORD1detail12.Compose(New AccpacCOMAPI.AccpacView() {OEORD1detail1})

            ' ================================
            ' OPEN SALES ORDER
            ' ================================
            OEORD1headerFields.FieldByName("DRIVENBYUI").Value = "1"                          ' Driven by UI

            OEORD1detail1Fields.FieldByName("DRIVENBYUI").Value = "1"                         ' Driven by UI
            OEORD1header.Cancel()
            OEORD1header.Cancel()
            OEORD1header.Init()
            OEORD1detail2.Browse("", 1)
            OEORD1detail2.Fetch()
            temp = OEORD1header.Exists

            OEORD1headerFields.FieldByName("ORDNUMBER").PutWithoutVerification(ORDERNO)         ' Order Number 

            OEORD1header.Browse("", 0)
            OEORD1header.Fetch()
            OEORD1header.Order = 0
            OEORD1detail1Fields.FieldByName("LINENUM").PutWithoutVerification("-32767")      ' Line Number
            OEORD1detail1.Browse("", 1)
            OEORD1detail1.Fetch()
            OEORD1detail9Fields.FieldByName("PRNCOMPNUM").PutWithoutVerification("-2147483647")   ' Parent Component Number

            OEORD1detail9Fields.FieldByName("COMPNUM").PutWithoutVerification("-2147483647")   ' Component Number

            OEORD1detail9.Browse("", 1)
            OEORD1detail9.Fetch()
            OEORD1detail3Fields.FieldByName("UNIQUIFIER").PutWithoutVerification("-32767")   ' Uniquifier
            OEORD1detail3.Browse("", 1)
            OEORD1detail3.Fetch()
            OEORD1detail2Fields.FieldByName("PAYMENT").PutWithoutVerification("-32767")      ' Payment Number
            OEORD1detail2.Browse("", -1)

            OEORD1detail2Fields.FieldByName("PAYMENT").PutWithoutVerification("-32767")      ' Payment Number

            OEORD1detail2.Browse("", -1)
            OEORD1detail2.Fetch()
            OEORD1detail2.Browse("", 1)

            OEORD1detail2Fields.FieldByName("PAYMENT").PutWithoutVerification("-32767")      ' Payment Number

            OEORD1detail2.Browse("", -1)
            OEORD1detail2.Fetch()
            temp = OEORD1header.Exists

            OEORD1headerFields.FieldByName("PONUMBER").Value = PONUMBER                        ' Purchase Order Number
            OEORD1headerFields.FieldByName("SHIPTRACK").Value = SHIPTRACK                     ' Shipment Tracking Number

            OEORD1headerFields.FieldByName("SHIPVIA").Value = SHIPVIA                         ' Ship-Via Code
            OEORD1headerFields.FieldByName("OECOMMAND").Value = "4"                           ' Process O/E Command
            OEORD1headerFields.FieldByName("REFERENCE").PutWithoutVerification(REFERENCE)       ' Order Reference

            OEORD1header.Process()
            OEORD1header.Order = 1
            temp = OEORD1header.Exists
            OEORD1header.Update()
            OEORD1header.Read()
            OEORD1header.Order = 0
            OEORD1detail1Fields.FieldByName("LINENUM").PutWithoutVerification("-32767")      ' Line Number
            OEORD1detail1.Browse("", 1)
            OEORD1detail1.Fetch()
            OEORD1detail9Fields.FieldByName("PRNCOMPNUM").PutWithoutVerification("-2147483647")   ' Parent Component Number

            OEORD1detail9Fields.FieldByName("COMPNUM").PutWithoutVerification("-2147483647")   ' Component Number

            OEORD1detail9.Browse("", 1)
            OEORD1detail9.Fetch()
            OEORD1detail3Fields.FieldByName("UNIQUIFIER").PutWithoutVerification("-32767")   ' Uniquifier
            OEORD1detail3.Browse("", 1)
            OEORD1detail3.Fetch()
            OEORD1detail2Fields.FieldByName("PAYMENT").PutWithoutVerification("-32767")      ' Payment Number
            OEORD1detail2.Browse("", -1)
            OEORD1detail2.Fetch()
            OEORD1detail2.Browse("", 1)

            OEORD1detail2Fields.FieldByName("PAYMENT").PutWithoutVerification("-32767")      ' Payment Number

            OEORD1detail2.Browse("", -1)
            OEORD1detail2.Fetch()
            temp = OEORD1header.Exists

            Return True
        Catch ex As Exception
            sError = Err.Description
            fHandleSageErrors(sError)
            sError = "ERR~~<Order - " & ORDERNO & "> " & sError
        End Try
        Return False
    End Function

 

[Jay Converse]

1. You wiped everything out when you did this:

OEORD1header.Browse("", 0)
OEORD1header.Fetch()

Use OEORD1header.Read instead if you have an existing order

2. Get rid of all the OEORD1Detail lines, they do nothing
3. Get rid of all the "temp =" lines, that's junk from the macro recording

Sage 300 Whisperer

 

[DjangMan]

Change all view .Order properties back to zero before you update.

 

[bryan1207]

Thanks for the input guys. I just copy that code from the Macro it self. I've already changed the .Order properties to 0.

I don't know where to used this:


OEORD1header.Browse("", 0)
OEORD1header.Fetch()

Use OEORD1header.Read instead if you have an existing order

I'm sorry, just new with ACPAC development.

 

[Jay Converse]

You should either do this:

OEORD1header.Order = 1
OEORD1header.Browse("ORDNUMBER = " & strMynumber, 1)
if OEORD1header.Fetch() then
...
end if

or

OEORD1header.Order = 1
OEORD1header.Fields("ORDNUMBER") = strMyNumber
if OEORD1header.Read theb
...
End if






Sage 300 Whisperer

 

[DjangMan]

.Browse is like setting up a WHERE clause in SQL. It uses the current .Order for the searching order and starts from the current record position.
.Fetch is asking the database to find the next record in the table that matches the current .Browse criteria, using the current .Order.

.Read is making use of the key values for the index referenced by the current .Order.

So when you called:

            OEORD1headerFields.FieldByName("ORDNUMBER").PutWithoutVerification(ORDERNO)         ' Order Number 

            OEORD1header.Browse("", 0)
            OEORD1header.Fetch()

You started to set the 'starting point' of the .Browse search but you had not set the .Order = 1. With .Order still set to 0 you're searching the database using the ORDUNIQ key. Thus updating the ORDNUMBER field would have no affect on your subsequent .Browse statement. You would likely be returned the first order in the table.

If you had called:

            OEORD1headerFields.FieldByName("ORDNUMBER").PutWithoutVerification(ORDERNO)         ' Order Number 

            OEORD1header.Browse("ORDNUMBER=""" & ORDERNO & """", 0)
            OEORD1header.Fetch()

Then you would have found the order but it would have taken a while because you're told Sage to look for that record in ORDUNIQ order.

Jay's response gives you the fastest way to navigate to the order record when you have the order number.