Solved: Listbox selecting

[Imdabaum]

Is there a way to keep the selection on your listbox the first time you click it? Everytime I select an item from the listbox it displays the item I selected, but then highlights the first item in the listbox. The list box access notes to project notes. And if a user selects a note to delete, it shows the one they select but then highlights the first item. If the user presses delete it deletes the first item and not the one displaying.

If I double click the highlight stays the same and everything works fine. I don't want to have to send out an email to every new user that accesses this reminding them to double click. I want good functionality right from the release.

[OBP]

My list box stays on the highlighted item with a single click, even if I put the form in design mode and come back to it. But I programmed it from a VBA array so that may be the reason.

[Imdabaum]

Mine loads the items from a qry in the Row Source. http://www.geocities.com/imdabaum2002/untitled.JPG here is what the image displays after one click on the 9/4/1991 selection. Hope someone can help me fix the problem here.

[XLGibbs]

Do you have an onExit event or LostFocus event that refreshes the list box?

It would seem unless you have something auto refreshing the list box that the active selection would remain highlighted..

[OBP]

If you have not found a way to overcome your list box resetting itself then there is a simple solution, which I would probably prefer to do anyway and that is to use some VBA in the list box's "On Click" event procedure to save the selection in an invisible text box and use that value for the "Delete routine".

[Imdabaum]

So what if I could do an onClick Event that would just set the highlight/selected item from the listbox. Here is the only thing I do with my listbox as far as code.

Public Sub UpdateList()
On Error GoTo UpdateListError
' If IsNull(Forms!frmPropertiesNew!ID) Then ' removed 16 Sep 2003, did not get to clear lstDates
' Exit Sub
' End If
strSQL = "SELECT tblPropertiesUpdates.PropertyID, tblPropertiesUpdates.NoteDate, tblPropertiesUpdates.Memo, tblPropertiesUpdates.MemoPart " & _
"FROM tblPropertiesUpdates " & _
"WHERE (((tblPropertiesUpdates.PropertyID) = " & [Forms]![frmPropertiesNew]![ID] & ")) " & _
"ORDER BY tblPropertiesUpdates.NoteDate desc" ' SMRF 6392 - 13 October 2000 - Ruth Radmall



Me.lstDates.RowSource = strSQL
Me.lstDates.Requery

Me.Memo.SetFocus

Exit Sub

UpdateListError:
MsgBox Err.Number & " " & Err.Description

End Sub
And this..... Private Sub lstDates_AfterUpdate()
On Error Resume Next

Dim strSQL As String

Me.RecordsetClone.FindFirst "PropertyID= " & Me.lstDates.Column(0) _
& " AND NoteDate= #" & Me.lstDates.Column(1) & "#" _
& " AND MemoPart = '" & Me.lstDates.Column(3) & "'"

Me.Bookmark = Me.RecordsetClone.Bookmark
Me.lstDates.SetFocus

End Sub

[OBP]

Sorry, I can't reproduce what your list is doing. I now have another list box getting it's data from a query.
Is your list "bound" to a field on your form?

[Imdabaum]

It's unbound. I'll try and capture an image of the properties.
http://www.geocities.com/imdabaum2002/stylestuff/listboxForm.JPG

It doesn't show much.. but the actual row source is
SELECT tblPropertiesUpdates.NoteDate, tblPropertiesUpdates.MemoPart FROM tblPropertiesUpdates
WHERE (((tblPropertiesUpdates.PropertyID)=forms!frmPropertiesNew!ID)) ORDER BY tblPropertiesUpdates.NoteDate DESC;

But that shouldn't affect the listbox selected property.

[OBP]

Can you remove the where and order parts of the source to see if it causing the problem?

[Imdabaum]

Sorry it got crazy over here with new projects that needed building and repairing. No it doesn't make a difference whether the WHERE/ ORDER BY clauses are in it.

Private Sub lstDates_AfterUpdate()
On Error Resume Next

Dim strSQL As String

Me.RecordsetClone.FindFirst "ProjectID= " & Me.lstDates.Column(0) _
& " AND NoteDate= #" & Me.lstDates.Column(1) & "#" _
& " AND MemoPart = '" & Me.lstDates.Column(3) & "'"

Me.Bookmark = Me.RecordsetClone.Bookmark
Me.lstDates.SetFocus

End Sub

That's the afterupdate function... Also If I want to display other field values in the listbox do I need to just update the RecordsetClone, or do I have to rebuild the listbox to display those values? Or can I just change the Row Source?

[Imdabaum]

I found what was causing the problem. I was not aware that when you set that the Me.Bookmark = Me.RecordsetClone.Bookmark actually calls the OnCurrent sub. And my onCurrent sub called UpdateList. I commented that out simply called the UpdateList() sub in the OnLoad sub and now the highlight stays where I click the first time. Thanks everyone for your ideas and support. It's nice to have things working.