How to execute NotInList code in combo box

[vector39]

Hi everyone

I have a form for adding a new agency (ageny, address, city, programinfo, etc) and the combo box lists all subsidiaries (child component of main agency). For now users can select one and the record will update, however if a new subsidiary and its info (address, city, email, etc) needs to be added, I thought I'd try experimenting with the Not In List property of the combo box. This is the code I have so far:

Not In List combo box property:

Private Sub cboSubs_NotInList(NewData As String, Response As Integer)
Response = AddNewToList(NewData, "tblSubsidiaryINFO2", "Subsidiary", "Address", "City", "Prov", "PostalCode", "FirstName", "LastName", "Position", "ContactNotes", "OriginalEmail", "PhoneNumber", "PhoneNotes", "frmAddSubsidiary")
End Sub

Module in DB

Option Compare Database


Public Function AddNewToList(NewData As String, stTable As String, _
                                   stFieldName As String, strPlural As String, _
                                   Optional strNewForm As String) As Integer
On Error GoTo err_proc
    'Adds a new record to a drop down box list
    'If form name passed, then open this form to the newly created record


    'Declare variables
    Dim rst As DAO.Recordset
    Dim IntNewID As Long
    Dim strPKField As String
    Dim strMessage As String
    
    ' Display message box asking if user wants to add the new item
    strMessage = "'" & NewData & "' is not in the current list. " & Chr(13) & Chr(13) & _
                 "Do you want to add it to the list of " & strPlural & "?" & Chr(13) & Chr(13) & _
                 "(Please check the entry before proceeding)."


    If MsgBox(strMessage, vbYesNo + vbQuestion + vbDefaultButton2, "Add New Data") = vbYes Then
        Set rst = CurrentDb.OpenRecordset(stTable, , dbAppendOnly)
        rst.AddNew
            rst(stFieldName) = NewData                'Add new data from combo box
            strPKField = rst(0).Name                  'Find name of Primary Key (ID) Field
        rst.Update
        rst.Move 0, rst.LastModified
        IntNewID = rst(strPKField)


        'if a form specified, then open the form with the primary key equal to the new record ID as the criteria
        If strNewForm <> "" Then DoCmd.OpenForm strNewForm, , , strPKField & "=" & IntNewID, , acDialog
  
        AddNewToList = acDataErrAdded                'Set response 'Data added'
    Else
        AddNewToList = acDataErrContinue             'Set response 'Data NOT added'
    End If
    
exit_proc:
On Error Resume Next
    rst.Close
    Set rst = Nothing
    Exit Function


err_proc:
    MsgBox "Error in Function: 'AddNewToList'" & Chr(13) & Err.Description, , "Function Error"
    Resume exit_proc


End Function

Note: the following code I borrowed from examples online. When I go back to the form and I start typing something in that isn't in the list, I get this error


And the following code is highlighted yellow:



Private Sub cboSubs_NotInList(NewData As String, Response As Integer)
Response = AddNewToList(NewData, "tblSubsidiaryINFO2", "Subsidiary", "Address", "City", "Prov", "PostalCode", "FirstName", "LastName", "Position", "ContactNotes", "OriginalEmail", "PhoneNumber", "PhoneNotes", "frmAddSubsidiary")
End Sub

Also I've set data entry and allow additions on the addsubisidaryform to No. How can I make sure what I've done actually did anything? Thoughts?

[pbaldy]

Haven't reviewed the code, but in order for it to fire you'd need to set the Limit To List property to Yes, and then type a new value into the combo and hit Enter or Tab.

[Bulzie]

On the Combo Box Properties on the Data tab, there are options to Limit to List, Allow Value List Edits, List Items Edit Form. These will allow you to update the combo list as needed.

[ssanfu]

You get the compile error because you have called the function with 15 parameters
and the function only has 5 parameters in the declaration.........

I would suggest having the code open a form to add the new data.

[vector39]

I would suggest having the code open a form to add the new data.

Hi thanks for the reply. For the combo box I set Limit to List, Allow Value List Edits, Inherit Value List all to Yes and as well as List Items Edit Form to frmAddSubsidiary. Now forgive me for being dense but when you say have the code open a form, isn't that what this is already doing?

[June7]

As ssanfu said, the function can receive only 5 parameters and you are sending it 15. You can send only one field name, in this case I think that would be Subsidiary. The frmAddSubsidiary should open to a new record with the Subsidiary field already populated with NewData and user enters all the other data.

[vector39]

Thank you all!