Using a function as opposed to "On Not in List" (or "Before Update")

You could - but in most cases, the method used to handle different controls would not be the same. For example, for a Customer dropdown you may simply wish to inform the user "You must add a new Customer first". For a "Type" dropdown you may not want to allow the user to add anything, and instead select from a pre-determined list.

As such, I'm not sure how much effort is worthwhile for something like this. Generally speaking, once you have code set for a DropDown it won't change much ...

Thank you Scott - I know what you are saying, but I'm evaluating the best method for this particular setup, so am still curious.

The "best method for this particular setup" would require us to know quite a bit about the setup before we could provide suggestions as to "best practices".

For example, if you're storing all the data for the dropdown lists in a single table, then you could certainly create a central routine that would modify/update that central table.

If you're pulling data from many different tables, then it would seem the effort to centralize this would be not worth the effort.

But without knowing more about "this particular setup" it's hard to give good advice.

I sometimes use a generic NotLinList event.

Public Function NotInList_general( _
   psTable As String _
   , psField As String _
   , pNewData As Variant _
   , Optional sDeli As String = "'" _
   , Optional pbooCase As String = "" _
   , Optional psField2 As String = "" _
   , Optional pnValue2 As Long = -99 _
   ) As boolean
'131027, 140103 pbooCase
    ' crystal (strive4peace)
 
   'assumption:
   'the combobox first column is hidden
   'and is the Autonumber record ID  for the source table
 
   'set up Error Handler
   On Error GoTo Proc_Err
 
   NotInList_general = False
 
   Dim sSQL As String _
      , sMsg As String
 
   Select Case pbooCase
   Case ""
   Case "U":  pNewData = UCase(pNewData)
   Case "M", "P": pNewData = StrConv(pNewData, vbProperCase)
   End Select
 
   ' Display message box asking if user wants to add a new item
   sMsg = "Do you want to add " & sDeli & pNewData & sDeli & "? " _
 
   If MsgBox(sMsg, vbYesNo + vbDefaultButton2, "Add New Data") = vbNo Then
      Exit Function
   End If
 
    sSQL = "INSERT INTO [" & psTable & "] (" & psField & ") " _
        & " SELECT " _
        & sDeli & pNewData & sDeli & ";"
   End If
 
   NotInList_general = (rSql(sSQL) > 0)
'   DoEvents 
 
Proc_Exit:
   On Error Resume Next
   Exit Function
 
Proc_Err:
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   NotInList_general"
 
   Resume Proc_Exit
 
   'if you want to single-step code to find error, CTRL-Break at MsgBox
   'then set this to be the next statement
   Resume
End Function

Select allOpen in new window

That code calls this code (I simplified it, hopefully it will still compile)

Sub rSql(pSQL, Optional pMsg _
   , Optional IsAggregateUpdate As Boolean) as long
'strive4peace
   DoEvents
   On Error GoTo Proc_Err

   Dim db As DAO.Database
 
   Set db = CurrentDb 
 
   Dim nTime As Date  _
      , nNumRecs As Long

   nTime = Now()
 
   'returns a negative number if SQL could not be executed
   nNumRecs = -1
   rSql = -1
 
   If Not IsMissing(pMsg) Then
      Debug.Print "~~~~~~~~~~~~~~ " & pMsg
      SysCmd acSysCmdSetStatus, pMsg & "..."
   End If
   Debug.Print pSQL
   If Not IsMissing(IsAggregateUpdate) Then
      If IsAggregateUpdate Then
         DoCmd.Echo False
         DoCmd.SetWarnings False
         DoCmd.RunSQL pSQL
         DoCmd.Echo True
         DoCmd.SetWarnings True
      Else
         with db
             .Execute pSQL
             nNumRecs = .RecordsAffected
        end with
      End If
   Else
         with db
             .Execute pSQL
             nNumRecs = .RecordsAffected
        end with
   End If

      Debug.Print _
         " -- " & Now() & " --- " & nTime _
         & " --- " & nNumRecs _
         & " --- " & IIf(IsMissing(pMsg), "", pMsg)
 
Proc_Exit:
   On Error Resume Next
   Set db = Nothing
   Exit Sub
 
Proc_Err:
   DoCmd.Echo True
   DoCmd.SetWarnings True
   resume proc_exit
 
End Sub

Select allOpen in new window

Thank you Crystal - that's great.  Can you call it directly from the controls properties (as opposed to creating an event)?

Thanks Scott - It is a single table.  It will be triggered on the "Not in list" event, so we know it's a new entry.  Assuming the user has permission, it will then add the value to the list, return a response that allows the user to proceed and then refresh the dropdown.  If the user doesn't have permission to add, it will stop them.

Brilliant - that'll do nicely.

Thanks guys for all of your help.

you're welcome ~ happy to help