Not in the list SQL

[Data Slave]

I want to create a SQL for when something is not in the list in the combo box I created. When you are trying to type something in the combo box I want it to realize that it's not on the list and a msgbox should tell you to double click on the field. Then I want a separate form (Target Species Additions) to pop up and allow you to enter this info. Then you could close that form and go back to entering your data. Sort of like the ingredients list. The field I want to do this in is named SNAME. If anyone know the SQL exactly I would really appreciate any help or advice.
Thanx again,
Data Slave

 

[Travis]

I don't know about SQL but you can do this with VBA.

Set the combo to LimitToList. On the NotInList Event add the following code.

Private SNAME_NotInList(olddata as variant,response as Integer)
response=acDataerrContinue 'This avoids Accesses little msg.
if Msgbox ("Entry is not currently in list. Do you wish to add it to the list?",vbYesNo)=vbYes then
docmd.OpenForm "Target Species Additions"
Else
me.sname=Empty
end if
end sub

On the on close event the the Target Species Additions form you will need to do a Forms![Name of the form where combo Sname is].SName.Requery.