add new value to combo list

  • Thread starter Thread starter Emidio Sivieri
  • Start date Start date
E

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
 
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
Click to expand...

See the online help for the NotInList event, or for a code sample go
to http://www.mvps.org/access and search for "NotInList".
 
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
 
Back
Top