Access 2003 NotInList Event bug?

[pk]

Hi,

This is driving me up a wall. I am using the notinlist event for a combo
box, based on a query, 2 columns. The following properties are set:
Bound Column = 1
Column Count = 2
Column Width = 0.0; 1.5
Limit To List = Yes
Auto Expand = Yes

When a value is entered into the combo box I call a rules engine class
module passing the text that was entered. The rules engine assuming the
data passed calls a data engine class module that inserts the new value into
the table. The data engine procedure returns a true false to the rules
engine, that in turn returns a integer that equates to the access constants
used in this event.

I check if the return value equals the constant value of acDataErrAdded and
if it does set the Response argument of the event to the value of
acDataErrAdded.
This all works yet I continue to recieve the message "the item is not in the
list" . The standard access error message is not being suppressed. This
happens even if I run the code directly in the event handler.

Here are the code snippets
Form
Private Sub cboApplications_NotInList(NewData As String, Response As
Integer)
Dim intResponse As Integer
' pass the new app to the rules engine
intResponse =
moRulesEngine.ChangeOptions(DirtyPage:=Me.tabOptions.Pages(Index:="pgeProcess"))
If intResponse = acDataErrContinue Then
Response = acDataErrContinue
MsgBox Prompt:="insert of new app name failed"
Else
Response = acDataErrAdded ' value should equate to the constant
acDataErrAdded
End If

Rules Engine Snippet
Case Is = "pgeProcess"
strAppName = DirtyPage.Controls("cboApplications").Text
' pass entered name to rhe routine to check for valid length
blnCheckPassed = ValidateAppName(AppName:=strAppName)
If blnCheckPassed Then ' send the name to the data engine to
insert into the database
blnDataEngineResponse =
moDataEngine.AddListItem(ctl:=DirtyPage.Controls(Index:=("cboApplications")),
NewValue:=strAppName)
If blnDataEngineResponse Then
ChangeOptions = acDataErrAdded ' app name was added
successfully
Else
ChangeOptions = acDataErrContinue ' app name was not
added successfully
End If
Else
Err.Raise Number:=mconBASE_ERROR_NBR + 4,
Source:="RulesEngine.ChangeOptions", Description:="The length of the entered
text does not fit the column size."
End If
End Select

Data Engine Snippet
Public Function AddListItem(ctl As ComboBox, NewValue As String) As Boolean
Dim strErrorProcedure As String
Dim blnFuncResult As Boolean
On Error GoTo CantAddListItem
strErrorProcedure = "CMPSupport.basDataLayer.AddListItem"
With mocmd
Select Case ctl.Name
Case Is = "cboApplications"
.CommandType = adCmdStoredProc
.CommandText = "spInsertApp"
.Parameters.Append .CreateParameter(Name:="AppDesc",
Type:=adVarChar, Direction:=adParamInput, Size:=50, Value:=NewValue)

 

[Dirk Goldgar]

Hi,

This is driving me up a wall. I am using the notinlist event for a
combo box, based on a query, 2 columns. The following properties are
set:
Bound Column = 1
Column Count = 2
Column Width = 0.0; 1.5
Limit To List = Yes
Auto Expand = Yes

When a value is entered into the combo box I call a rules engine class
module passing the text that was entered. The rules engine assuming
the data passed calls a data engine class module that inserts the new
value into the table. The data engine procedure returns a true false
to the rules engine, that in turn returns a integer that equates to
the access constants used in this event.

I check if the return value equals the constant value of
acDataErrAdded and if it does set the Response argument of the event
to the value of acDataErrAdded.
This all works yet I continue to recieve the message "the item is not
in the list" . The standard access error message is not being
suppressed. This happens even if I run the code directly in the
event handler.

Here are the code snippets
Form
Private Sub cboApplications_NotInList(NewData As String, Response As
Integer)
Dim intResponse As Integer
' pass the new app to the rules engine
intResponse =
moRulesEngine.ChangeOptions(DirtyPage:=Me.tabOptions.Pages(Index:="pgePr
ocess"))
If intResponse = acDataErrContinue Then
Response = acDataErrContinue
MsgBox Prompt:="insert of new app name failed"
Else
Response = acDataErrAdded ' value should equate to the
constant acDataErrAdded
End If

Rules Engine Snippet
Case Is = "pgeProcess"
strAppName = DirtyPage.Controls("cboApplications").Text
' pass entered name to rhe routine to check for valid
length blnCheckPassed =
ValidateAppName(AppName:=strAppName) If blnCheckPassed
Then ' send the name to the data engine to
insert into the database
blnDataEngineResponse =
moDataEngine.AddListItem(ctl:=DirtyPage.Controls(Index:=("cboApplication
s")),
NewValue:=strAppName)
If blnDataEngineResponse Then
ChangeOptions = acDataErrAdded ' app name was
added successfully
Else
ChangeOptions = acDataErrContinue ' app name
was not added successfully
End If
Else
Err.Raise Number:=mconBASE_ERROR_NBR + 4,
Source:="RulesEngine.ChangeOptions", Description:="The length of the
entered text does not fit the column size."
End If
End Select

Data Engine Snippet
Public Function AddListItem(ctl As ComboBox, NewValue As String) As
Boolean Dim strErrorProcedure As String
Dim blnFuncResult As Boolean
On Error GoTo CantAddListItem
strErrorProcedure = "CMPSupport.basDataLayer.AddListItem"
With mocmd
Select Case ctl.Name
Case Is = "cboApplications"
.CommandType = adCmdStoredProc
.CommandText = "spInsertApp"
.Parameters.Append
.CreateParameter(Name:="AppDesc", Type:=adVarChar,
Direction:=adParamInput, Size:=50, Value:=NewValue)

Assuming the various class do work, and the new item is successfully
added to the rowsource table of the combo box, then the only thing that
occurs to me to wonder offhand is whether the data is added via a
separate connection to the database. It looks like this code may be in
an ADP, not an MDB, and I don't know much about them, but in MDBs I've
seen a problem like this where the update procedure uses a separate
connection from the one that Access is using, and Access doesn't know
that the data has been added/updated until some time goes by.

Is data engine module opening a separate connection to the database? If
so, can it use CurrentProject.Connection instead?

 

[Albert D.Kallal]

The following works:

You can set the limit to yes = to yes.

The, for the no in list event, you can use the following code:


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

Dim strSql As String
If MsgBox(NewData & " not in list, add?", _
vbYesNo + vbQuestion) = vbYes Then
strSql = "insert into tblStudents (name) values('" & NewData & "')"
CurrentDb.Execute strSql
Response = acDataErrAdded
End If

End Sub

MAKE SURE that the value you are adding is the text field value. The combo
box is storing a ID field, but the NewData is going to be the text value..

When you code runs, does the value get added to the table correctly (simply
open the table....and check what is in the list -- make sure the text bound
column is EXACTLY the same as newdata..or it will fail. In addition, do be
aware of the

I suspect somewhere in your complex code that replaces the above simple code
that you may has messed up the fact that the combo stores a long number id
field, but newdata is text....

 

[pk]

Albert,

Thanks for the insight. I actually ran something similiar to what you have
written below and still received the same error. The new value does get
added to the table correctly, I thought the event after requerying the combo
box does compare the value in the text portion of the control to the list.
Which is why it should not fail, I will most definitely go back and check
what value is going where.

Kevin