NotInList Error Problem

[Pete]

I have a combo box bound to a lookup table. It contains 3 columns. The first
column is an ID column and is zero width. I have used the Not In List event
to add values many times before without a problem, but for some reason I
still get the "text you entered is not in the list" error, even when a value
has been successfully added and can be seen in the list. I just can't see why
this is happening.

Private Sub cboiStockID_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
NewData = UpperCase(NewData)
intAnswer = MsgBox("Add " & NewData & " as a new Stock Code?",
vbQuestion + vbYesNo, Me.Caption)
If intAnswer = vbYes Then

Enter_Stock_Name:
Dim strStockName As String
strStockName = InputBox("Please enter the corresponding Stock Name
for " & NewData, "Setup New Stock Code")
If strStockName = "" Then
intAnswer = MsgBox("You must enter a value for this Stock Name.
Click OK to continue or Cancel to abort.", vbExclamation + vbOKCancel, "Stock
Name Error")
If intAnswer = vbOK Then
GoTo Enter_Stock_Name
Else
GoTo Exit_Sub
End If
End If

Dim strSQL As String
strSQL = "INSERT INTO tlkpStock (cStockCode, cStockName) SELECT '" &
NewData & "', '" & strStockName & "'"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Exit Sub
End If

Exit_Sub:
Response = acDataErrDisplay

End Sub

 

[AccessVandal via AccessMonster.com]

Hi Pete,

Are you sure about the sub-query?

Pete wrote:
strSQL = "INSERT INTO tlkpStock (cStockCode, cStockName) SELECT '" &
NewData & "', '" & strStockName & "'"

Try without single qoutes.

strSQL = "INSERT INTO tlkpStock (cStockCode, cStockName) SELECT " &
NewData & ", " & strStockName

NewData = the column of the table? name of the column? key in column name?
strStockName = FROM TableName WHERE ColumnName = the stock Name? is this a
string that user have to key in?

I don't think you can use the single qoutes in the syntax. Remove it.

 

[Pete]

Hi

Thanks for your response. I realised that the problem is due to the fact
that control is bound using the hidden ID column and the New Data Value is
for column 1, so whilst the data is getting added to the lookup table
correctly and the correct Response value is returned the NotInList error will
still occur. I just need to find out what the work around is.

 

[AccessVandal via AccessMonster.com]

Hi Pete,

If you hide the column using "0" and Bound Column is "1", will take the
second column as the first column thus the error message.

There someone asking about this last week.
'-----------------------------------------------------------------------------
--------------------------------
Try this, set the “List Width†only to show the “Descriptionâ€. Instead of
“ID†as the first column/field, put it at the last column/field , for example,

if each field is length is 1â€, say you have 3 fields..so, total of 3â€. Set
the “List Width†to 2â€, so you have the “ID†field hidden.

Select ID, Description, PartType From Table1
To
Select Description, PartType, ID From Table1

Set the “Bound Column†to 3 in this case. This will “ID†Bound the data into
the field.
'-----------------------------------------------------------------------------
--------------------------------