if item is already exist in the listbox add quantity of that item else add item and quantity.

[rajudh]

Hello,

I have one form named Form1, in that form combo box named CmbProductName for adding products, text box named TxtQty and listbox named ListBox1 and AddButton for adding both.
I have following code

Private Sub AddButton_Click()


ListBox1.ColumnCount = 2


 Dim i As Integer
 Dim itemFound As Boolean
 Dim currentQuantity As Double
 Dim previousQuantity As Double


    ' Check if the item already exists
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Column(0, i) = CmbProductName Then ' Assuming item name is in the first column
            itemFound = True
            Exit For
        End If
    Next i


    If itemFound Then
        ' Item exists, update the quantity
        previousQuantity = Me.ListBox1.Column(1, i) ' Assuming quantity is in the second column
        currentQuantity = previousQuantity + TxtQty.Value
        Me.ListBox1.Column(1, i) = currentQuantity 'Update quantity
    Else
        ' Item doesn't exist, add it with the given quantity
        Me.ListBox1.AddItem (CmbProductName.Value & ";" & TxtQty.Value)
        'Me.ListBox1.Column(1, Me.ListBox1.ListCount - 1) = TxtQty.Value 'Set quantity in second column
    End If
    
End Sub

when I run this code error message is displayed as " Runtime error '424' object required."
when debug is run the error is in line -
Me.ListBox1.Column(1, i) = currentQuantity 'Update quantity



How to solve this error

Thanks

[Welshgasman]

Upload the DB with enough to see the issue.
I cannot see why that error from what you have posted.

[moke123]

Are there underlying tables for the products and quantities?
You appear to be using a value list.

If you need to add products or quantities you would add them to the underlying tables and requery/re-populate the list box.

[jojowhite]

maybe something like this:

Private Sub AddButton_Click()


Listbox1.ColumnCount = 2




 Dim i As Integer, j As Integer
 Dim itemFound As Boolean
 Dim currentQuantity As Double
 Dim previousQuantity As Double
 Dim arr() As Variant


    ' Check if the item already exists
    For i = 0 To Me.Listbox1.ListCount - 1
        If Me.Listbox1.Column(0, i) = CmbProductName Then ' Assuming item name is in the first column
            itemFound = True
            Exit For
        End If
    Next i




    If itemFound Then
        ' Item exists, update the quantity
        previousQuantity = Me.Listbox1.Column(1, i) ' Assuming quantity is in the second column
        currentQuantity = previousQuantity + TxtQty.Value
        
        ReDim arr(0 To Me.Listbox1.ListCount - 1)
        For j = 0 To Me.Listbox1.ListCount - 1
            If j = i Then
                arr(j) = Me.Listbox1.Column(0, i) & ";" & currentQuantity
             Else
                arr(j) = Me.Listbox1.Column(0, j) & ";" & Me.Listbox1.Column(1, j)
             End If
        Next
        ' delete previous items from listbox
        With Me.Listbox1
            For i = .ListCount - 1 To 0 Step -1
                .RemoveItem i
            Next i
        End With
                
        ' re-add from array
        For i = 0 To UBound(arr)
            Me.Listbox1.AddItem arr(i)
        Next
        
    Else
        ' Item doesn't exist, add it with the given quantity
        Me.Listbox1.AddItem (CmbProductName.Value & ";" & TxtQty.Value)
        'Me.ListBox1.Column(1, Me.ListBox1.ListCount - 1) = TxtQty.Value 'Set quantity in second column
    End If
    
End Sub

[moke123]

Why would you want to manipulate the list box rowsource without saving the information to a table?

[mike60smart]

Hi
Seems a strange process to have Code to add a product using a Combobox.
Normal process is select a Product and then add a Quantity required using Bound Controls.
Can you upload a zipped copy of the database?

[orange]

rajudh,
The thread seems to show some confusion. It would be helpful to you and readers if you could step back and tell us in simple terms what it is you are trying to solve/accomplish. You have code that shows how you have attempted something. But for clear communication, I recommend you give us the 30,000 ft overview of your environment, the business issue to be assisted by automation and perhaps an example or two showing a before and after effect.

[rajudh]

Thank you for your reply
I am trying to solve if user accidentally insert one product twice, instead of saving same product multiple times the quantity should be added to that item.
I am uploading my database.
Version is Access 2013.
thanks

[Welshgasman]

Here is what ChatGPT offers.

Private Sub UpdateListBoxValue(oldValue As String, newValue As String)
    Dim items() As String
    Dim i As Long
    Dim found As Boolean
    Dim updatedList As String


    ' Get and split the current RowSource
    items = Split(Me.lstFruits.RowSource, ";")
    found = False


    ' Loop through items and update the matching one
    For i = LBound(items) To UBound(items)
        If Trim(items(i)) = oldValue Then
            items(i) = newValue
            found = True
            Exit For
        End If
    Next i


    ' If not found, you can decide to add newValue to the list
    If Not found Then
        ReDim Preserve items(UBound(items) + 1)
        items(UBound(items)) = newValue
    End If


    ' Join the array back into a string
    updatedList = Join(items, ";")
    
    ' Set the new RowSource
    Me.lstFruits.RowSource = updatedList
End Sub

So basically put it into a string, check if it exists, update that index value, and put back into Valuelist.
You would need to use Step 2 and add 1 to the item index being updated as you have two columns. However, walk your code to confim that.

Would be so much easier using a table/query recordset.

[Welshgasman]

This appears to work.

Private Sub AddButton_Click()


Call UpdateListBoxValue(Me.CmbProductName, Me.TxtQty)




End Sub


Private Sub UpdateListBoxValue(OldValue As String, NewValue As Integer)
    Dim items() As String
    Dim i As Long
    Dim found As Boolean
    Dim updatedList As String




    ' Get and split the current RowSource
    items = Split(Me.ListBox1.RowSource, ";")
    found = False




    ' Loop through items and update the matching one
    For i = LBound(items) To UBound(items) Step 2
        If Trim(items(i)) = OldValue Then
            items(i + 1) = items(i + 1) + NewValue
            found = True
            Exit For
        End If
    Next i




    ' If not found, you can decide to add newValue to the list
    If Not found Then
        ReDim Preserve items(UBound(items) + 1)
        items(UBound(items)) = NewValue
    End If




    ' Join the array back into a string
    updatedList = Join(items, ";")
    
    ' Set the new RowSource
    Me.ListBox1.RowSource = updatedList
End Sub

Amend as you see fit. I just wanted to see if it worked as ChatGPT has given me some code that does not. This time they were spot on.

[mike60smart]

Hi
You should have a Main Form based on tblInvoiceNew
with a Subform based on tblInvoiceDetails as shown in the attached screenshot.

You should not be using Lookup fields in tables.

[jojowhite]

see frmInvoice.

[rajudh]

Thank you JojoWhite for your solution, and your first code is also working

Thank you all for your guidance.