add new value to combo list

[Emidio Sivieri]

Hi again,
A form user enters a value in a combo-list field
which is not already in the lookup table for that combo-
list. How can I have this new value be added to the lookup
table automatically?

Thanks again,
ems

 

[John Vinson]

Hi again,
A form user enters a value in a combo-list field
which is not already in the lookup table for that combo-
list. How can I have this new value be added to the lookup
table automatically?

Thanks again,
ems

See the online help for the NotInList event, or for a code sample go
to http://www.mvps.org/access and search for "NotInList".

 

[peter]

Try this... Insert this code into the notinlist property
and adjust where necessary. Insert into is the table name
eg mine is [Matter Types] , and the next text in square
brackets is the control name eg. mine is [Type]

Private Sub MatterType_NotInList(NewData As String,
Response As Integer)
Dim strSQL As String
If vbYes = MsgBox("'" & NewData & "' is not a current
Choice." & vbCrLf & "Do you wish to add it?", vbQuestion +
vbYesNo, " ") Then
Set db = DBEngine(0)(0)
strSQL = "INSERT INTO [MatterTypes] ([Type]) VALUES
('" & NewData & "');"
db.Execute strSQL
Response = acDataErrAdded
Set db = Nothing
Else
Response = acDataErrContinue
End If
End Sub