Avatar of kootenay_tech
kootenay_techFlag for Canada asked on

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

Open in new window

Microsoft AccessDatabasesMicrosoft Applications

Avatar of undefined
Last Comment
kootenay_tech

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
telyni19

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
kootenay_tech

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.

telyni19

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

Open in new window

ASKER
kootenay_tech

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck