Access 2003 NotInList Event bug?

#1 (**permalink**) 28-Jul-2006, 08:28 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12392-access-2003-notinlist-event-bug.html
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.tabOptio ns.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(I ndex:=("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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12392
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)

#2 (**permalink**) 28-Jul-2006, 08:28 AM

"pk" wrote in message
news:A6udnTegy6Rp1DjZnZ2dnUVZ_qCdnZ2d@adelphia.com
> 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.tabOptio ns.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(I ndex:=("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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12392
>
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12392
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?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

#3 (**permalink**) 28-Jul-2006, 08:28 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12392
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....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.members.shaw.ca/AlbertKallal
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12392

#4 (**permalink**) 28-Jul-2006, 08:29 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12392
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

"Albert D.Kallal" wrote in message
news:%23iaKTrJnGHA.4352@TK2MSFTNGP02.phx.gbl...
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12392
> code that you may has messed up the fact that the combo stores a long
> number id field, but newdata is text....
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.members.shaw.ca/AlbertKallal
>