Rx_
Nothing In Moderation
- Local time
- Today, 09:53
- Joined
- Oct 22, 2009
- Messages
- 2,803
Completed and working:
I could not find a solution to borrow and came up with this.
When new rows are added to a table and the sort order is based on the primary ID or date created, selecting the newly inserted row in the list box and then updating the form is straightforward.
When the list box sort order is more complex, the newly inserted row will not be at the top or bottem.
This solution is working, it checks the primary field ID for a newly inserted record, then loops through the list box to find the list order and selects it.
Once a new record is entered, this will select the new record (somewhere in the middle of your listbox, and then update the associated form.
I could not find a solution to borrow and came up with this.
When new rows are added to a table and the sort order is based on the primary ID or date created, selecting the newly inserted row in the list box and then updating the form is straightforward.
When the list box sort order is more complex, the newly inserted row will not be at the top or bottem.
This solution is working, it checks the primary field ID for a newly inserted record, then loops through the list box to find the list order and selects it.
Once a new record is entered, this will select the new record (somewhere in the middle of your listbox, and then update the associated form.
Code:
110 DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
' acMenuVer70 works in 2008 but no new acMenuVer beyond 70 exist
120 rs.MoveLast ' move to new record
130 rs.Edit ' rs.update later
' code to control form's business rules not shown
320 Me.lstNotifications.Requery ' shows new record in listbox
345 Call Lstbox_NewItmInList(Me.lstNotifications, rs.Fields(0).Value) ' (pass in listbox name and rs field 0 (primary key) for new inserted record - this will highlight the new record in the listbox
346 Me.Bookmark = rs.Bookmark ' updates form
350 Set rs = Nothing
Sub Lstbox_NewItmInList(ListBoxName As ListBox, NewRecord As Integer)
' 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)
'is the new record from your record set object valid?
Dim MyListBoxname As ListBox
Dim i As Long
If IsNull(NewRecord) Then
MsgBox "Please enter info", vbExclamation, "number in subLstbox_ewItmInLst is invalid"
Exit Sub
End If
Set MyListBoxname = ListBoxName
If MyListBoxname.ListCount > 0 Then
For i = 0 To MyListBoxname.ListCount - 1
If CInt(MyListBoxname.Column(0, i)) = NewRecord Then
MyListBoxname.Selected(i) = True ' Highlight column 0 same as RS column 0
End If
Next
Else
Exit Sub
End If
Set MyListBoxname = Nothing
' More Notes ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'If you would like to search against another column,
'change the 1st argument in MyListBoxname.Column(0, i)
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
End Sub
Last edited:
