NotInList Event

[Jeff H]

I have an unbound combobox with a value list. I want users to be able to add to the list so I’m using the NotInList event. It does add the new item to the Row Source but when the form is closed the Row Source property change is lost.

The combobox represents a text field in the table, so on Form Current I populate the cbo with the value of that field and After Update of the cbo I populate the field with its value.

The cbo has these properties: no Control Source; Row Source is the value list; Bound Column 1; Limit to List - Yes; Allow Value List Edits - Yes

The NotInList event has this code, and it works.

Code: Select all

Private Sub cboTrngSession_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim strAdd As String
 
Set ctl = Me!cboTrngSession
If MsgBox("The session designation '" & NewData & _
        "is not in the list. Add it?", _
         vbYesNo) = vbYes Then
	
	strAdd = Chr(34) & NewData & Chr(34)
	Response = acDataErrAdded
	Debug.Print ctl.RowSource
	ctl.RowSource = ctl.RowSource & ";" & strAdd 'NewData
	Debug.Print ctl.RowSource
Else
	Response = acDataErrContinue
	ctl.Undo
End If

End Sub 

I see the change in the Row Source both in the debug window and in the dropdown list, but when I switch to design view or close the form it does not get saved. I originally just used NewData, but I added the double quotes because the updated value list didn’t have them and I thought that was the problem.

I see that the cbo itself has an editing tool that appears when the list is dropped down, and that’s an adequate feature. But at this point I’m curious about why NotInList isn’t working.

[HansV]

Permanent changes to the Value List can only be made in design view.

If you want to add entries to the list in form view, the Row Source Type must be Table/Query and the Row Source must be a table, query or SQL string.
In the On Not in List event procedure, you have to add a record to the relevant table. Since this record is stored in the database, it will remain available.

ComboBox.NotInList event (Access) has examples of the code you can use to do so. Post back with detailed information if you need help with this.

[Jeff H]

Ok. I'll make it a table and check it out. I had already used NotInList successfully with a table list, but I thought I could do it with a simple value list. I'll let you know.

[Jeff H]

Yes sir. It worked fine. Thanks again!

[HansV]