VBA Best way to update tables and records listboxes

I have a table with elements (record ID,lot number, weight, status...).
In a form, I created a listbox through a query that shows only element with a certain status.
I then choose an element and –at the press of a button-move it all ( or part of its weight) to a second listbox.( I will also need at the end to move it back to the first listbox , in case the user changes his  mind). The first listbox Row Source Type is Query/Table while the second has Row Source Type = Values. This limit my possibility to use RemoveItem in the first listbox.
I need to update the first listbox ( because if I use it al,l the weight in the first list box will go down to “0” and the status will go from” Inventory” to “Used”. And I also need to update the original table from where the same first listbox was created out of.
Is there a easier way than to use Recordsets?
Here is my code so far:it runs and does what i need , populating the second listbox. Now I need to update the table behind and the weight and status of the record I worked on and I am puzzled on how to do it.

Private Sub ADDorREMOVE_Click()
    Dim Weight As Long
    Dim strLotNo As String
    Dim strGrade As String
   Private Sub ADDorREMOVE_Click()
    Dim Weight As Long
    Dim strLotNo As String
    Dim strGrade As String
    Dim intRecID As Long
    Dim Wt2Add2 As Long
   Dim WeightCharge As Long
 If IsNull(List1.Column(0)) Or IsEmpty(List1.Column(0)) Then
        MsgBox "Select an item first", vbCritical, "No item selected"
        Exit Sub
        End If
        
        intRecID = List1.Column(0)
         strLotNo = List1.Column(1)
         strGrade = List1.Column(2)
         Weight = List1.Column(3)
         
          If IsNull(Me!Wt2Add2) Or IsEmpty(Me!Wt2Add2) Then
         MsgBox "Please enter the weight you wish to add"
         Exit Sub
         End If
 If Me!Wt2Add2 = Weight Then
        List2.AddItem Item:=(((intRecID & ";" & strLotNo & ";" & strGrade & ";" & Weight)))
        
 
        
        
        ElseIf Me!Wt2Add2 - Weight > 0 Then
        MsgBox ("You have entered a weight bigger than what we have in stock. Please re-enter lower weight")
        
        
        ElseIf Me!Wt2Add2 - List1.Column(3) < 0 Then
        WeightCharge = ((List1.Column(3)) - Me!Wt2Add2)
        List2.AddItem Item:=(((intRecID & ";" & strLotNo & ";" & strGrade & ";" & WeightCharge)))
         
        End If
        
        
        
        
        
        
        
        

        

    
   'List1.RemoveItem
    
ADDorREMOVE_Click_Exit:

    Exit Sub

Select all Open in new window