[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Not in List Event Procedure is not updating list

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 " & Me.ActiveControl.name & " 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
  • 3
  • 2
1 Solution
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

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
DavidDF1913Author Commented:
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.

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

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


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now