Delete entire row in a table from Listbox

[lgatinoi]

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) & ")"

I Actually have the error 438 on
For Each varSelect In Me.ListInputBudget

Even with the modification you ask me to do

Thanks

I've done a modification to your code and now I have a new error msg (Syntax error (missing operator) in query expression 'BIID in ()' )

Dim strIDs As String
Dim strSQL As String
strSQL = "DELETE * FROM [W1_Budget Input]"
Dim I
For Each I In Me.ListInputBudget.ItemsSelected
strIDs = strIDs & Me.ListInputBudget.ItemData(I) & ","
Next

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

strSQL = strSQL & strIDs

CurrentDb.Execute strSQL, dbFailOnError

 

[boblarson]

I Actually have the error 438 on
For Each varSelect In Me.ListInputBudget

Even with the modification you ask me to do

Thanks

I've done a modification to your code and now I have a new error msg (Syntax error (missing operator) in query expression 'BIID in ()' )

Dim strIDs As String
Dim strSQL As String
strSQL = "DELETE * FROM [W1_Budget Input]"
Dim I
For Each I In Me.ListInputBudget.ItemsSelected
strIDs = strIDs & Me.ListInputBudget.ItemData(I) & ","
Next

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

strSQL = strSQL & strIDs

CurrentDb.Execute strSQL, dbFailOnError

Okay let's work through this.

A Runtime error 438 means "Object Doesn't Support This Property."

So, the question is - is your list box set up for multiple selections or have you just used the default which is to select one at a time?

Second, when you "modified" the code you removed the SPACE at the end of this line:
strSQL = "DELETE * FROM [W1_Budget Input]"

Which is IMPORTANT TO LEAVE:

strSQL = "DELETE * FROM [W1_Budget Input] "

or else your SQL String will run on

"DELETE * FROM [W1_Budget Input]WHERE BIID IN....etc.

And it should read
"DELETE * FROM [W1_Budget Input] WHERE BIID IN....etc.

So you have to be careful with building SQL that you remember to leave the correct spaces.

now I have a new error msg (Syntax error (missing operator) in query expression 'BIID in ()' )

This is showing that no BIID is being selected in the listbox because the listbox row source doesn't probably even have that in the Row Source since you just added it. You will need to add it to the listbox's Row Source and I would suggest making it the first column.

 

[lgatinoi]

Okay let's work through this.

A Runtime error 438 means "Object Doesn't Support This Property."

So, the question is - is your list box set up for multiple selections or have you just used the default which is to select one at a time?

Second, when you "modified" the code you removed the SPACE at the end of this line:
strSQL = "DELETE * FROM [W1_Budget Input]"

Which is IMPORTANT TO LEAVE:

strSQL = "DELETE * FROM [W1_Budget Input] "

or else your SQL String will run on

"DELETE * FROM [W1_Budget Input]WHERE BIID IN....etc.

And it should read
"DELETE * FROM [W1_Budget Input] WHERE BIID IN....etc.

So you have to be careful with building SQL that you remember to leave the correct spaces.


This is showing that no BIID is being selected in the listbox because the listbox row source doesn't probably even have that in the Row Source since you just added it. You will need to add it to the listbox's Row Source and I would suggest making it the first column.

Thanks! It almost work. I have multi selection option on and follow up carefully your VBA code.

Now it deletes one but only one line even if I select 3 or more line It always delete the last line of the selection

Thanks

 

[boblarson]

Set a breakpoint at the top of the code and then use F8 to follow the code and see what it does and where and what values are being pulled.

If all else fails, do a COMPACT AND REPAIR, and then ZIP the File (right-click on the file and select SEND TO > COMPRESSED FOLDER and then hopefully it will be below 2Mb and you can upload it. If not, take a copy and delete all of the items except for those used on that form (all tables not associated with that form and listbox and all other forms (except subforms if you have them), and reports. Then do the compact and repair and zip and see if that is below the threshold to upload here.

 

[lgatinoi]

Set a breakpoint at the top of the code and then use F8 to follow the code and see what it does and where and what values are being pulled.

If all else fails, do a COMPACT AND REPAIR, and then ZIP the File (right-click on the file and select SEND TO > COMPRESSED FOLDER and then hopefully it will be below 2Mb and you can upload it. If not, take a copy and delete all of the items except for those used on that form (all tables not associated with that form and listbox and all other forms (except subforms if you have them), and reports. Then do the compact and repair and zip and see if that is below the threshold to upload here.

Here you can find an example what I'm trying to do

A big Thanks for your help!

 

[boblarson]

Okay, found the problem. It is so much easier for me when I can look at the actual stuff.

We need to modify this line:

For Each varSelect In Me.ListInputBudget

to this:

For Each varSelect In Me.ListInputBudget[COLOR=red][B].ItemsSelected[/B][/COLOR]

Somehow I dropped that and that is the problem. It now works for me with that added.

 

[lgatinoi]

Okay, found the problem. It is so much easier for me when I can look at the actual stuff.

We need to modify this line:

For Each varSelect In Me.ListInputBudget

to this:

For Each varSelect In Me.ListInputBudget[COLOR=red][B].ItemsSelected[/B][/COLOR]

Somehow I dropped that and that is the problem. It now works for me with that added.

IT WORKS !!! Youhou

Thanks a lot and all the best!

 

[boblarson]

Whew! Glad we got that sorted.