??? How to simply expand the List of Values on a form.

[ssmith]

I have a Form that has a table driven by a List the values are in a seperate
table. If the user enters a value that does not exist - I wish for the form
to add that value to the table and continue without interruption - How do I
code this ?? I am using an event procedure as follows but it's not working
the way I want - any help please???

 

[tina]

I am using an event procedure as follows but it's not working

the way I want - any help please???

nothing follows. care to post your procedure, so we can review it?

hth

 

[ssmith]

I have found the answer - the following is an example procedure that I have
found and used - it works charmingly - Thanks for your help!!!

Private Sub FlavorID_NotInList(NewData As String, Response As Integer)
On Error GoTo SomethingBadHappened

Dim rstFlavors As ADODB.Recordset
Dim intAnswer As Integer

intAnswer = MsgBox("Add " & NewData & " to the list of flavors?", _
vbQuestion + vbYesNo)

If intAnswer = vbYes Then
Set rstFlavors = New ADODB.Recordset
rstFlavors.Open "Flavors", CurrentProject.Connection, _
adOpenStatic, adLockOptimistic, adCmdTable

rstFlavors.AddNew
rstFlavors!Flavor = NewData
rstFlavors.Update
Response = acDataErrAdded
Else
Response = acDataErrDisplay
End If

rstFlavors.Close
Set rstFlavors = Nothing

Exit Sub

SomethingBadHappened:
MsgBox "When trying to process this order, something bad happened" & _
vbCrLf & "Please contact the program vendor and " & _
"report the error as follows" & vbCrLf & _
"Error #: " & Err.Number & vbCrLf & _
"Description: " & Err.Description
Resume Next
End Sub