Error with Private Sub and NotInList procedure not working!!!

[Himy]

I'm trying to run the simple NotInList example provided in the Access2000 help file. The Code is as follows:

Private Sub Colors_NotInList(NewData As String, Response As Integer)

Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Colors
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = 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

End Sub

Now it says to assign this code to "the form" - I thought it had to be added to the OnNotInList command assigned to the combobox but it doesn't work - where in the "form" do I assign the code to make this run properly...

Also with every OTHER code I've ever tried - the Private Sub statement always gives me an error - the Sub only works when I remove the Private...

Any help would be greatly appreciated!

Cheers,

Himy

 

[Fornatian]

I use this function

Public Function Add2Source(tbl As String, fld2update As String, NewData As String) As Integer
'Adds record to the list if not already present
'This only works if there is just one field to update....

Dim StrMessage As String
Dim Dbs As Database
Dim RstTypes As Recordset
Dim Response As Integer
StrMessage = "'" & NewData & "' is not in current list" & _
" To add the item for future reference choose yes, or choose no to select from the present options."
Response = MsgBox(StrMessage, vbYesNo, "Not in List")
If Response = vbYes Then
Set Dbs = CurrentDb()
Set RstTypes = Dbs.OpenRecordset(tbl)
RstTypes.AddNew
RstTypes.Fields(fld2update) = NewData
RstTypes.Update
Add2Source = acDataErrAdded
Else
Add2Source = acDataErrDisplay
End If
End Function

and call it as follows

Private Sub Breed_NotInList(NewData As String, Response As Integer)
Response = Add2Source("Breeds", "Breed", NewData)
End Sub


this adds the new data to the record source table Breeds into the field Breed as a new record. it only works on lookup tables with one field