Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54

    NotInList Event Not Firing

    Hi all,

    I'm sure this is simple, so I must just be a little 'thick' today.

    I have a form called "Journal Quick Add" which contains a combo box, cboCompany, and a subsequent combo box, cboContact. It's combo box has the first column hidden (PK Contact_ID) and the second column displayed (ContactName). Its row source is filtered by Company_ID of the value in cboCompany. Works fine.

    I want the user to be able to add a new ContactName to the Contacts table. I've set both the the Limit To List property and the Allow Value List Edits both to Yes. I have set On Not in List to [Event Procedure], which should run the code below.

    The event doesn't fire at all. I get the typical Access message that the text I entered isn't in the list and to select one that's in the list or enter text that matches.

    I've got the NotInList event running correctly in several other places in this database. I can't figure out why the event isn't firing.

    Thanks for the help!



    Code:
    Private Sub cboContact_NotInList(NewData As String, Response As Integer)On Error GoTo ErrorHandler
       
       Dim intAnswer As Integer
       Dim CompanyID
       Dim CompanyName
       CompanyID = Me.Company_ID
       CompanyName = DLookup([CompanyName], "tblCompanies", "Company_ID = " & CompanyID)
       intAnswer = MsgBox("Do you want to add '" & NewData & "' to the contact list for " & CompanyName & "?", _
          vbQuestion + vbYesNo)
    
    
       If intAnswer = vbYes Then 'User wants to add name to tblContacts
            Dim strSQL As String
            strSQL = "INSERT INTO tblContacts (Company_ID, ContactName) VALUES (" & CompanyID & ", & NewData & );"
            DoCmd.RunSQL strSQL
    
    
          Response = acDataErrAdded
       
       Else 'User doesn't want to add name to tblContacts
          
          Response = acDataErrContinue
          
       End If
       
       
    Exit_Handler:
    Exit Sub
    
    
    ErrorHandler:
       Resume Exit_Handler
    
    
    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,869
    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,461
    NewData is a string. Try it with delimiters:
    Code:
           strSQL = "INSERT INTO tblContacts (Company_ID, ContactName) VALUES (" & CompanyID & ", '"  &  NewData & "')"
    

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,096

  5. #5
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    davegri, it seems that the event doesn't even 'fire'. I just get the standard message that the name isn't in the list. So it doesn't get far enough to see that NewData is a string and needs the delimiters in strSQL, although I did modify it per your suggestion.

    I've checked all the properties and made sure the event procedure On Not In List is there.

    orange, opening a List Item Edit Form would need to open and somehow use the correct Company_ID, wouldn't it?

    I'd still like for a custom message to display, so the event has to fire.

    Thanks for the input. June7, I'll have to do some work to post the db. It's a working file with a lot of data in it.

    MIB1019

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,528
    I've checked all the properties and made sure the event procedure On Not In List is there.
    But did you set the LimitToList property to Yes? If not, the event won't fire.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,869
    Stated in OP: "I've set both the the Limit To List property and the Allow Value List Edits both to Yes."

    The Allow Value List Edits property should be irrelevant because RowSource is a table/query not a Value List.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,096
    orange, opening a List Item Edit Form would need to open and somehow use the correct Company_ID, wouldn't it?
    Depends on your situation. If it's a new item from a separate table, then adding a new record with an autonumber key would be entered without knowing the key in advance.
    I was pointing to a feature when wanting to add a new record to the combo, and that such records were originally entered through a specific form.

    If you want custom, explicit messages then I agree with others.
    Good luck with your project.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,528
    @june7 - ok so I missed that about the property. As for the other, just going by what it says here
    https://docs.microsoft.com/en-us/off...obox.notinlist

    The LimitToList property must be set to Yes for the NotInList event to occur.

    Doesn't say the list has to be a value list.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,869
    I never said it has to be a value list. I merely said AllowValueListEdit property is irrelevant because RowSource uses table/query. And I only mention that because the OP referenced that property.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,528
    Not sure how else to interpret this
    The Allow Value List Edits property should be irrelevant because RowSource is a table/query not a Value List.
    To me that implies the property is only relevant if the source is a value list. All I'm pointing out is what it says there, and it makes no such distinction. Take it or leave it as you will.
    Last edited by Micron; 09-24-2020 at 05:48 PM. Reason: correction
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,869
    The referenced link speaks to LimitToList, not AllowValueListEdits. As far as functionality of NotInList event, when RowSource is a table/query AllowValueListEdits can be either Yes or No, doesn't matter therefore is irrelevant in this situation.

    From what I've read, AllowValueListEdits is only useful when lookup field is in table and combobox or listbox bound to that field. Then companion property ListItemsEditForm can be utilized.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54
    Thanks for all the input there, everybody! I have read up on Allow Value List Edits and think I understand it better.

    I did get the code to fire by taking out the error handler, which was simply doing an exit sub. I'll want to redo the error handling on that sub.

    Going back to the Journal Quick Add form... the field above the one that was giving me trouble is the Company_ID field. Wanting to allow for adding a company on the fly as I have done with the Contact (OP). The code is as follows:

    Code:
    Private Sub cboCompany_NotInList(NewData As String, Response As Integer)
        'On Error GoTo ErrorHandler
       
       Dim intAnswer As Integer
       Dim db As Database
       Dim rstCompanies As Recordset
       
       intAnswer = MsgBox("Do you want to add '" & NewData & "' to the companies list?", _
          vbQuestion + vbYesNo)
    
    
       If intAnswer = vbYes Then
          
          Set db = CurrentDb
          Set rstCompanies = db.OpenRecordset("tblCompanies", dbOpenDynaset)
          With rstCompanies
            .AddNew
            .Fields("CompanyName") = NewData
            .Update
            .Close
          End With
          db.Close
          Set rstCompanies = Nothing
          Set db = Nothing
           
          Response = acDataErrAdded
          
          Dim Answer As Integer
          Answer = MsgBox("Do you want to provide other details about " & NewData & "?", vbQuestion + vbYesNo)
          
          If Answer = vbYes Then
             Dim txtform
             Dim CompanyID
             txtform = "Company Quick Add"
             CompanyID = DLookup("Company_ID", "tblCompanies", "CompanyName = '" & NewData & "'")
             
             Debug.Print "New CompanyID " & CompanyID
             DoCmd.OpenForm txtform, acNormal, "", "[Company_ID]=" & CompanyID, acFormEdit, acWindowNormal
          End If
          
       Else
          
          Response = acDataErrContinue
          
       End If
       
       
    Exit_Handler:
    'Exit Sub
    
    
    ErrorHandler:
      ' Resume Exit_Handler
    End Sub
    It runs through the point of DoCmd.OpenForm "Company Quick Add", then I get the standard NotInList message from Access. I close the Company Quick Add dialog and the original form runs the 'Do you want to add NewData to the Company list' MsgBox. Like an endless loop, adding the same company over and over again.

    What's missing in my code for this combo?

    Thanks for all the exellent help! I've been using Access for quite a while but I still get so stumped on code.

    MIB1019

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,869
    So what is different between working procedure and this one? Have you step-debugged? If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    mib1019 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    54

    Help me with Not In List Event

    Thanks for the help. I've looked at the code for Company Not In List, compared to Contact Not In List, and don't see any problematic differences. I excluded the part asking user if he/she wants to add additional details, and that made no difference.

    Here is the file. Please have a look and see what you think.

    MIB1019

    Not In List.zip

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. ClsModule and a NotInList Event
    By d9pierce1 in forum Programming
    Replies: 4
    Last Post: 08-04-2019, 05:16 AM
  2. Key Press Event and/or Key Up Event not firing as expected
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 01-16-2018, 04:11 AM
  3. NotInList event
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 04-13-2011, 09:10 AM
  4. NotInList event issue
    By elinde in forum Forms
    Replies: 1
    Last Post: 04-01-2011, 08:43 PM
  5. Cancelling the NotInList event
    By Remster in forum Programming
    Replies: 12
    Last Post: 11-21-2010, 10:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums