Listbox reverts to old highlighted value on Me.Refresh

Rx_

Nothing In Moderation
Local time
Yesterday, 19:25
Joined
Oct 22, 2009
Messages
2,803
Adding a new record to a form - The Listbox is sorted by a date, so the new row can appear anywhere. This code correctly finds the new row and highlights it (selects it). The Forms bookmark is set to the new record.
Problem - the first time a Me.Refresh is encountered
The Listbox re-highlights the old selected record.
The Form's bookmark is still on the new record. So all the data in the form is still on the new record.
It is just that the highlight on the listbox is not on the correct row.
It would appear that any Me.Refresh makes the listbox's highligh jump.

Code:
Private Function ListBox_NewRecord_RowOrder(Listboxname As ListBox, NewRecord As Integer) As Boolean
      ' Pass in the name of the listbox, and the RecordSet field 0 (Primary Key) for the new record.
      'DESCRIPTION ' find last record added to list box (records don't come in order they are sorted by a Date field)
      ' textbox ID_Wells_Sundry (hidden) shows Form's primary key (id_Wells_Sundry) after ADD new record
      ' Pass the list box pointer, pass in the RecordSet primary key (usually based on autocounter for base table)
      Dim MyListboxname As ListBox
      Dim i As Long
10       On Error GoTo ListBox_NewRecord_RowOrder_Error
20       If IsNull(NewRecord) Then
40          Exit Function
50       End If
60       Set MyListboxname = Listboxname ' works great
70       If MyListboxname.ListCount > 0 Then
80          For i = 0 To MyListboxname.ListCount - 1
90             If CInt(MyListboxname.Column(0, i)) = NewRecord Then
100               MyListboxname.Selected(i) = True ' Highlight column 0 same as RS column 0 ' Works highlights new record in listbox
110                 Exit For
                End If
120         Next
130      Else
140           Exit Function
150      End If
160      Set MyListboxname = Nothing
 
Evidently, staring at it harder with out a cup of coffee didn't help.

Code:
100               MyListboxname.Selected(i) = True ' Highlight column 0 same as RS column 0 ' Works highlights new record in listbox
101                        MyListboxname = NewRecord ' updates actual listbox so it sticks when next refresh is hit
110                 Exit For
 

Users who are viewing this thread

Back
Top Bottom