Not In List Help Needed (1 Viewer)

[Wayne]

I have set up 3 combo boxes on a form. They are based on a value list, not a table or query. I have set the limit to list property to Yes, and put the following code into the OnNotInList Event:

Private Sub AgtType_NotInList(NewData As String, Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me.AgtType
' Prompt user to verify they wish to add new value.
If MsgBox("The agent type you entered is not in the list. Do you wish to add it to the list?", _
vbOKCancel + vbQuestion, "New Agent Type") = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub

When I enter a value that is not in the value list, nothing happens. The new value remains in the combo box, but is not added to the value list, nor does a message box open up to allow that option.

Also, when I added the row "ctl.RowSource = ctl.RowSource & ";" & NewData", the intellisense window did not have an option fro "RowSource". Is there a reference to a Library that I am missing or something?

Can anyone help here please?

Wayne

 

[John Big Booty]

Using a value list (or table level lookup) is generally not recommended. This sample demonstrates a method of managing the contents of a lookup table