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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
ASKER
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 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
ASKER
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.