Not in list Error

[Kaur]

Hi,

I have a form Form1 that displays surveys. This form has a subform1
which displays questions related to a particular survey. This subform
has a subsubform which displays subquestions for a particular question
for a particular survey.
I have created a combo box for questions on subform1. What I am trying
to achieve is if a user wants to add a question to a particular survey,
if the question is not in the list, the user should be able to add the
new question in the underlying lookup table and at the same time save
the record on the form (associating this new question to the Survey).
This is the code I have written;

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

Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Question...")
If i = vbYes Then
strSQL = "Insert Into LkupQuestions ([Question]) values ('" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Each time I try to add a new question to the list I get an error: The
text you have entered is not in list, select the text from the list
box. . . . . .

Please let me know what I am doing wrong as I want to acheive the same
results with the subsubform by adding a new subquestion and associating
it with question and survey.

Any help would be appreciated.
Thanks

Kaur

 

[Someone]

What strikes me initially is that you have set the combo box's 'Limit To
List' property to Yes. If this property is set to Yes, then despite your
coding, this will over-ride your code. In your form's design view,
right-click the combo box and select Properties. Now check for 'Limit To
List' and see if it is set to Yes. If it is, then change it to No.

M

 

[Kaur]

Thanks ever so much. The code runs fine now.

 

[Someone]

You're welcome. Glad it worked!