VBA Best way to update tables and records listboxes

Thanks for the input and sorry for the typing mistakes.
No, I am not trying to move and use all the weight.It will be the user's choice how much material to use from each lot in listbox1 ( and element table behind).

The user will choose to use all- or only part- of the weight in stock   entering it in a textbox.
 I have therefore 3 cases for every record:
1: all the weight in stock is used and the status goes from Inventory to Used [ so the lot shouldn't show anymore in the listbox1 ,which consists only of "Inventory" items].
2: only part of the weight is used and transferred from list 1 to list 2. in this case i need to calculate the weight used and the weight left,  update the weight left in the original record and and move only the weight to be used in the listbox2
3: the user just input the wrong amount and he gets a message

I will be fine with 2 buttons, one to move weight and lots,one to update the records behind the listboxes.I need to keep listbox2( and all its records) in a table (  created ad-hoc) to document the weight used and the lot number etc. and for future uses.

Right, okay. I still think WeightCharge is backwards though. Let's think about an example. If Weight is 10 (the available weight), and I want to use 2 of that, then I enter 2 in theMe!Wt2Add2 textbox on the form, right? Then WeightCharge = ((List1.Column(3)) - Me!Wt2Add2). That means WeightCharge is 10 - 2 = 8. So then when we add the item to List2, we're adding an item with a weight of 8. Surely you want to add an item with a weight of 2, to indicate that we've used up 2 of the 10 available. So that would mean using Me!Wt2Add2 unless the entered value is greater than the available (case 3 in your list), in which case you just give an error message. Cases 1 and 2 can be treated the same when it comes to updating List2 because Me!Wt2Add2 is equal to Weight for Case 1.

I thought about it a little more, and how you want to accomplish the results depends on how you're generating the listbox. It's far easier to pull data into a listbox based on a query from your datatable. But if you do it this way, then it's much harder to change the information later. Much easier to go ahead and change the data in the table and requery the box.

Of course, that means that List2 is only a display of the changes that have already been made. Instead of one button to move and another to make changes, you'll probably want one button to make changes in the forward direction, acting on List1, and one button to make changes in the backward direction, acting on List2. Once you have one working though, it should be pretty easy to get the other working.

See the attached code for my suggestion on how the "forward" button could work. (I've renamed it RemoveWeight to express how it is removing or using up inventory, but you can call it whatever you want, of course.) I've made some revisions and added comments so you can see why I did what I did, I hope.

Private Sub RemoveWeight_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
    Dim strSQL As String

'Change these constants to the corrent names of the actual tables
Const tblInv As String = "tblInventoryData"
Const tblChange As String = "tblWeightChanges"

'Error handling
On Error GoTo ErrSkip

'Test for blank selection
If IsNull(List1.Column(0)) Or IsEmpty(List1.Column(0)) Then
  MsgBox "Select an item first", vbCritical, "No item selected"
  Exit Sub
End If

'Pull selected item current details
intRecID = List1.Column(0)
strLotNo = List1.Column(1)
strGrade = List1.Column(2)
Weight = List1.Column(3)

'Test for blank weight entry
If If IsNull(Me!Wt2Add2) Or IsEmpty(Me!Wt2Add2) Then
  MsgBox "Please enter the weight you wish to add"
  Exit Sub
End If

'Test for weight higher than available
If Me!Wt2Add2 > Weight Then
  MsgBox ("You have entered a weight bigger than what we have in stock. For the selected item, the weight must be less than " & Weight & ".")
  Exit Sub 
Else
  List2.AddItem Item:=intRecID & ";" & strLotNo & ";" & strGrade & ";" & Me!Wt2Add2
End If

'Update the inventory data based on the indicated change
If Me!Wt2Add2 < Weight Then  'Partial move, the SQL command is complete by just updating the weight
  strsql = "UPDATE " & tblInv & " SET Weight = " & Weight - Me!Wt2Add2
Else 'The case of available weight < entered value is already handled by the previous if-statement, so this is the case of using up available inventory
  strsql = "UPDATE " & tblInv " SET Weight = " & Weight - Me!Wt2Add2 & ", Status = 'Used'"
End If

'Add the conditions to choose the correct item to update
strsql = strsql & " WHERE RecID = " & intRecID & " AND strLotNo = '" & strLotNo & "';"

'Run the generated SQL command
DoCmd.RunSQL(strsql)

'Add a record for the change to the table for weight changes
  strsql = "INSERT INTO " & tblChange & "(RecID, LotNo, Grade, WeightChange) VALUES (" & intRecID & ", '" & strLotNo & "', '" & strGrade & "', " & Me!Wt2Add2 & ");"
  DoCmd.RunSQL(strsql)

'Requery List1 to reflect the change in data
List1.Requery

ExitErr:
'Cleanup after an error can go here
Exit Sub

ErrSkip:
    MsgBox Err.Description
    Resume ExitErr
End Sub

Select allOpen in new window

I think u r great.

I worked on your first advice and it's a hit.
Yes, the weight were reversed and it was an easy mistake to correct (I just input the wrong textbox source in the code).
 My listbox2 is very important because it will be the input of a third listbox : they all mirror  factory purifying process data let's say the first is the raw material, the second a purified material and so on.

I went with the DoCmd.RunSQL (statement) and it works wonderfully.
In 2 lines of code I achieved what 13 lines of recordsets still couldn't do.
I went on and also updated the status.
Now I am thinking of using it for the Create table (out of the listbox2).

Thank you so very much