Why is is my MS Access control not accepting my “o

Question

I've been using the Access "On Not In List" event for a long time. It allows you to limit the items in your combo box to a particular list, but allows the user to add an item to combo's record source on the fly if they want to enter something that isn't already there. You set the "Limit To List" property of the combo box to Yes, but then you put some code behind the "On Not In List" event. Intermittently, I get a situation in Access 2016 where this doesn't seem to work. I get the standard "The item is not in the list." error when trying to enter a new item, without my code-behind logic being seen and called. What's up?

Answer 1

After banging my head against the wall for a long time, I believe this is bug in Access 2016 and I think I stumbled on a fix. I set the form's RecordSetType property to Snapshot, closed and saved form, reopened the form in design view, set the RecordSetType property back to Dynaset. This seems to have cleared up the problem. I have no idea why.

But since I'm here. . . some additional details: In the code-behind for each control I use code like this:

Private Sub VendorID_NotInList(NewData As String, Response As Integer)
    Response = RU_NotInList("Lookup Vendor", "Description", NewData, gc_strMsgCap)
End Sub

This type of subroutine gets created automatically behind the "On Not In List" event, when you click on the 'code builder' option. I have mine call a utility function that I wrote a long time ago. ("RU" refers a code library.)

The function returns an intrinsic Access integer constant that gets passed straight back to Access to handle.

The inside of that routine looks like this:

Function RU_NotInList(TableName As String, FieldName As String, newdata As String, Optional pstrTile As String) As Integer
    Dim rs As DAO.Recordset, db As DAO.Database, n1 As Integer

    RU_NotInList = DATA_ERRCONTINUE
    On Error GoTo RU_NotInList_Error
    If Len(Trim(newdata)) = 0 Then Exit Function

    n1 = MsgBox(newdata & " is not in the list.  Do you wish to add it?", MB_ICONQUESTION + MB_YESNO, pstrTile)
    If n1 = IDNO Then Exit Function

    Dim strSQL As String
    strSQL = "INSERT INTO [" & TableName & "] ([" & FieldName & "]) VALUES (""" & newdata & """)"
    WarningsHour True 'Turns hourglass cursor on, warning messages off.
    DoCmd.RunSQL strSQL
    WarningsHour False 'Undoes the above.

    RU_NotInList = DATA_ERRADDED
    Exit Function

RU_NotInList_Error:
    RUError "RU_NotInList", Err 'generic error-handling routine in the RU library
    Exit Function

End Function

All the all-caps items in the code above are Access intrinsic constants.