How to clear an item from a list box

[Spaniard]

How does one clear an item from a list box? The scenario is: There are several items listed. The user chooses one and then clicks the "Save" button to save that record. I then want to have the item disappear and show only the remaining items. I am new to Access so please forgive me if the question sounds too stupid.

Thanks,
SWK

 

[jebry]

Hi!

It depends on the rowsource of the list box. For a value list you can use the following:

Dim varRow As Variant
Dim lngPos As Long
Dim lstBox As Control

Set lstBox = Me!YourListBox
For Each varRow in lstBox.ItemsSelected
lngPos = InStr(lstBox.RowSource, lstBox.Columns(0, varRow)
lstBox.RowSource = Left(lstBox.RowSource, lngPos - 2) &
Mid(lstBox.RowSource, lngPos + Len(lstBox.Columns(0, varRow))
Next VarRow

If you RowSource is a query or sql then you need some way of identifying what belongs in the list box and change the record on the table accordingly or replace the sql if that seems necessary.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Spaniard]

Thanks for the fast response! Unfortunately, I'm using a query and I don't quite understand what you mean by needing some way of identifying what belongs in the list box and change the record on the table accordingly.

In its current state, the user is able to select and save items in the list box, but to eliminate confusion, I want the item to disappear from the user's view so he/she won't select it again. I suspect that it should happen immediately after saving the record in the click event, but am unsure of the code needed to implement.

Thanks,
SWK

 

[jebry]

Hi!

Without seeing what you have done I may not be able to give you a good answer. That said we can look at a couple of possibilities. For instance, when you save the record, are you saving it to a different table then the list box is accessing? If so then all you need to do is delete the record from that table after saving it to its new table and requery the list box:

Dim sql As String

sql = "Delete YourTable.* From YourTable Where PrimaryKey = " & YourListBox.Value

CurrentDb.Execute sql

YourListBox.Requery

If all the records are in the same table or you don't want to delete any records then you need a field to identify which records to select.

Dim sql As String
Dim rst As DAO.Recordset

sql = "Select AppearOnListBox From YourTable Where PrimaryKey = " & YourListBox.Value

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
rst.Edit
rst!AppearOnListBox = False
rst.Update

Set rst = Nothing
YourListBox.Requery

Then in the query your list box is based on add the condition that AppearOnListBox must be true.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Spaniard]

Thanks for your assistance! I believe that will give me something to work with.

SWK