Delete entire row in a table from Listbox

L

lgatinoi

Registered User.
Local time
Today, 06:15
Joined
Oct 26, 2007
Messages
27
Hello,

I have a multiple selection listbox (datasource on a tble). I try to delete the selected row from the list box with that VBA:

Private Sub Command61_Click()
On Error GoTo Err_Exit_Click
Dim i
For Each i In ListInputBudget.ItemsSelected
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("W1_Budget Input")
rs.Delete
Next i
rs.Close
Set rs = Nothing
Set db = Nothing
Me.ListInputBudget.Requery
Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub

Problem is that it does not delete the selected line but the first line and follow...

Many thanks for whom is helping me.
 
1. you can't delete forward like that.

2. If you delete one then the indexes of the rest change and the following code will not have the correct indexes of the selected items.

3. Deleting will delete from the table not just the listbox. Is that your desire?

4. To delete using ItemsSelected you would need to go in REVERSE Order if you were to do it one at a time. However I would suggest

5. that you build a string of ID's and then run a delete query ONCE to delete all of those items that were selected, if you really want to delete them from the table.

6. If you don't want to delete them from the table but just from the listbox you can build a string of ID's and then set a new query for the row source which excludes those numbers. You would need to declare your ID string as STATIC so that subsequent deletions would be ADDED to the string and not replace the string where the previous ones would then return because there was no history kept of which were deleted.
 
You have the code completely wrong, sorry, but how you have it now you are not telling Access which record to delete from the table.

Code: 
Dim i as integer

Dim Rs As DAO.Recordset

For Each i In ListInputBudget.ItemsSelected

    Set Rs = CurrentDb.OpenRecordset("Select * From Table where Id = " & me.ListInputBudget.Column(0,i)
    Rs.Delete
    Rs.Update
Next
Set Rs = Nothing

Aircode for brevity only.

.Column(0,i) 0 = the bound column which will be the PK in the Table
.Column(0,i) i = list box index number
 
Hello,

Thanks for your quick answer :-)

Yes actually I want it deleted from the table.

What do you mean by reverse order?

I'm not sure that I understand how to built a string of ID's
 
Hi Dcrake;

Thanks for the code. I've tried to run it but it faild.

For Each control variable must be variant or object. is my error message...

Sorry :o
 
lgatinoi said:
I'm not sure that I understand how to built a string of ID's
Click to expand...

You would do it this way:
Code: 
Dim strIDs As String
Dim strSQL As String
Dim varSelect As Variant
 
strSQL = "DELETE * FROM W1_Budget Input "
 
For Each varSelect In Me.ListInputBudget
     strIDs = strIDs & Me.ListInputBudget.ItemData(varSelect) & ","
Next
 
strIDs = "WHERE ID In (" & Left(strIDs, Len(strIDs)-1) & ")"
 
strSQL = strSQL & strIDs
 
CurrentDb.Execute strSQL, dbFailOnError
 
Last edited:
Hi Bob,

Also thanks for your code but I have the same error message...

:o
 
DCrake said:
You have the code completely wrong, sorry, but how you have it now you are not telling Access which record to delete from the table.

Code: 
Dim i as integer
 
Dim Rs As DAO.Recordset
 
For Each i In ListInputBudget.ItemsSelected
 
    Set Rs = CurrentDb.OpenRecordset("Select * From Table where Id = " & me.ListInputBudget.Column(0,i)
    Rs.Delete
    Rs.Update
Next
Set Rs = Nothing
Click to expand...
David:

That would still not work. The problem comes in that you are deleting FORWARDS and each time you delete one the ItemsSelected will change and lose 1 in the index. So, you need to run a reverse if you want that to work.

Code: 
Dim Rs As DAO.Recordset
Dim i As Integer 
 
For i = Me.ListInputBudget.ItemsSelected.Count To 0 Step -1
 
'...etc.
 
The same message as i got with the code of David

for each control variable must be Variant or Object
 
Hi bob,

Now I have a syntax error with FROM...

Dim strIDs As String
Dim strSQL As String
Dim varSelect As Variant

strSQL = "DELETE * FROM [W1_Budget input] "

For Each varSelect In Me.ListInputBudget.ItemsSelected
strIDs = strIDs & Me.ListInputBudget.ItemData(varSelect) & ","
Next

strIDs = "In (" & Left(strIDs, Len(strIDs) - 1) & ")"

strSQL = strSQL & strIDs

CurrentDb.Execute strSQL, dbFailOnError
 
Your Code have some problem. Modify the code as below and try again:

Code: 
Private Sub Command61_Click()
On Error GoTo Err_Exit_Click

Dim i,[B] item[/B]

For Each i In ListInputBudget.ItemsSelected
   [B] item = i.column(0).value[/B]
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("W1_Budget Input")

[B]rs.FindFirst "[FieldName] = " & item
If Not rs.NoMatch then
   rs.Delete
End If[/B]

Next i
rs.Close
Set rs = Nothing
Set db = Nothing
Me.ListInputBudget.Requery
Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub

You must read the Key Data from the List and use it to find the corresponding record from the Table. When the search and find is successful then apply the Delete action. Otherwise it will always delete the first record and there is no relationship with what you have selected in the list box.

If multiple list item selection is allowed then the database opening repeated action is not advisable, so the rs.close must be positioned before the next i statement. Otherwise there must be an Exit For statement immediately after the rs.Delete statement.
 
apr pillai said:
You must read the Key Data from the List and use it to find the corresponding record from the Table.
Click to expand...
I'm gonna disagree with this. It is very inefficient compared to using a single delete query.
 
YAP,

With the last solution I have Run time error 424...
 
lgatinoi said:
Hi bob,

Now I have a syntax error with FROM...
Click to expand...
Sorry, I missed something else. I forgot to add the words

WHERE ID

to the strIDs statement. Replace the word ID with whatever your field name which is the ID field. If it has spaces (hopefully not) use brackets around it like

[My ID]

if your ID field name was My ID

I went back and modified the code.
 
Sorry.... still doesn't work

The thing is that I dont have an unic ID.... I've tried to post an example of my table but it fails for security reason...
 
lgatinoi said:
Sorry.... still doesn't work

The thing is that I dont have an unic ID.... I've tried to post an example of my table but it fails for security reason...
Click to expand...
Well, you should have a primary key - UNIQUE ID for each table.

So, what is the data you are deleting and what is there that can identify a unique record to delete then?
 
boblarson said:
Well, you should have a primary key - UNIQUE ID for each table.

So, what is the data you are deleting and what is there that can identify a unique record to delete then?
Click to expand...

So i've updated my table with a unic ID but code still failed - Run time 438

Private Sub Command61_Click()
On Error GoTo Err_Exit_Click
Dim strIDs As String
Dim strSQL As String
Dim varSelect As Variant

strSQL = "DELETE * FROM [W1_Budget Input] WHERE BIID = " & Me.ListInputBudget.Column(0)

For Each varSelect In Me.ListInputBudget
strIDs = strIDs & Me.ListInputBudget.ItemData(varSelect) & ","
Next

strIDs = "WHERE ID In (" & Left(strIDs, Len(strIDs) - 1) & ")"

strSQL = strSQL & strIDs

CurrentDb.Execute strSQL, dbFailOnError
Me.ListInputBudget.Requery

Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub
 
lgatinoi said:
So i've updated my table with a unic ID but code still failed - Run time 438

Private Sub Command61_Click()
On Error GoTo Err_Exit_Click
Dim strIDs As String
Dim strSQL As String
Dim varSelect As Variant

strSQL = "DELETE * FROM [W1_Budget Input] WHERE BIID = " & Me.ListInputBudget.Column(0)

For Each varSelect In Me.ListInputBudget
strIDs = strIDs & Me.ListInputBudget.ItemData(varSelect) & ","
Next

strIDs = "WHERE ID In (" & Left(strIDs, Len(strIDs) - 1) & ")"

strSQL = strSQL & strIDs

CurrentDb.Execute strSQL, dbFailOnError
Me.ListInputBudget.Requery

Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub
Click to expand...

Get rid of this part - you're conflicting with the rest of the code:
WHERE BIID = " & Me.ListInputBudget.Column(0)

And change this part:
strIDs = "WHERE ID In (" & Left(strIDs, Len(strIDs) - 1) & ")"

to this:

strIDs = "WHERE BIID In (" & Left(strIDs, Len(strIDs) - 1) & ")"
 
So the code in its entirety should be:
Code: 
Private Sub Command61_Click()
On Error GoTo Err_Exit_Click
Dim strIDs As String
Dim strSQL As String
Dim varSelect As Variant

strSQL = "DELETE * FROM [W1_Budget Input] " 

For Each varSelect In Me.ListInputBudget
strIDs = strIDs & Me.ListInputBudget.ItemData(varSelect) & ","
Next

strIDs = "WHERE BIID In (" & Left(strIDs, Len(strIDs) - 1) & ")"

strSQL = strSQL & strIDs

CurrentDb.Execute strSQL, dbFailOnError
Me.ListInputBudget.Requery

Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub
 
You must log in or register to reply here.

Similar threads

S
Recordset.Requery still showing deleted record
Replies
6
Views
584
dalski
D
T
ListBox Doesn't Show Selected Values
Replies
10
Views
378
MajP
MajP
N
How to send filtered transactions in the MS Access Combo box with one Click
Replies
10
Views
146
CJ_London
C
N
How to ensure that the current stock control is update when the data appears on the subform in access
Replies
5
Views
783
Minty
Minty
R
VBA not working for saved query parameters
2
Replies
24
Views
609
RonPaii
RonPaii

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom