Delete values from multi-select list box after selection

[timmy773]

I have to list boxes. After I select a value from listbox1 and click a button, it places the value in listbox2, so I know that it was updated. Now what I want to do is remove the value that was selected from listbox1 when I click the update button, so it is no longer available. Listbox1 is fed by a table (MasterElements) and Listbox2 is fed by a table (SelectedElements). I know I'm making this too hard, I hope someone can help.

 

[vbajock]

Delete the value from the underlying table and then run Listbox1.requery to refresh the list box

 

[timmy773]

Yes, that's exactly the goal, but I can't get the code right that will tell me which boxes I've selected and thusly which to delete from the underlying table.

 

[vbajock]

The easy way to do it for a non-programmer would be to set up a query that deletes records from the underlying table, MasterElements. In the criteria section of the query, you need to enter

Forms!TheNameofMyform!Listbox1.Value

in the field the list box is based on.

After that is set up, you would put the following code into the AfterUpdate event of listbox2:

'turn off warning messages
docmd.setwarnings false
'delete the value from the table
docmd.openquery "NameOfmyDeleteQuery"
'requery the control
Forms!TheNameofMyform!Listbox1.requery
'turn warnings back on
docmd.setwarnings true

 

[timmy773]

Okay, actually the control on the list box is a group-by query (so I only see single occurances of records), and the listbox is a multi-select, so after I select 10 records and click a button to update the records, I want them removed from list box. The ultimate would be not to actually remove them from the table, but just the view of the listbox. If it does actually require deleting the records that would be fine too. Thanks for your help.

 

[LogicalmanUS]

timmy773,

When you click the update button, you obviously, at that point in time, have a list of the selected items.
If you place these into a comma-delimited string, e.g. 'Toast',' bread', 'butter' or 1,2,4,6,78
then you can use them for an IN statement. This is like a multiple WHERE .. LIKE statement.

Example:

The following statement:
SELECT myID from myTable
returns the result:
1
2
3
4
5
6

The following statement:
SELECT myID from myTable WHERE myID NOT IN (1,3,5)
returns the result:
2
4
6

So you can see how it works.
Just create the SQL staring you use for the ListBox and then alter it as you go along and put it back as the rowsource for the listbox each time.

Logicalman

 

[timmy773]

LogicalMan, this sounds like a great solution but I have to admit I'm a bit of a novice in this area, especially with the SQL, could you provide a bit more assistance? A real life example possibly?

 

[LogicalmanUS]

timmy773,

OK. Follow the instructions below for an example.

Create a Table

Name: tblList
Column1: ListID autonumber
Column2: ListText Text

Open the table in datahseet view, and enter the foolowing data (the first column is an sutonumber)

1 One
2 Two
3 Three
4 Four
5 Five
6 Six

Create a form with the following controls:

ListBox
Name: lstSelect
ColumnCount: 2
ColumnWidths: 0";1"

Text Box -
Name: txtSelected
Width: 2"

Button
Name: cmdSubmit


Open the Class Module for the form, and copy the following code into it:

Private Sub cmdSubmit_Click()
Me.txtSelected = Me.txtSelected & ", " & Me.lstSelect.Value
If Left(Me.txtSelected, 1) = "," Then Me.txtSelected = Mid(Me.txtSelected, 2)
Me.lstSelect.RowSource = GET_RS()
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.lstSelect.RowSource = "SELECT tblList.ListID, tblList.ListText FROM tblList; "
End Sub

Function GET_RS() As String
GET_RS = ""
GET_RS = GET_RS & " SELECT tblList.ListID, tblList.ListText FROM tblList"
GET_RS = GET_RS & " WHERE tblList.ListID NOT IN (" & Me.txtSelected & &quot"
GET_RS = GET_RS & " ; "
End Function

Make sure the Event Tab of the button has the following for the On Click Event: [Event Procedure]

Make sure the Event tab of the Form has the following for the On Open Event: [Event Procedure]

Save the form and then run it.
Select an entry in the list box, and click the button. The number will appear in the text box, whilst the same entry in the List Box will be removed.

If you have further questions, just ask,

Happy Coding,

Logicalman

 

[timmy773]

LogicalMan,

thanks for the help...I tried this however, and it close but not quite there, the textbox is only populated with commas, and the initial list box is totally cleared no matter what you select.

 

[LogicalmanUS]

timmy773,

Does anything appear in the list box at the start?

I have created all the objects as I described, with the code, and it works here.

If the list box is populated at the start but then all entries disappear when you click the submit button, I will take a further look and write a check to stop that happening.

Logicalman