We help IT Professionals succeed at work.
Get Started

VBA Best way to update tables and records listboxes

Last Modified: 2012-05-10
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



    Exit Sub

Open in new window

Watch Question
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE