Removing multiple items from multiselect listbox

[r.harrison]

Hope someone can help.

I have a listbox on a form with letters waiting to be printed. I need to be able to remove multiple items at once using an SQL statement.

The main problem is that I need to include multiple columns in the SQL as I loop through the items.

What I need is something like lst_print.itemdata(i).column(0) but this doesn't work.

I've tried changing the bound column but this de-selects the remaining items.

Any ideas?

 

[llkhoutx]

The data source for you listbox should be a table or query. Add/Remove/Sort records in that table/query as is appropriate.

 

[vbaInet]

You haven't clearly explained your objective.

What does the following mean?

The main problem is that I need to include multiple columns in the SQL as I loop through the items.

What I need is something like lst_print.itemdata(i).column(0) but this doesn't work.

 

[r.harrison]

Sorry, should be more specific;

lst_print is a multiselect listbox on my form. I'm using a drag and drop function that I 'borrowed' from a forum, can't remember where now. When I drag from the listbox it should delete the items from the print table.

The function cycles through the listbox items and if they are selected it should remove them.

The problem I'm coming across is that the listbox.itemdata only returns the value in the Bound Column. I need to get the details from 3 different columns.



But don't worry, just figured it out as I was typing this reply! Need to have a Unique ID in my print table then just remove it using that.

Sorry, simple problem made more difficult by cutting corners!

 

[r.harrison]

Sorry, should be more specific;

lst_print is a multiselect listbox on my form. I'm using a drag and drop function that I 'borrowed' from a forum, can't remember where now. When I drag from the listbox it should delete the items from the print table.

The function cycles through the listbox items and if they are selected it should remove them.

The problem I'm coming across is that the listbox.itemdata only returns the value in the Bound Column. I need to get the details from 3 different columns.



But don't worry, just figured it out as I was typing this reply! Need to have a Unique ID in my print table then just remove it using that.

Sorry, simple problem made more difficult by cutting corners!

 

[vbaInet]

But don't worry, just figured it out as I was typing this reply! Need to have a Unique ID in my print table then just remove it using that.

Good job!

The function cycles through the listbox items and if they are selected it should remove them.

Look into the ItemsSelected method of the listbox control. It will narrow down the number of loops you have to do.

 

[r.harrison]

Excellent, that's what I was looking for!

Thanks vbaInet

 

[vbaInet]

You're welcome!

 

[r.harrison]

That works perfectly:

For Each i In Me.lst_Print.ItemsSelected
        sql = "DELETE '*' FROM [tbl_print] WHERE [Print_ID] = " & Me.lst_Print.ItemData(i) & " ;"
        CurrentDb.Execute (sql)
    Next
    Me.lst_Print.Requery

 

[vbaInet]

Great!

Just two things:

1. You don't need the single quotes around the asterisk
2. How many items are there in the listbox? Or what's the maximum number of items can a user select?

 

[r.harrison]

The listbox shows all items that have not been printed so could be any number of items. And the user can select as many items as they wish.

 

[vbaInet]

What's the max number of records in that listbox?

 

[r.harrison]

There isn't one. Generally there wouldn't be more than 1000 at a time.

 

[vbaInet]

The reason why I ask is because for every record you're executing a Delete statement and if you had fewer records you could have concatenated the WHERE part of the SQL in the IN() operator and execute only once.

What you can still do is Execute for every 100 records for example.

 

[r.harrison]

WHERE [Print ID] = 1 OR [Print_ID] = 2 .......

Is that what you mean?

That could be an option, What is the maximum length of a string variable?

 

[vbaInet]

I think you can have a maximum of 80 ORs (I think) and I don't remember the max length of an SQL string in Access. It's probably something like 1025 characters.

I mean, WHERE [PrintID] IN (1, 2, 3, ...)