Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Returning Price Data From Sage 300 2012 view using vb.NET 3

Status
Not open for further replies.
planetbluau

planetbluau

Programmer
May 25, 2010
54
AU
Can anyone help with the following code? I am trying to return the current pricing for a customer (to allow for sale pricing etc). The code below does not return the correct values. I have put a few MsgBox's to show values. When the code runs, the BILNAME is always correct. (In SAMINC 1200 will return Ronald Black and 1100 Bargain Mart - San Diago) However, the PRICELIST is WRONG (seems to just return the first it encounters) and so too is the price.

When I run the same VBA code in a macro, it's correct. Can you see where the code maybe wrong?

(The project needs to be done in vb.NET so using the MACRO is not an option.)

MY CODE

Code: 
 Public Sub CheckPrice()
        'This checks the PriceToCheck value = the Proper price for the customer and returns true if equals
        Dim mSession As New AccpacCOMAPI.AccpacSession
        mSession = CreateObject("Accpac.Session")
        mSession.Init("", "XY", "XY1000", "61A")

        mSession.Open("ADMIN", "ADMIN", "SAMLTD", Now.Date, 0, "")

        Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
        mDBLinkCmpRW = mSession.OpenDBLink(1, 0)

        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 OEORD1detail9 As AccpacCOMAPI.AccpacView
        Dim OEORD1detail9Fields As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0502", OEORD1detail9)
        OEORD1detail9Fields = OEORD1detail9.Fields


        'vb.net code to compose arrays
        Dim icViews(4) As AccpacCOMAPI.AccpacView
        icViews(0) = OEORD1detail1
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail2
        OEORD1header.Compose(icViews)

        ReDim icViews(4)
        icViews(0) = OEORD1header
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail9
        OEORD1detail1.Compose(icViews)

        OEORD1detail2.Compose(OEORD1header)
        OEORD1detail9.Compose(OEORD1detail1)



        'enter customer and item
        [COLOR=#204A87][b]OEORD1headerFields.FieldByName("CUSTOMER").Value = "1100"                         ' Set Customer Number
        MsgBox(OEORD1headerFields.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(OEORD1headerFields.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly
        OEORD1detail1Fields.FieldByName("ITEM").Value = "A1-105/0"                        ' Set Item
        MsgBox(OEORD1detail1Fields.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect[/b][/color]
        mDBLinkCmpRW.Close()
        mSession.Close()

        Exit Sub
 
Sort by date Sort by votes
Either get the price from the IC price tables, or .Insert the item first.
 
Upvote 0 Downvote
Thanks Tuba2007.

Am I going about this the correct way to get the price applicable to the customer? (Taking into account sale price etc)

Would it be better to...
[ol 1]
[li]Check Customer Pricelist[/li]
[li]Then Check Unit Price for that Pricelist[/li]
[/ol]

Which view would give the UNITPRICE (taking into account any applicable SALE price)?

Much apprciated.
 
Upvote 0 Downvote
Going with the order entry view is the easiest way to get the correct price. OE will take all sorts of things into account such as contract prices, sale prices, expiry dates, quantity discounts, etc.

You can actually write a whole bunch of code to figure that out yourself, without OE. If your client doesn't have OE, for example, the code you've written isn't going to work.
 
Upvote 0 Downvote
Code: 
Thanks DjangMan,
The client does have OE.  However I am having some difficulty getting the .Insert done.  It keeps producing an error. (HRESULT E_FAIL has been returned from a call to a COM component.)  I've tried to mirror the VBA code in vb.NET but I'm missing something.

The code is the same as above except for the latter section which I've shown below

       [COLOR=#8AE234]'enter customer and item[/color]
        OEORD1headerFields.FieldByName("CUSTOMER").Value = "1100"                         ' Set Customer Number
        MsgBox(OEORD1headerFields.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(OEORD1headerFields.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly

        OEORD1header.Process()[COLOR=#CC0000][/color]
        Dim temp = OEORD1detail1.Exists
        OEORD1detail1.RecordClear()
        temp = OEORD1detail1.Exists
        OEORD1detail1.RecordCreate(0)
        OEORD1detail1Fields.FieldByName("ITEM").Value = "A1-103/0"                        ' Item
        OEORD1detail1Fields.FieldByName("PROCESSCMD").PutWithoutVerification("1")         ' Process Command
     [COLOR=#CC0000]   
        OEORD1detail1.Process()                                                           ' errors here[/color]                                    
        OEORD1detail1.Insert()
        OEORD1detail1Fields.FieldByName("LINENUM").PutWithoutVerification("-1")           ' Line Number
        OEORD1detail1.Read()
        temp = OEORD1detail1.Exists
        OEORD1detail1.RecordCreate(0)
        
        MsgBox(OEORD1detail1Fields.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect
        
        mDBLinkCmpRW.Close()
        mSession.Close()

I can't see where I've gone wrong. The equivelent VBA code works fine.

Ideas?

Thanks again.
 
Upvote 0 Downvote
1. Remove all "temp = " lines, they're junk from recording
2. LOL. You did a .Insert, then a .RecordCreate, which starts a new line! Check it after the .Insert.
 
Upvote 0 Downvote
Tuba2007 Thanks!!

Too many late hours staring at nothing!
 
Upvote 0 Downvote
Folks, I'm missing something here with this code - I know it has to be with the vb.NET difference - and I know you hate it, but if you have achieved what I am trying to do in vb.NET or see what I am still doing wrong I would be grateful.

See the relevant part of the cleaned up code below. (See above for the code that comes before this section) It is still failing on .insert !!

Code: 
         'vb.net code to compose arrays
        Dim icViews(4) As AccpacCOMAPI.AccpacView
        icViews(0) = OEORD1detail1
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail2
        OEORD1header.Compose(icViews)

        ReDim icViews(4)
        icViews(0) = OEORD1header
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = OEORD1detail9
        OEORD1detail1.Compose(icViews)

        OEORD1detail2.Compose(OEORD1header)
        OEORD1detail9.Compose(OEORD1detail1)


        ORDERHEADER.Cancel()
        ORDERHEADER.Init()


        'enter customer and item
        ORDHEADERFIELDS.FieldByName("CUSTOMER").Value = "1200"                         ' Set Customer Number
        ORDDETAILFIELDS1.FieldByName("LINENUM").Value = 0
        ORDDETAILFIELDS1.FieldByName("ITEM").Value = "A1-320/0"                        ' Item
        ORDDETAILFIELDS1.FieldByName("QTYORDERED").Value = "1.0000"                    ' Quantity Ordered
        [COLOR=#CC0000]
        ORDDETAIL1.Insert()                                                            ' This errors in vb.NET
        [/color]
        MsgBox(ORDDETAILFIELDS1.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect
        MsgBox(ORDHEADERFIELDS.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(ORDHEADERFIELDS.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly


        mDBLinkCmpRW.Close()
        mSession.Close()
 
Upvote 0 Downvote
Throw in your error trapping code. OE is fairly good at telling you what the problem is.

However, just eyeballing you haven't created a detail record. Here's some old code I used to do the same:
Code: 
      OEORD1header.RecordClear
      OEORD1header.RecordCreate VIEW_RECORD_CREATE_DELAYKEY
      
      OEORD1header.Fields("CUSTOMER").value = aCustNo
      OEORD1header.Fields("PRICELIST").value = aPriceList
      OEORD1detail1.RecordClear
      OEORD1detail1.RecordCreate 0
    
      OEORD1detail1.Fields("ITEM").value = aItemNo
      OEORD1detail1.Process
      OEORD1detail1.Fields("QTYORDERED").value = CDbl(texQty.Text)
      GetCustomerPrice = FmtNumber(OEORD1detail1.Fields("UNITPRICE").value, OEORD1detail1.Fields("UNITPRCDEC").value)
 
Upvote 0 Downvote
Sorry, for complete clarity I should post the whole code now. I've worked on it a bit so here it is...

Code: 
    Public Sub CheckPrice()
        'This checks the PriceToCheck value = the Proper price for the customer and returns true if equals
        Dim mSession As New AccpacCOMAPI.AccpacSession
        mSession = CreateObject("Accpac.Session")
        mSession.Init("", "XY", "XY1000", "61A")

        mSession.Open("ADMIN", "ADMIN", "SAMLTD", Now.Date, 0, "")

        'On Error GoTo ACCPACErrorHandler


        Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
        mDBLinkCmpRW = mSession.OpenDBLink(1, 0)

        Dim ORDERHEADER As AccpacCOMAPI.AccpacView
        Dim ORDHEADERFIELDS As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0520", ORDERHEADER)
        ORDHEADERFIELDS = ORDERHEADER.Fields

        Dim ORDDETAIL1 As AccpacCOMAPI.AccpacView
        Dim ORDDETAILFIELDS1 As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0500", ORDDETAIL1)
        ORDDETAILFIELDS1 = ORDDETAIL1.Fields

        Dim ORDDETAIL2 As AccpacCOMAPI.AccpacView
        Dim ORDDETAILFIELDS2 As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0740", ORDDETAIL2)
        ORDDETAILFIELDS2 = ORDDETAIL2.Fields

        Dim ORDDETAIL9 As AccpacCOMAPI.AccpacView
        Dim ORDDETAILFIELDS9 As AccpacCOMAPI.AccpacViewFields
        mDBLinkCmpRW.OpenView("OE0502", ORDDETAIL9)
        ORDDETAILFIELDS9 = ORDDETAIL9.Fields


        'vb.net code to compose arrays
        Dim icViews(4) As AccpacCOMAPI.AccpacView
        icViews(0) = ORDDETAIL1
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = ORDDETAIL2
        ORDERHEADER.Compose(icViews)

        ReDim icViews(4)
        icViews(0) = ORDERHEADER
        icViews(1) = Nothing
        icViews(2) = Nothing
        icViews(3) = ORDDETAIL9
        ORDDETAIL1.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL2.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL9.Compose(icViews)

        ORDERHEADER.Cancel()
        ORDERHEADER.Init()


        'enter customer and item
        ORDHEADERFIELDS.FieldByName("CUSTOMER").Value = "1200"                         ' Set Customer Number
        ORDDETAILFIELDS1.FieldByName("ITEM").Value = "A1-320/0"                        ' Item
        ORDDETAILFIELDS1.FieldByName("QTYORDERED").Value = "1.0000"                    ' Quantity Ordered
       [COLOR=#EF2929]
        ORDDETAIL1.Insert()                                                            ' THIS IS WHERE IT ERRORS
[/color]
        MsgBox(ORDDETAILFIELDS1.FieldByName("UNITPRICE").Value)                        ' Return Value - is incorrect
        MsgBox(ORDHEADERFIELDS.FieldByName("BILNAME").Value)                           ' Returns Bilname correctly
        MsgBox(ORDHEADERFIELDS.FieldByName("PRICELIST").Value)                         ' Does not return PRICELIST correctly


        mDBLinkCmpRW.Close()
        mSession.Close()

        Exit Sub

ACCPACErrorHandler:

        Dim lCount As Long
        Dim lIndex As Long
        Dim Errors As AccpacCOMAPI.AccpacErrors


        If Errors Is Nothing Then
            MsgBox(Err.Description)
        Else
            lCount = Errors.Count

            If lCount = 0 Then
                MsgBox(Err.Description)
            Else
                For lIndex = 0 To lCount - 1
                    MsgBox(Errors.Item(lIndex))
                Next
                Errors.Clear()
            End If
            Resume Next

        End If
    End Sub
 
Upvote 0 Downvote
And the order date. Some on the information, like Price List and Location could be defaulting from the customer. But they should be specified within the code.

And you still aren't creating a new detail line record.

Plus - don't skip on your compositions. Do all of them.
 
Upvote 0 Downvote
Ok, thanks - I'll go back and redo and try, try again.
Many thx with your patience!!!
 
Upvote 0 Downvote
You're missing this line
ORDDETAILFIELDS1.RecordCreate(0)
before this line
ORDDETAILFIELDS1.FieldByName("ITEM").Value = "A1-320/0"
 
Upvote 0 Downvote
A big thank you to all your help. Each one helped me get a solution, even if it was not the method I first started with. I just could not get the ACCPACCOMAPI to give me the correct values, even though the very same VBA version of the code would. So, in another leap forward for me (I know many of you may consider it in the other direction [mad]) was to try the Sage 300 ERP .Net API (Accpac.Advantage)

After modifying the code to suit, I am pleased to report it now works fine. For those that read this in the days and weeks to come, I present the working code below. Thanks again. (Until the next glitch)
Code: 
Sub test()
      
        Dim mSession As New Session
        mSession.Init("", "XY", "XY1000", "62A")
        mSession.Open("ADMIN", "ADMIN", "SAMINC", DateTime.Today, 0)
        Dim mDBLinkCmpRW As DBLink = mSession.OpenDBLink(DBLinkType.Company, DBLinkFlags.ReadWrite)

        Dim ORDERHEADER As View = mDBLinkCmpRW.OpenView("OE0520")
        Dim ORDDETAIL1 As View = mDBLinkCmpRW.OpenView("OE0500")
        Dim ORDDETAIL2 As View = mDBLinkCmpRW.OpenView("OE0740")
        Dim ORDDETAIL3 As View = mDBLinkCmpRW.OpenView("OE0180")
        Dim ORDDETAIL4 As View = mDBLinkCmpRW.OpenView("OE0526")
        Dim ORDDETAIL5 As View = mDBLinkCmpRW.OpenView("OE0522")
        Dim ORDDETAIL6 As View = mDBLinkCmpRW.OpenView("OE0508")
        Dim ORDDETAIL7 As View = mDBLinkCmpRW.OpenView("OE0507")
        Dim ORDDETAIL8 As View = mDBLinkCmpRW.OpenView("OE0501")
        Dim ORDDETAIL9 As View = mDBLinkCmpRW.OpenView("OE0502")
        Dim ORDDETAIL10 As View = mDBLinkCmpRW.OpenView("OE0504")
        Dim ORDDETAIL11 As View = mDBLinkCmpRW.OpenView("OE0506")
        Dim ORDDETAIL12 As View = mDBLinkCmpRW.OpenView("OE0503")

        [COLOR=#4E9A06]'vb.net code to compose arrays[/color]
        Dim icViews(6) As View
        icViews(0) = ORDDETAIL1
        icViews(1) = Nothing
        icViews(2) = ORDDETAIL3
        icViews(3) = ORDDETAIL2
        icViews(4) = ORDDETAIL4
        icViews(5) = ORDDETAIL5
        ORDERHEADER.Compose(icViews)

        ReDim icViews(6)
        icViews(0) = ORDERHEADER
        icViews(1) = ORDDETAIL8
        icViews(2) = ORDDETAIL12
        icViews(3) = ORDDETAIL9
        icViews(4) = ORDDETAIL6
        icViews(5) = ORDDETAIL7
        ORDDETAIL1.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL2.Compose(icViews)


        ReDim icViews(2)
        icViews(0) = ORDERHEADER
        icViews(1) = ORDDETAIL1
        ORDDETAIL3.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL4.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDERHEADER
        ORDDETAIL5.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL6.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL7.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL8.Compose(icViews)

        ReDim icViews(3)
        icViews(0) = ORDDETAIL1
        icViews(1) = ORDDETAIL10
        icViews(2) = ORDDETAIL11
        ORDDETAIL9.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL9
        ORDDETAIL10.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL9
        ORDDETAIL11.Compose(icViews)

        ReDim icViews(1)
        icViews(0) = ORDDETAIL1
        ORDDETAIL12.Compose(icViews)


        [highlight #4E9A06]'set customer and item #[/highlight]
        ORDERHEADER.Fields.FieldByName("CUSTOMER").SetValue("1200", False)
        ORDDETAIL1.Fields.FieldByName("ITEM").SetValue("A1-320/0", False)

        [COLOR=#4E9A06]'test output[/color]
        MsgBox(ORDDETAIL1.Fields.FieldByName("UNITPRICE").Value)
        MsgBox(ORDERHEADER.Fields.FieldByName("BILNAME").Value)
        MsgBox(ORDERHEADER.Fields.FieldByName("PRICELIST").Value)


        mDBLinkCmpRW.Dispose()
        mSession.Dispose()


    End Sub

Again,
Thank you!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Raphyy
  • Locked
  • Question
Sage 300 2019 Error Printing Completed PO with popor01.rpt file
Replies
1
Views
175
ettienne
ettienne
vbdbcoder
  • Question
Can Sage NOTE be retrieved 1
Replies
3
Views
589
DjangMan
DjangMan
hushman
  • Locked
  • Question
Deleting quotes from Sage 300
Replies
2
Views
159
ettienne
ettienne
vbdbcoder
  • Locked
  • Question
Can VB6 screen with OCX look like Sage 300 native screen with color theme and flat text box ?
Replies
3
Views
208
vbdbcoder
vbdbcoder
jsh8286
  • Locked
  • Question
Random Slowness One Workstation Sage 300
Replies
0
Views
134
jsh8286
jsh8286

Part and Inventory Search

Sponsor

Back
Top