ComboBox not selecting added record

[jeffmoseler]

I have a combo box with an not in list event that adds a record to the table it is bound with. After it adds it, the combo box seems to be struggling with re-selecting the added record. I have a feeling that it is because I need to update the record source of the main form, but if I do that then I loose the currently selected record on the form. How do I update the record source of the form and keep the currently selected record?

Or maybe I'm missing something and there's another way. I posted the not in list event code below.

Private Sub cmbCurrentIns_NotInList(NewData As String, Response As Integer)
    Dim oRS As DAO.Recordset, i As Integer, sMsg As String
    Dim oRSClone As DAO.Recordset

    Response = acDataErrContinue

    If MsgBox("Add " & NewData, vbYesNo) = vbYes Then
        Set oRS = CurrentDb.OpenRecordset("tblInsurers", dbOpenDynaset)
        oRS.AddNew
        oRS.Fields(1) = NewData
        oRS.Update
        cmbCurrentIns = Null
        cmbCurrentIns.Requery
    End If
    
End Sub

 

[dlearyfun]

If you think it's because you lose(note the correct spelling) the current record, you could create a bookmark which will store the current recordset pointer and then reset it back. Try the following if you believe your premise is correct.
Dim oRS As DAO.Recordset, i As Integer, sMsg As String
Dim oRSClone As DAO.Recordset
Dim MyBookmark 'This creates a variant
Response = acDataErrContinue


If MsgBox("Add " & NewData, vbYesNo) = vbYes Then
Set oRS = CurrentDb.OpenRecordset("tblInsurers", dbOpenDynaset)
MyBookmark = oRS.BookMark 'This saves the current record pointer to where you were before the Addnew.
oRS.AddNew
oRS.Fields(1) = NewData
oRS.Update
cmbCurrentIns = Null
cmbCurrentIns.Requery
oRs.Bookmark = MyBookmark 'This restores the record pointer
End If

 

[jeffmoseler]

I think we are on the right track, but I think we need to bookmark the record of the form and not the combo-box. I swapped out your code for mine and it did exactly the same thing.
Because I think I loost my spot (lol).

 

[jeffmoseler]

bumb
I may be misunderstanding the code. I think it is trying to bookmark the record of the form. But it still isn't working. It adds the record to the table and the combo, but it doesn't assign the new record created in the combo to the current record on the form. So after I confirm to add, the combobox automatically drops down with nothing selected and the new item added. I need it to select the new item and assign it to the forms record too. Am I missing something?

 

[jeffmoseler]

A little more info:
tblInsurers is the table associated with the combobox
tblSortedCarriers is the table associated with the form.
The new item created in the tblInsurers table needs to be assigned to the tblSortedCarriers table in the CurrentInsurance field.