updating recordset after removing items from recordset list.

[mdpems]

Hi,
I have a button that will add items to a list from another table and then total the cost of these items by clicking a total button. The problem I am having is that when I remove an item from the list it does not update the recordset so it continues to total the items even though the items are no longer there. Is there something wrong with my code? Help Please!!!! Here is the code I am using in my form.

Thanks in advance.

Private Sub cmdAdd_Click()
If Nz(TextQuantity.Value, "") = "" Then TextQuantity.Value = "1"
If Nz(cmbItem.Value, "") <> "" Then
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Cost FROM [Medical Supplies] WHERE Item = '" & removeApostrophes(cmbItem.Value) & "';")
If Not rs.EOF Then total = total + (CSng(rs!Cost) * CInt(TextQuantity.Value))
lstOrder.AddItem (TextQuantity.Value & "x " & cmbItem.Value)
End If

End Sub


Private Sub cmdremove_Click()
lstOrder.RemoveItem Index:=varItem

On Error GoTo 0

End Sub

[puppydogbuddy]

Hi,
I have a button that will add items to a list from another table and then total the cost of these items by clicking a total button. The problem I am having is that when I remove an item from the list it does not update the recordset so it continues to total the items even though the items are no longer there. Is there something wrong with my code? Help Please!!!! Here is the code I am using in my form.

Thanks in advance.

Private Sub cmdAdd_Click()
If Nz(TextQuantity.Value, "") = "" Then TextQuantity.Value = "1"
If Nz(cmbItem.Value, "") <> "" Then
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT Cost FROM [Medical Supplies] WHERE Item = '" & removeApostrophes(cmbItem.Value) & "';")
If Not rs.EOF Then total = total + (CSng(rs!Cost) * CInt(TextQuantity.Value))
lstOrder.AddItem (TextQuantity.Value & "x " & cmbItem.Value)
End If

End Sub


Private Sub cmdremove_Click()
lstOrder.RemoveItem Index:=varItem

On Error GoTo 0

End Sub

I don't see where anything is assigned to varitem in your code.

[mdpems]

I don't see where anything is assigned to varitem in your code.

That is probably my problem. This is the default remove item that is set up in Access. I am not sure what I need to put here code wise. I have tried putting index:=rs but this did not produce any results. Thanks for helping.

[puppydogbuddy]

That is probably my problem. This is the default remove item that is set up in Access. I am not sure what I need to put here code wise. I have tried putting index:=rs but this did not produce any results. Thanks for helping.

try this to remove one item:
lstOrder.RemoveItem lstOrder.ListIndex

or this to remove all items:
Do Until lstOrder.ListCount = 0
lstOrder.RemoveItem (0)
Loop

[mdpems]

try this to remove one item:
lstOrder.RemoveItem lstOrder.ListIndex

or this to remove all items:
Do Until lstOrder.ListCount = 0
lstOrder.RemoveItem (0)
Loop

I tried both of these. The first gives me a run time error stating invalid procedure call or argument. The second removes all items as stated but I can not retotal the items. The total still reflects as if all items were still there even though they are not. Thanks a bunch. I feel like I am close.

[puppydogbuddy]

I tried both of these. The first gives me a run time error stating invalid procedure call or argument. The second removes all items as stated but I can not retotal the items. The total still reflects as if all items were still there even though they are not. Thanks a bunch. I feel like I am close.

Here you go. Your original code was pretty close. According to this link the general syntax for removing a single item using the RemoveItem method is as shown below, but be aware that this method can only be used if the Listbox's row source property is set to value list
:
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

General Syntax
------------------------
' Remove the list box item and set the return value
' to True, indicating success.
ctrlListBox.RemoveItem Index:=varItem
RemoveListItem = True

Based on the above your syntax would be:

lstOrder.RemoveItem Index:=varItem
RemoveListItem = True

[mdpems]

Here you go. Your original code was pretty close. According to this link the general syntax for removing a single item using the RemoveItem method is as shown below, but be aware that this method can only be used if the Listbox's row source property is set to value list
:
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx

General Syntax
------------------------
' Remove the list box item and set the return value
' to True, indicating success.
ctrlListBox.RemoveItem Index:=varItem
RemoveListItem = True

Based on the above your syntax would be:

lstOrder.RemoveItem Index:=varItem
RemoveListItem = True

Should this reset the total? For some reason no matter if I remove the item from the list or not the total still is reflecting as if the item is still there. Here is the code for my total button that I have. Sorry about the previous post. I did not get a response for several days so I thought that maybe my original post was not specific enough. Thanks for your help.

Private Sub cmdTotal_Click()
TextTotal.Value = Round(total, 2)

End Sub

[puppydogbuddy]

Should this reset the total? For some reason no matter if I remove the item from the list or not the total still is reflecting as if the item is still there. Here is the code for my total button that I have. Sorry about the previous post. I did not get a response for several days so I thought that maybe my original post was not specific enough. Thanks for your help.

Private Sub cmdTotal_Click()
TextTotal.Value = Round(total, 2)

End Sub

I am not sure, but here are a few things you can try:
1. try requerying or refreshing your listbox and see if that helps.
Private Sub cmdTotal_Click()
lstOrder.Requery
TextTotal.Value = Round(total, 2)

2. try recalcuating the total
total.Recalc
TextTotal.Value = Round(total, 2)

3. try requerying the recordset after the total is computed in the recordset
rs.requery

[mdpems]

I am not sure, but here are a few things you can try:
1. try requerying or refreshing your listbox and see if that helps.
Private Sub cmdTotal_Click()
lstOrder.Requery
TextTotal.Value = Round(total, 2)

2. try recalcuating the total
total.Recalc
TextTotal.Value = Round(total, 2)

3. try requerying the recordset after the total is computed in the recordset
rs.requery

None of that is working to requery the recordset. Is there anyway that I can reset the recordset and start over without reseting all the information in the form. What I am hoping to do is keep from having to reload the whole form.

[puppydogbuddy]

None of that is working to requery the recordset. Is there anyway that I can reset the recordset and start over without reseting all the information in the form. What I am hoping to do is keep from having to reload the whole form.

You can try the the .Edit and .Update methods of updating the recordset. However, I believe requerying the listbox should have worked. If the requery is not working for you then the problem is probably where I suggested that you place the code, try placing the requery after the end of your main recordset loop and see if that helps.

This link has several free downloadable databases that contain listboxes that have calculations.that are requeried. Hope this helps.

http://www.cse.dmu.ac.uk/~mcspence/E...0databases.htm