Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    70

    Unanswered: NotInList not working

    H i all, I have a combo field (cboItemLU) bound to a table field on a form. I have code that when I double click on the form field another form opens and the user adds a new record in the combo field's table. The close button saves the record and closes the add form then refreshes the cboItemLU field on the form and allows me use that entry to add a new record to the table bound to the form. This all works.

    I also have had occassions where a user tried to type a non-existent entry and was confused by the error messages and did understand that they could double click to add the entry. So I am trying to make their life easier by responding to their confusion and not try to make them bend to my way of doing things. So I added what I thought was a way to allow them add a record to the field's table using the NotInList event. NotInLIst is set to No. This is not working.

    This is the code from the double click that works.
    Code:
    Private Sub cboItemLU_DblClick(Cancel As Integer)
    
        Dim stDocName As String
        Dim stLinkCriteria As String
        stDocName = "frmAddItem"
        
        DoCmd.OpenForm stDocName, acFormAdd, , , , acDialog
    
    End Sub
    This is the NotInList code that does not work.
    Code:
    Private Sub cboItemLU_NotInList(NewData As String, Response As Integer)
    
        Dim stDocName As String
        Dim stLinkCriteria As String
        stDocName = "frmAddItem"
        
        DoCmd.OpenForm stDocName, acFormAdd, , , , acDialog
    
    End Sub
    Yes they are almost exactly the same. I saw no reason they should not work the same. But I must be wrong.

    What is happening. When I type a new entry and then click Save and Close I get a Run Time error 2118:
    You must save the current field before you run the Requery action

    Debug takes me here:
    Code:
    Private Sub Form_AfterUpdate()
    Forms!frmInventory!cboItemLU.Requery
    End Sub
    This is code on the form called by both pieces of code above.

    This is the code to save and close the add record form:
    Code:
    Private Sub cmdCloseandSave_Click()
    On Error GoTo Err_cmdCloseandSave_Click
    
        DoCmd.Save
        DoCmd.Close
        
    Exit_cmdCloseandSave_Click:
        Exit Sub
    
    Err_cmdCloseandSave_Click:
        MsgBox Err.Description
        Resume Exit_cmdCloseandSave_Click
        
    End Sub
    Anyone have a thought on what is wrong? Thanks, Scott

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    Private Sub cboItemLU_NotInList(NewData As String, Response As Integer)
        
        DoCmd.OpenForm "frmAddItem", acNormal, , , acFormAdd, acDialog
        Response = acDataErrAdded
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2013
    Posts
    70
    Thanks, Sinndho. I went back a step and simplified it. I took out the double click add. Then used a tried and true NotInList code snippet that has worked for me before. I always think that it should be easy to just put it all in a quick line of code. Oh well. Not in this case. But the code I am using is working fine. I am curious why I got an error in the NotInList code when the double click code had the requery line and did not when I took it out. Thanks for the response. Best, Scott

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •