Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Check for duplicate in...
Microsoft Dynamics SL (Archived)

Check for duplicate inventory/warehouse combination in grid using VBA

(0) ShareShare
ReportReport
Posted on by Jo Wykerd 1,639

I am trying to check for a duplicate Inventory/Warehouse combination entered in the Inventory Transfer screen using the below code. As I tab off the whseloc field where the check is done, the whole line goes blank.  Please help!

Private Sub cWhseLoc_Chk(ChkStrg As String, retval As Integer)

    Dim invtid As String
    Dim whseloc As String
    Dim lineref As String
    Dim mem_detail As Integer
    Dim rownbr As Integer
    Dim MemMaintFlg As Integer
    Dim InvtidCheck As String
    Dim WhselocCheck As String
    Dim linerefCheck As String

    Call GetBufferValue("bintran.lineref", lineref)
    invtid = GetObjectValue("cinvtid")
    whseloc = GetObjectValue("cwhseloc")

    mem_detail = GetGridHandle("Spread1")
    rownbr = MGetRowNum(mem_detail)

    serr1 = MFirst(mem_detail, MemMaintFlg)
   
    While (serr1 = 0)
 
       'get the inventory on loop line
      Call GetBufferValue("bintran.InvtID", InvtidCheck)
      Call GetBufferValue("bintran.whseloc", WhselocCheck)
      Call GetBufferValue("bintran.lineref", linerefCheck)

       If Trim(InvtidCheck) = Trim(invtid) And Trim(WhselocCheck) = Trim(whseloc) And Trim(lineref) <> Trim(linerefCheck) Then
             
            MsgBox "Inventory ID/Warehouse Combination already used"

        End If

        serr1 = MNext(mem_detail, MemMaintFlg)
       
     Wend
    
    Call msetrownum(mem_detail, rownbr)
 
End Sub

*This post is locked for comments

  • Jo Wykerd Profile Picture
    Jo Wykerd 1,639 on at
    Re: Check for duplicate inventory/warehouse combination in grid using VBA

    Thanks for your help Barry.  

    Will investigate doing my own check on quantities.

    The first problem you helped me with (checking for duplicates in grid) is working fine now.

    Jo

  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at
    Re: Check for duplicate inventory/warehouse combination in grid using VBA

    Jo

    It sounds like the host screen is putting up that message and permitting the user to proceed anyway.

    This code will be in the host'd own Chk event.

    I don't know any way you can influence that.

    One possible approach is for you to do your own check in the customization Chk event.

    If your code decides the user can't proceed, put up your own message, followed by

       retval = ErrNoMess

       exit sub

    That would cause the user to get 2 messages...

    Which you could fix by eliminating your message.

    But suppose there is some situation where your code detects a qty problem, but  the host's code didn't.

    If you don't put up your message, the user will find that they can't proceed, with no message telling them why.

    Another possibility my be Inventory/order options.

    I'm not too knowledgeable in those modules, but I think there is a "allow inventory to go negative?" option.

    I don't think you can detect that the host's mwessage hasd displayed. You can do that when writing object model code, but that doesn't sound appropriate here.

    Barry

  • Jo Wykerd Profile Picture
    Jo Wykerd 1,639 on at
    Re: Check for duplicate inventory/warehouse combination in grid using VBA

    Thanks for you reply Barry.  i am going to try this.

    Please can you help me with another problem in the Shipper Screen.  If the user enters an amount in the Qty field that is more than is available, a warning pops up saying "The qty shipped is more than what is in stock for Inventory Item xxxxx".  This is system message "15378" This is just a warning and the user is still able to carry on with the invoice.  I need to stop the user here and not allow him to continue with this line.  how do I trap this specific message?

  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at
    Re: Check for duplicate inventory/warehouse combination in grid using VBA

    Jo

    << Do you know how to get it to do this?

    I think I might create either a standard VB string array, or a Solomon array (mOpen / mClear)

    Then navigate the "real array".

    For each entry, scan the second array to see if the id exists.

    If it does - put up a message (which could include the row number.)

    If it doesn't, insert it in the 2nd array.

    When you get to the end, set the "main array" back to the original row number.

    Abd, if you found any duplicates, do a

     retval = ErrNoMess

     exit sub

    That will prevent the Save event from taking place.

    Are you familiar with Levels?

    The Update event may fire multiple times - once for each Level being saved.

    But you want to execute your code only once.

    So possibly have an

     if Level = nnnn then

    I guess the "nnn" would be the Grid Level - do you execute your code only if the grid has been changed.

    How does that sound??

    Barry

  • Jo Wykerd Profile Picture
    Jo Wykerd 1,639 on at
    Re: Check for duplicate inventory/warehouse combination in grid using VBA

    Thanks Barry, I have moed this to the Update event.  The only problem is I then have to check each line against all the other lines to see if duplicated which my code is not doing.  Do you know how to get it to do this?

  • Barry Flynn Profile Picture
    Barry Flynn 3,090 on at
    Re: Check for duplicate inventory/warehouse combination in grid using VBA

    Jo

    As I understand it.

    The grid is "supported" by a memory array - one entry per grid line.

    The table buffer bInTran is bound to that array.

    When you click on a grid line, the appropriate entry from the array is loaded into bInTran.

    (Or a new record is initiated.)

    When you depart from that line, after the LineChk event fires, the new/updated bInTran is put back into the array.

    I think your problem is that you are navigating the array from the Chk event of a control that is "on the grid".

    This is before bInTran has been put into the array.

    So as soon as you do an mFirst, the bInTran buffer is overwritten by the record retrieved by the mFirst.

    I don't know of a workaround that will let you run that soer of code from the chk event of a control that is on the grid.

    An alternative may be to do checking for all gris lines as part of the Update event.

    Barry

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

#1
Community Member Profile Picture

Community Member 136

#2
Mohamed Amine Mahmoudi Profile Picture

Mohamed Amine Mahmoudi 102 Super User 2025 Season 1

#3
REUser Profile Picture

REUser 8

Last month Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans