Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access 2003 NotInList Event bug?

Discussion in 'Information Technology' started by pk, Jul 28, 2006.

  1. pk

    pk
    Expand Collapse
    Guest

    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)
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "pk" <pk@hotmail.com> 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.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?

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

    (please reply to the newsgroup)
     
  4. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    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....

    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  5. pk

    pk
    Expand Collapse
    Guest

    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

    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> 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
    > 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
    >
     

Share This Page