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

[Matthew Wells]

Sounds like you just have to requery the combo box after your insert
statement.

If you're not even getting to this point you may have fallen into the Access
trap. Did you start to write the procedure in the code window by selecting
the control on the left and the procedure on the right? Doing it this way
sometimes does not bind the event to the control. Check the properties
window of the control in design view to make sure there is [Event Procedure]
there.
"Kaur" <ka***@saic.com> wrote in message
news:11**********************@i40g2000cwc.googlegr oups.com...

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

[Kaur]

Thanks ever so much. I found the mistake. the code runs without any
problem