Clear a list box on Access 2002 1

[buhgman33]

I have read a few threads on how to clear the selected line from a list box, however non of the work.
What I would like to do is if an item is selected and then I add a new item to the listbox, I either want the selection highlight to be cleared or move to the top row. The problem is after I save the new data, the list box requeries and then the form refreshes, then what happens is I go back to the same row being selected as it was before. I try have tried
me.list0.selected(varitem)=false
this will clear the selectio, but then when it refreshes it goes back to the previous selection.
Me.list0.rowsource=me.list0.rowsource did absolutely nothing.
Any help would be appreciated.

Thanks.

 

[fneily]

There's probably an easier way to do this. But this is what I did:
Create a "make table" query just picking out your field. Add the criteria Is Not Null. Right click the grey area, select Properties and set Unique Values to yes. Save and name it.

Create an UNbound listbox on a form(Do not use wizard). For Rowsource, place some SQL: "SELECT [YourFieldName] FROM TableNameFromMakeTableQuery; "

On the AfterUpdate event of the listbox, place:

Private Sub List10_AfterUpdate()
Dim R As DAO.Recordset
Set R = CurrentDb.OpenRecordset("Select [YourFieldName] From [TableNameFromMakeTableQuery] Where [YourFieldName] = " & Chr(34) & Me![List10].Value & Chr(34))
R.Delete
R.Close
Me![List10].Requery
End Sub

Create a macro with the following actions and arguments:
Echo No
Setwarnings No
Openquery MakeTableQueryName, datasheet, edit
Echo yes
Setwarnings yes

Place Macro on the On Open event of the form.

When you open the form, the macro will run creating a little table. Then when a user choses a listbox selection, the code will run, delete the record from the new table, then requeries the listbox source.

If listbox items are numeric, get rid of Chr(34)'s.

 

[buhgman33]

Not sure I am following this. The listbox already exists how does this clear the list box I have selected, since it seems you are deleting from a totaly different table. I don't want the list box on my form to lose any data. So how does deleting a record from another table, clear the selection on the existing list box. I have noticed that if I select an item from the list box and then delete it, then the listbox is cleared, but I am not sure how it would help in this case?
Your help is appreciated.

 

[fneily]

You're making a table with the items you want displayed in the listbox. This'll be the rowsource. Why? So from this table, after a selection is made, you can delete the item then requery the listbox. Now that item is no longer in the list. You obviously can't delete the item from the main table so you create this temp table.
If you actually tried my suggestion, the user sees items in a listbox. They select one item. Now the AfterUpdate code runs and deletes it from the temp table, requeries the listbox and the item is no longer in the list. If that's not what you want then I misunderstood.
The listbox is never empty until the last item is selected.

 

[MajP]

I iterpret this a little differently.

1) You have a bound listbox
2) You either want to just get rid of the selection hilighting
3) or you want to get rid of the highlighting and set the value of the underlying record to null

Here is the issue. If you do what you say it unselects the hilighting of the listbox; however, the value in the underying table stays the same. So if you requery or come back to this record the value in the underlying table will rehilight in the listbox. So it depends on what you are really trying to do.

Private Sub Command12_Click()
  Dim lstOne As Access.ListBox
  Set lstOne = Me.intDutySection
  Call unSelectListBox2(lstOne)
  'Call unSelectListBox2(lstOne)
End Sub

Public Sub unSelectListBox(theList As Access.ListBox)
  Dim varItem As Variant
  For Each varItem In theList.ItemsSelected
    theList.selected(varItem) = False
  Next varItem
End Sub

Public Sub unSelectListBox2(theList As Access.ListBox)
   theList = Null
End Sub

the "unSelectListBox" does what you did originally, but if you requery the form you will need to call the procedure
again if you want to keep the list unselected. In other words: unselect, requery (since bound will rehilite), unselect again.

the "unSelectListBox2" will set the value to null so it wil not only unselect(unhilite), but change the underlying value.

 

[fneily]

MajP - You're probably right. For some reason I interpreted
"to clear the selected line" as delete. Like "clear the room", "clear the dishes", begone. Oh well.

 

[buhgman33]

Thanks MajP, the "unSelectListBox2" is what I wanted in the sense that it clears the highlight away. But can I then reset the list box so that it will go to the top of the list again, but with no highlight.
Thanks