Results 1 to 27 of 27

Thread: [RESOLVED] inventory updates same name Twice Although there are 2 different item names why?

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Resolved [RESOLVED] inventory updates same name Twice Although there are 2 different item names why?

    i have a listview that i add products in to it
    when i hit the ok button it updates the same item twice Although there are 2 different item names why?
    e.x
    lets say my list has 2 items with 2 different names

    Item 1 - Tv 5 units
    Item 2 - Phone - 10 units


    both of them are in the listview as SubItems(2)

    how do i update each item name that are in the listview to the inventory system?

    if i Have 100 Tv at hand in the inventory so i need to see after the update 95

    if i Have 200 Phones at hand in the inventory so i need to see 190

    the update dosnt update the 2 items listed
    its updating only the first item twice
    meaning Tv - it updates 10 so i see in the inventory 90 instead of 95

    this is the code i am using at this moment
    Code:
            Dim A As Integer
            Dim Sum As Currency
            
            For A = 1 To LsVw.ListItems.Count
            Sum = Sum + CCur(LsVw.ListItems(A).SubItems(2))
                Next
                
            
            Dim S As String
            S = "Update Inventory set InvSold = InvSold + " & Sum & ","
            S = S & " InvRemain = InvRemain - " & Sum
            S = S & " Where InvItem = '" & LsVw.ListItems(1).SubItems(1) & "'"
            CN.Execute S
    any help will be appreciated
    regards salsa31

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Your code tells it to update only one item, and it is only doing it once but with the wrong number if you are seeing the wrong result.

    Why are you looping through the listview and adding those items together before doing your update?

    Again think about what you are doing then look at what you have done. You should see your mistake.

    Hint: If you had say 20 items in your list view and you sold 10 each of all the even numbered items your code would remove 100 from item1

  3. #3

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Again think about what you are doing then look at what you have done. You should see your mistake.
    cant find my mistake sir
    Why are you looping through the listview and adding those items together before doing your update?
    i need to update 2 different items names with 2 different quantity
    if i dont loop then it will only update the first row and not both of them

    how can i update 2 different item names in the same list?
    i realy dont have a idea

  4. #4
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    You may need to loop.
    Read your code that you wrote. It loops through the list and adds up all the numbers
    It then exits the loop and updates item one with the total of all items sold.

    You really can't see that? Do you have any idea at all how a loop works? How adding works? How an update statement works? Haven't you did all of this 100 times already?

    If you want to update 2 different records with different values then obviously you need to execute two update statements one for each record you want to change.

    In other words you should not be adding any of those values together in your loop.
    You should be updating the database in your loop because that is what you want to do.

    This is really simple and you have did it before, makes me think that you have not understood most of the code you have posted to date nor any of the advice given.

    Then again you may be able to do it all in one update statement provided the right data exists to identify the records but that information is not contained in your posts.
    A loop will definitely work and at your level you should probably just do that and try to understand how and why it works.
    Last edited by DataMiser; Dec 25th, 2014 at 12:32 PM.

  5. #5

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    hey dm
    a update i know np
    an as you can see i did a loop however this looping thing considering a subitems a little new to me
    how can i do 1 update for each subitems that is in the list?
    what i need to change or add?
    Code:
    Dim A As Integer
            Dim Sum As Currency
            
            For A = 1 To LsVw.ListItems.Count
            Sum = Sum + CCur(LsVw.ListItems(A).SubItems(2))
                Next
                
            
            Dim S As String
            S = "Update Inventory set InvSold = InvSold + " & Sum & ","
            S = S & " InvRemain = InvRemain - " & Sum
            S = S & " Where InvItem = '" & LsVw.ListItems(1).SubItems(1) & "'"
            CN.Execute S
    you have to understand sir i am not that experience as you are
    i will appreciate if you help me to build the right code
    the next time i will have a similar problem

  6. #6

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    you mean like this?
    Code:
                Dim i As Integer
                Dim Sum As Currency
            For i = 1 To LsVw.ListItems.Count
            
            Sum = Sum + CCur(LsVw.ListItems(i).SubItems(2))
            
            StrSql = "Update Inventory set InvSold = InvSold + " & Sum & ","
            StrSql = StrSql & " InvRemain = InvRemain - " & Sum
            StrSql = StrSql & " Where InvItem = '" & LsVw.ListItems(1).SubItems(1) & "'"
            CN.Execute StrSql
    
                Next
    to put it in the loop?

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Much closer

    But you are still only updating Item1
    LsVw.ListItems(1)

    You need to use your loop variable there instead of 1 so that you are actually updating the item you want rather than always updating the first item.

    btw using the subitems part is no different than anything else

    All you are doing is using a variable it doesn't matter it that variable is in the form of a user created variable like MyVar, a textbox, a label, a listitem or subitem or any other type of variable data container it is all the same exact thing.


    Edit: I almost missed that Sum addition part in there. You need to get rid of that and use the number i.e. the subitem data.
    Last edited by DataMiser; Dec 25th, 2014 at 03:06 PM.

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Code:
    Sum = Sum + CCur(LsVw.ListItems(i).SubItems(2))
    Think about this a bit, What do you think that is going to do at run time.

    Lets say there is 5 items in the list and you sold 1 of item 1, 3 of item 2, 5 of item 3 and 4 of item 5

    Code:
    StrSql = "Update Inventory set InvSold = InvSold + " & Sum & ","
            StrSql = StrSql & " InvRemain = InvRemain - " & Sum
            StrSql = StrSql & " Where InvItem = '" & LsVw.ListItems(1).SubItems(1) & "'"
            CN.Execute StrSql
    This code is only going to update item 1 because it is hard coded to do so but how many do you think it will subtract of item one given the sample numbers above when it is done?

  9. #9

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    i need the Sum to count the item quantity

  10. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    The loop will run 5 times
    first pass it will subtract 1
    next pass it will subtract 4 i.e. [1+3]
    next pass it will subtract 9 [4+5]
    next pass it will subtract 9 [9+0]
    last pass it will subtract 13 [9+4]
    so when it is done it will have removed 36 from item 1 but you only sold 1

  11. #11

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    i see this is a guessing game
    i dont know

  12. #12
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Quote Originally Posted by salsa31 View Post
    i need the Sum to count the item quantity
    No you don't not unless all the items in the listview are the same item which I highly doubt

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    You see one problem here is that you did not tell us what those items are to you subitems(2) means something, to me it means nothing at all it is just a value of one column.
    I assume that holds the quantity sold but I can only guess because you did not say.

    And no this is not a guessing game for you, it is for us because only you know what those values are and exactly what you are trying to do. There is no guessing required just a little bit of simple logic.

  14. #14

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    subitems(2) holds the quantity yes

  15. #15
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    And are the list items different items or are they all the same item? Your first post appears to have different items
    Item 1 - Tv 5 units
    Item 2 - Phone - 10 units
    So why would you add 5+10 ??????

  16. #16
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Code:
    Dim i As Integer
        Dim Sum As Currency
        For i = 1 To LsVw.ListItems.Count
            
            Sum = Sum + CCur(LsVw.ListItems(i).SubItems(2))
            
            StrSql = "Update Inventory set InvSold = InvSold + " & Sum & ","
            StrSql = StrSql & " InvRemain = InvRemain - " & Sum
            StrSql = StrSql & " Where InvItem = '" & LsVw.ListItems(1).SubItems(1) & "'"
            CN.Execute StrSql
            
        Next
    See the parts in red and see if you can figure out what needs to be done to them

    btw I strongly recommend not using i as a loop variable, looks to much like 1 or l and is easy to miss a typo. I never use it largely for that reason.

  17. #17

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    i dont know why
    listitems(1) can have a different item name
    subitems(2) holdsthe quantity for each listitems(1)

    so item name Tv Quantity 5
    item name Phone Quantity 10
    so in the inventory i need to update the item name and its quantity

  18. #18

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    i want to update each item and its quantity
    there cannot be the same item name twice in the list
    how do i update a diffrent item and its quantity in 1 string?
    is it possible without a loop?

  19. #19
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    So are you saying that you added those numbers together without knowing why you added them together?
    Were you just typing random things hoping it would work?

    Think. if you add 5+10 you get 15 then your update is going to say remove 15 from the item when you only sold a total of 15 across two items, worse still since it is in a loop it is going to subtract 5 first and then another 15 for a total of 20.

    Surely you can see that after it has been explained to you. I've did everything but write the code for you at this point.

    I'll give you some time to try and figure out what needs to be done to those places I marked in red.

  20. #20
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,930

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Quote Originally Posted by salsa31 View Post
    i want to update each item and its quantity
    there cannot be the same item name twice in the list
    how do i update a diffrent item and its quantity in 1 string?
    is it possible without a loop?
    Salsa, when a problem bites you (and you're "lost somehow") - what helps me
    in such cases is, when I "throw more speaking variables at the problem".

    I've did that in the example below (which will still contain the errors you introduced)
    but hopefully it will be easier now, to find them...

    Code:
    Private Sub Form_Click()
    'your former Variables, which are still in use in this example
    Dim i As Long, Sum As Currency, StrSql As String
    
    'a few new introduced Variables, to make it a bit more readable
    Dim Rows As ListItems, ItemNam As String, ItemVal As Currency
    
      Set Rows = LsVw.ListItems
      
      For i = 1 To Rows.Count
        ItemNam = Rows(1).SubItems(1)
        ItemVal = Rows(i).SubItems(2)
        
        Sum = Sum + ItemVal
        
        StrSql = "Update Inventory set InvSold = InvSold + " & Sum & ","
        StrSql = StrSql & " InvRemain = InvRemain - " & Sum
        StrSql = StrSql & " Where InvItem = '" & ItemNam & "'"
        Debug.Print StrSql
    '   CN.Execute StrSql
      Next
    End Sub
    Olaf

  21. #21

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    sry i still dont get it Schmidt
    dm tnx for trying to help but i dont get it
    i realy dont understand

  22. #22
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Ok in your first post you have two items in the list
    Item 1 - Tv 5 units
    Item 2 - Phone - 10 units
    So you want to remove 5 TVs from stock and add 5 to the number of TVs sold
    You also want to remove 10 phones from stock and add 10 phones to phones sold

    Then you have this code
    Code:
    Dim i As Integer
        Dim Sum As Currency
        For i = 1 To LsVw.ListItems.Count
            
            Sum = Sum + CCur(LsVw.ListItems(i).SubItems(2))
            
            StrSql = "Update Inventory set InvSold = InvSold + " & Sum & ","
            StrSql = StrSql & " InvRemain = InvRemain - " & Sum
            StrSql = StrSql & " Where InvItem = '" & LsVw.ListItems(1).SubItems(1) & "'"
            CN.Execute StrSql
            
        Next
    With two items in the list this will execute twice. The first time Sum=0 so when you add 5 to it it becomes 5
    So far you're ok even though there is already an error in the logic where you add 5
    then you update item1 in the database adding 5 to number sold and removing 5 from stock
    Still ok even though we have two logic errors in that code.

    Now back to the top. Item2 sold 10 items so you add that to sum which is already 5 so now it is 15 'see a problem there?
    Then you get to the update which is going to update item1 [because it is hard coded to always update item 1]
    It is going to add 15 more to the number of TVs sold and remove 15 more TVs from stock.
    It will not do anything with the phones.

    So your DB will say that you sold 20tvs and 0 phones

    I marked both errors in red in a previous post all it needs is a minor change and it will work correctly. Surely you can do this yourself and maybe learn something from it?

  23. #23
    PowerPoster
    Join Date
    Jun 2013
    Posts
    4,930

    Re: inventory updates same name Twice Although there are 2 different item names why?

    Quote Originally Posted by salsa31 View Post
    sry i still dont get it Schmidt
    dm tnx for trying to help but i dont get it
    i realy dont understand
    Then let me write the routine shorter (not yet building any SQL):

    Code:
    Private Sub Form_Click()
    'your former Variables, which are still in use in this example
    Dim i As Long, Sum As Currency, StrSql As String
    
    'a few new introduced Variables, to make it a bit more readable
    Dim Rows As ListItems, ItemNam As String, ItemVal As Currency
    
      Set Rows = LsVw.ListItems
      
      For i = 1 To Rows.Count
        ItemNam = Rows(1).SubItems(1)
        ItemVal = Rows(i).SubItems(2)
        
        Debug.Print ItemNam, ItemVal 'just print out, what we got in each Row of our loop
      Next
    End Sub
    If you use that shortened loop in your code:
    - what's printed out in the immediate-Window?
    - are you happy with these results of the simple looping over your ListView-Rows?

    Olaf
    Last edited by Schmidt; Dec 25th, 2014 at 07:00 PM.

  24. #24
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    13,285

    Re: inventory updates same name Twice Although there are 2 different item names why?

    I'm going to go do something else so if you have not figured this out yet I will leave you with the solution.

    Look at the post where I marked the two parts in red.

    The first red part needs to be deleted as that is causing problems. You should not be adding there. You want that number to be 5 the first time through and 10 the second time through you do not want to keep adding to it because that will make a mess that gets worse and worse as more items are in the list.

    The second part you have the number 1 it needs to be the variable i as is it will always update item 1 but you want it to update the list items 1 by one so it would do item 1 and then item 2

    If you make those two changes then it will correctly update 5 for the first item and 10 for the second item.

    Merry Christmas

  25. #25

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    schmidt i get error type mismatch here [ Set Rows = LsVw.ListItems]
    dm you mean like this?
    Code:
    Dim i As Integer
        Dim Sum As Currency
        For i = 1 To LsVw.ListItems.Count
            
            Sum = (LsVw.ListItems(i).SubItems(2))
            
            StrSql = "Update Inventory set InvSold = InvSold + " & Sum & ","
            StrSql = StrSql & " InvRemain = InvRemain - " & Sum
            StrSql = StrSql & " Where InvItem = '" & LsVw.ListItems(i).SubItems(1) & "'"
            CN.Execute StrSql
            
        Next

  26. #26

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    i think it is ok now sir

  27. #27

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    5,144

    Re: inventory updates same name Twice Although there are 2 different item names why?

    schmidt tnk you for your help
    DM tnx for your help
    i appreciate your time friends
    Merry Christmas and god bless

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width