Combobox NotInList problems

[John]

I am having trouble with the NotInList event on a combobox (actually several
similar comboboxes).

When I enter data that is not in the combobox list, I sometimes (but not
always!) get the "You have entered data that is not on the list." error
message, in spite of the NotInList procedure which should add the new item to
the table which is the rowsource for the combobox. I can't find any pattern
to when I get the message and when I don't.

I have verified that the data has in fact been entered onto the table, but
it is not showing up on the combobox list. If I undo and retype the data, I
get a duplicate key error (in fact, occasionally I get a duplicate key error
the first time I enter new data!). The combobox has only a single column.

The table ttmpCboSpecColor is the RowSource for cboSpecColor, and is located
in the FE of a split FE/BE mulituser database (this is a workaround to
improve a performance issue caused by comboboxes requerying from the BE on
the server). It has a single field called "SpecColor" which is set as the
primary key.

Here is the code for the NotInList event procedure:

Private Sub cboSpecColor_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboSpecColor_NotInList

Dim rs As DAO.Recordset

'Add item to table which is combobox rowsource.
Set rs = CurrentDb.OpenRecordset("ttmpCboSpecColor", dbOpenDynaset)
With rs
.AddNew
!SpecColor = NewData
.Update
End With
Response = acDataErrAdded

Exit_cboSpecColor_NotInList:
On Error Resume Next

'Release object variable.
rs.Close
Set rs = Nothing

Exit Sub

Err_cboSpecColor_NotInList:
'Notify user and exit.
MsgBox Err.Description, vbExclamation, "Unable to update combobox: error
#" & Err.Number
Response = acDataErrContinue
Resume Exit_cboSpecColor_NotInList
End Sub

It seems as though the acDataErrAdded response is not functioning. Can
anyone help?

Thanks in advance,
John

 

[Graham Mandeno]

Hi John

That is very curious. It seems that acDataErrAdded is somehow translating
to the wrong value. I suggest you set a breakpoint on the
Response=acDataErrAdded line and verify that Response is indeed being set to
the value 2. If it is not, then you must have acDataErrAdded declared as a
constant or variable somewhere, and its value is taking precedence over the
value in the library.

Also, ensure that you have "Option Explicit" at the top of ALL your modules.

Another possibility is that you are currently in a transaction. Could this
be the case?

Incidentally, you can use a SQL statement for a simple record insertion like
this:

CurrentDb.Execute "Insert into ttmpCboSpecColor (SpecColor) values ('" _
& NewData & "');", dbFailOnError

That will eliminate 8 lines of your code

 

[John]

Hi Graham,
Thanks for the suggestions. I verified the value of acDataErrAdded in my
code, and I always use Option Explicit. There is definitely NOT a
conflicting variable with the same name.
After further investigation, I am pretty sure the problem is caused by the
size of the combobox rowsource, which is several hundred items (I know, not a
great idea). As I understand the acDataErrAdded response, it causes the
combobox to be requeried and then the entered data is checked against the
combobox list again to make sure it is on the list. Since the combobox list
is very large, it hasn't finished loading when the data is re-checked, so the
data is not found and the "Not In List error" is triggered. If the entered
data is near the top of the list, it has been loaded when the data is
re-checked against the list and the error does not trigger. The combobox
also works in a smaller test database with fewer items on the comboboxes,
which supports this idea.
Does that sound plausible? If so, I just have to refine the rowsource for
the combobox to return fewer items.

Thanks,
John

Hi John

That is very curious. It seems that acDataErrAdded is somehow translating
to the wrong value. I suggest you set a breakpoint on the
Response=acDataErrAdded line and verify that Response is indeed being set to
the value 2. If it is not, then you must have acDataErrAdded declared as a
constant or variable somewhere, and its value is taking precedence over the
value in the library.

Also, ensure that you have "Option Explicit" at the top of ALL your modules.

Another possibility is that you are currently in a transaction. Could this
be the case?

Incidentally, you can use a SQL statement for a simple record insertion like
this:

CurrentDb.Execute "Insert into ttmpCboSpecColor (SpecColor) values ('" _
& NewData & "');", dbFailOnError

That will eliminate 8 lines of your code

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having trouble with the NotInList event on a combobox (actually
several
similar comboboxes).

When I enter data that is not in the combobox list, I sometimes (but not
always!) get the "You have entered data that is not on the list." error
message, in spite of the NotInList procedure which should add the new item
to
the table which is the rowsource for the combobox. I can't find any
pattern
to when I get the message and when I don't.

I have verified that the data has in fact been entered onto the table, but
it is not showing up on the combobox list. If I undo and retype the data,
I
get a duplicate key error (in fact, occasionally I get a duplicate key
error
the first time I enter new data!). The combobox has only a single column.

The table ttmpCboSpecColor is the RowSource for cboSpecColor, and is
located
in the FE of a split FE/BE mulituser database (this is a workaround to
improve a performance issue caused by comboboxes requerying from the BE on
the server). It has a single field called "SpecColor" which is set as the
primary key.

Here is the code for the NotInList event procedure:

Private Sub cboSpecColor_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboSpecColor_NotInList

Dim rs As DAO.Recordset

'Add item to table which is combobox rowsource.
Set rs = CurrentDb.OpenRecordset("ttmpCboSpecColor", dbOpenDynaset)
With rs
.AddNew
!SpecColor = NewData
.Update
End With
Response = acDataErrAdded

Exit_cboSpecColor_NotInList:
On Error Resume Next

'Release object variable.
rs.Close
Set rs = Nothing

Exit Sub

Err_cboSpecColor_NotInList:
'Notify user and exit.
MsgBox Err.Description, vbExclamation, "Unable to update combobox:
error
#" & Err.Number
Response = acDataErrContinue
Resume Exit_cboSpecColor_NotInList
End Sub

It seems as though the acDataErrAdded response is not functioning. Can
anyone help?

Thanks in advance,
John

 

[Graham Mandeno]

Hi John

Well, it does sound plausible, but I've not seen it happen, even with much
larger RowSources. Is your RowSource data in a remote table, or ODBC linked
table?

What might work is executing cboSpecColor.Requery after adding the new
record. You might also need a DoEvents befor exiting the event procedure.

Let us know how you get on with these suggestions.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Thanks for the suggestions. I verified the value of acDataErrAdded in my
code, and I always use Option Explicit. There is definitely NOT a
conflicting variable with the same name.
After further investigation, I am pretty sure the problem is caused by
the
size of the combobox rowsource, which is several hundred items (I know,
not a
great idea). As I understand the acDataErrAdded response, it causes the
combobox to be requeried and then the entered data is checked against the
combobox list again to make sure it is on the list. Since the combobox
list
is very large, it hasn't finished loading when the data is re-checked, so
the
data is not found and the "Not In List error" is triggered. If the
entered
data is near the top of the list, it has been loaded when the data is
re-checked against the list and the error does not trigger. The combobox
also works in a smaller test database with fewer items on the comboboxes,
which supports this idea.
Does that sound plausible? If so, I just have to refine the rowsource
for
the combobox to return fewer items.

Thanks,
John

Hi John

That is very curious. It seems that acDataErrAdded is somehow
translating
to the wrong value. I suggest you set a breakpoint on the
Response=acDataErrAdded line and verify that Response is indeed being set
to
the value 2. If it is not, then you must have acDataErrAdded declared as
a
constant or variable somewhere, and its value is taking precedence over
the
value in the library.

Also, ensure that you have "Option Explicit" at the top of ALL your
modules.

Another possibility is that you are currently in a transaction. Could
this
be the case?

Incidentally, you can use a SQL statement for a simple record insertion
like
this:

CurrentDb.Execute "Insert into ttmpCboSpecColor (SpecColor) values ('" _
& NewData & "');", dbFailOnError

That will eliminate 8 lines of your code

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having trouble with the NotInList event on a combobox (actually
several
similar comboboxes).

When I enter data that is not in the combobox list, I sometimes (but
not
always!) get the "You have entered data that is not on the list." error
message, in spite of the NotInList procedure which should add the new
item
to
the table which is the rowsource for the combobox. I can't find any
pattern
to when I get the message and when I don't.

I have verified that the data has in fact been entered onto the table,
but
it is not showing up on the combobox list. If I undo and retype the
data,
I
get a duplicate key error (in fact, occasionally I get a duplicate key
error
the first time I enter new data!). The combobox has only a single
column.

The table ttmpCboSpecColor is the RowSource for cboSpecColor, and is
located
in the FE of a split FE/BE mulituser database (this is a workaround to
improve a performance issue caused by comboboxes requerying from the BE
on
the server). It has a single field called "SpecColor" which is set as
the
primary key.

Here is the code for the NotInList event procedure:

Private Sub cboSpecColor_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_cboSpecColor_NotInList

Dim rs As DAO.Recordset

'Add item to table which is combobox rowsource.
Set rs = CurrentDb.OpenRecordset("ttmpCboSpecColor", dbOpenDynaset)
With rs
.AddNew
!SpecColor = NewData
.Update
End With
Response = acDataErrAdded

Exit_cboSpecColor_NotInList:
On Error Resume Next

'Release object variable.
rs.Close
Set rs = Nothing

Exit Sub

Err_cboSpecColor_NotInList:
'Notify user and exit.
MsgBox Err.Description, vbExclamation, "Unable to update combobox:
error
#" & Err.Number
Response = acDataErrContinue
Resume Exit_cboSpecColor_NotInList
End Sub

It seems as though the acDataErrAdded response is not functioning. Can
anyone help?

Thanks in advance,
John

 

[John]

Hi Graham,
Thanks for the additional suggestions. I had already tried requerying the
combobox in the NotInList event (after adding the new data to the rowsource
table), but it give an error saying "You must save the current field before
you run the requery action." I tried adding a DoEvents statement before
exiting the NotInList event, but it did not help. The rowsource tables are
local (but the combobox is bound to a field on a linked table with the BE on
a server).
I don't think my theory was exactly correct, but it does seem to be
related to the speed with which the combobox loads the rowsource data when it
requeries. Inexplicably, after trying your suggestions and a few other
things, then changing them back, the combobox is now working. I noticed
that, when it wasn't working, if I clicked the dropdown button on the
combobox and tried to scroll through the list using the scroll bar, it went
in chunks as the combobox list loaded more rows. Now, when it is working
fine, I can scroll easily through the whole list. Any idea why the combobox
would perform differently on the same computer? I'm a little hesitant to say
it's fixed without knowing what caused the problem.

Thanks,
John

Hi John

Well, it does sound plausible, but I've not seen it happen, even with much
larger RowSources. Is your RowSource data in a remote table, or ODBC linked
table?

What might work is executing cboSpecColor.Requery after adding the new
record. You might also need a DoEvents befor exiting the event procedure.

Let us know how you get on with these suggestions.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
Thanks for the suggestions. I verified the value of acDataErrAdded in my
code, and I always use Option Explicit. There is definitely NOT a
conflicting variable with the same name.
After further investigation, I am pretty sure the problem is caused by
the
size of the combobox rowsource, which is several hundred items (I know,
not a
great idea). As I understand the acDataErrAdded response, it causes the
combobox to be requeried and then the entered data is checked against the
combobox list again to make sure it is on the list. Since the combobox
list
is very large, it hasn't finished loading when the data is re-checked, so
the
data is not found and the "Not In List error" is triggered. If the
entered
data is near the top of the list, it has been loaded when the data is
re-checked against the list and the error does not trigger. The combobox
also works in a smaller test database with fewer items on the comboboxes,
which supports this idea.
Does that sound plausible? If so, I just have to refine the rowsource
for
the combobox to return fewer items.

Thanks,
John

Hi John

That is very curious. It seems that acDataErrAdded is somehow
translating
to the wrong value. I suggest you set a breakpoint on the
Response=acDataErrAdded line and verify that Response is indeed being set
to
the value 2. If it is not, then you must have acDataErrAdded declared as
a
constant or variable somewhere, and its value is taking precedence over
the
value in the library.

Also, ensure that you have "Option Explicit" at the top of ALL your
modules.

Another possibility is that you are currently in a transaction. Could
this
be the case?

Incidentally, you can use a SQL statement for a simple record insertion
like
this:

CurrentDb.Execute "Insert into ttmpCboSpecColor (SpecColor) values ('" _
& NewData & "');", dbFailOnError

That will eliminate 8 lines of your code

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am having trouble with the NotInList event on a combobox (actually
several
similar comboboxes).

When I enter data that is not in the combobox list, I sometimes (but
not
always!) get the "You have entered data that is not on the list." error
message, in spite of the NotInList procedure which should add the new
item
to
the table which is the rowsource for the combobox. I can't find any
pattern
to when I get the message and when I don't.

I have verified that the data has in fact been entered onto the table,
but
it is not showing up on the combobox list. If I undo and retype the
data,
I
get a duplicate key error (in fact, occasionally I get a duplicate key
error
the first time I enter new data!). The combobox has only a single
column.

The table ttmpCboSpecColor is the RowSource for cboSpecColor, and is
located
in the FE of a split FE/BE mulituser database (this is a workaround to
improve a performance issue caused by comboboxes requerying from the BE
on
the server). It has a single field called "SpecColor" which is set as
the
primary key.

Here is the code for the NotInList event procedure:

Private Sub cboSpecColor_NotInList(NewData As String, Response As
Integer)
On Error GoTo Err_cboSpecColor_NotInList

Dim rs As DAO.Recordset

'Add item to table which is combobox rowsource.
Set rs = CurrentDb.OpenRecordset("ttmpCboSpecColor", dbOpenDynaset)
With rs
.AddNew
!SpecColor = NewData
.Update
End With
Response = acDataErrAdded

Exit_cboSpecColor_NotInList:
On Error Resume Next

'Release object variable.
rs.Close
Set rs = Nothing

Exit Sub

Err_cboSpecColor_NotInList:
'Notify user and exit.
MsgBox Err.Description, vbExclamation, "Unable to update combobox:
error
#" & Err.Number
Response = acDataErrContinue
Resume Exit_cboSpecColor_NotInList
End Sub

It seems as though the acDataErrAdded response is not functioning. Can
anyone help?

Thanks in advance,
John