Not in List Event Procedure is not updating list

Posted on 2011-04-25
Last Modified: 2012-05-11
I have attached the Not-in-list Event Procedure for my combo box called Combo_Clients.
Also attached is the After Update Procedure.  I want to search for clients on the client No. field (cClientno - numeric) which is in the ClientTbl. (main form name is ClientFrm).

If user enters a client in the list it is displayed in the form.  If a client number is  not in the list the user is prompted to create new client, display it in the form where the user can complete the other fields (clientFld, contact, tel, etc.) and add it  to the list.  The code below creates a new client but evidently does not update the list so it asks to add it again.  This creates an error that states a duplicate will be created.  Thanks for any help.


Private Sub Combo_Clients_NotInList(NewData As String, Response As Integer)
'Original version Created by Helen Feddema
'modified 4-25-2011   ' Limit to List set to Yes  & using Access 2010
On Error GoTo ErrorHandler
    Dim cbo As Access.ComboBox
    Dim dbs As DAO.Database
    Dim intMsgDialog As Integer
    Dim intResult As Integer
    Dim rst As DAO.Recordset
    Dim strEntry As String
    Dim strFieldName As String
    Dim strMsg As String
    Dim strMsg1 As String
    Dim strMsg2 As String
    Dim strTable As String
    Dim strTitle As String
      strTable = "clientTbl" ‘combo box's row source
      strEntry = "Client No."  'type of item to add to the table
    strFieldName = "[cClientNo]"  'field in lookup table where new entry is stored
    Set cbo = Me.ActiveControl    'The add-to combo box
    'Display msg box asking whether user wants to add new entry.
    strTitle = strEntry & " not in list"
    intMsgDialog = vbYesNo + vbExclamation + vbDefaultButton1
    strMsg1 = "Do you want to add "
    strMsg2 = " as a new " & strEntry & " entry?"
    strMsg = strMsg1 + strNewData + strMsg2
    intResult = MsgBox(strMsg, intMsgDialog, strTitle)
    If intResult = vbNo Then
        'Cancel adding the new entry to the lookup table.
        intResponse = acDataErrContinue
        GoTo ErrorHandlerExit
    ElseIf intResult = vbYes Then
        'Add new record to lookup table.
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset(strTable)
        rst(strFieldName) = NewData
        intResponse = acDataErrAdded
        intResponse = acDataErrAdded
        Me.Dirty = False
       ' Me.Requery
       ' intResponse = acDataErrAdded
        GoTo ErrorHandlerExit
     End If
   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & " in " & & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Private Sub Combo17_AfterUpdate()
'Find the record that matches the control.
  Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cClientno] = " & Me![Combo17]
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub
Question by:DavidDF1913
    LVL 26

    Expert Comment

    You've got a lot of stuff in there!
    It looks like Helen built a generic NotInList handler.
    I don't know if I'd go that route.
    It's fairly complex.

    Just straight up in the controls NotInList event
    Dim db As Database
    Dim rs As Recordset
    Dim lngClientID As Long  '<------------ this depends on what YOUR primary key is
    If vbYes = MsgBox("'" & StrConv(NewData, vbProperCase) & "' is not entered as a current Client." & vbCrLf & "Do you wish to add it?", vbQuestion + vbYesNo, " ") Then
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT * FROM [Clienttbl] WHERE 1=2;", dbOpenDynaset, dbSeeChanges)
        With rs
           ![Client No.] = NewData
           .Bookmark = .LastModified
           lngOwnerID = ![Client ID] '<------------ this depends on what YOUR primary key is
         End With
         Set rs = Nothing
         Set db = Nothing
         DoCmd.OpenForm "frmClients", , , "[Client No.]=" & lngOwnerID  '<------------ this depends on what YOUR primary key is, and the form where the rest is to be entered
        DoCmd.SelectObject acForm, "frmClients" '<------------ this depends on what YOUR form, mine is "frmClients"
        response = acDataErrAdded
            response = acDataErrContinue
        End If

    Open in new window

    LVL 26

    Expert Comment

    If the author wants to do that, he can of course.
    He hasn't given me any feedback on my post.
    <The solution was not related to my question>

    My code is an adaptation of production code that deals with a notinlist event by creating a new record and opening a form to edit it.
    If it was not related, I clearly did not understand the question, and was not given any re-direction.
    It's the author's call

    Author Comment

    I am sorry, I didn't mean the solutionoffered  was not good but i tried to adapt  it for my problem in every way I could think of and it failed.   I may have phrased the question poorly.  The key thing I believe is pulling up the requested record if it exists but adding a new record  using the key field, cClientNo, if user requests it. Perhaps my event prodedure After Update is wrong.

    LVL 26

    Accepted Solution

    Maybe I misunderstood, too

    A NotInList event fires if the entry you make in a combobox doesn't exist in the the combobox's recordset.  Where you can get into trouble is if the combobox's recordset is not a full listing of what's actually in the table.  The NotInList event may then try to create a duplicate entry that the combobox doesn't see in the table because the combobox's recordset is a subset of what's in the table.

    The NotInList event also happens FIRST.  You cannot depend on an AfterUpdate event saving you from the consequences of a NotInList event.  If you KNOW that a NotInList event can occur despite something being in a table, you have to catch that in the NotInList.  Some code to see if the item really isn't in the TABLE, code to add it if it is not, code to adjust the recordset of the combo box if it is.

    Your AfterUpdate code is a mess
    Private Sub Combo17_AfterUpdate()
    'Find the record that matches the control.
      Dim rs As Object

        Set rs = Me.Recordset.Clone
        rs.FindFirst "[cClientno] = " & Me![Combo17]
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    End Sub

    If you need to find a record use this code.
    Private Sub Combo17_AfterUpdate()
    Dim rst As DAO.Recordset
    Dim strCriteria As String
    strCriteria = "[cClientno] = " & Me![Combo17]
    Set rst = Me.RecordsetClone
    rst.MoveLast 'populate it fully
    rst.FindLast strCriteria 'this works nicely on the assumption that what you are finding is near the end of the recordset
    Me.Bookmark = rst.Bookmark
    end sub

    Open in new window


    Author Closing Comment

    I finslly got it to work. Thanks very much for your help.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now