Solved NotInList error

[ClaraBarton]

I apparently don't understand the NotInList function. I have several comboboxes so I made a separate module sub called EditCombo to carry out various actions for different kinds of combos and then return to the line "Me.cboPayee = null", etc. Everything works except when the code runs to the End Select it brings up "The text you entered isn't an item it the list. Do you want to edit it?" No. I don't. I'm finished. I don't want that message. I want the combo to requery with the new data and it doesn't. I've tried various things but I don't know how to get rid of it. What am I missing here?

Private Sub cboPayee_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("Add to the name list?", vbYesNo, "Missing Name")
    
    Select Case intAnswer
        Case vbYes
            EditCombo Me, "cboPayee", "popNameInfo"
            Me.cboPayee = Null
            Me.cboPayee.Requery
        Case vbNo
            Response = acDataErrContinue
    End Select
  
End Sub

 

[arnelgp]

you forgot to set the Response to acDataErrAdded.

 

[ClaraBarton]

oh. Where should I put it? I tried it right after Edit Combo. I tried it after the requery. Which is a repeat, not?
I tried it after the whole select thing. Same thing. Same response.

 

[Gasman]

Why not look up the syntax?

ComboBox.NotInList event (Access)

Office VBA reference topic

learn.microsoft.com

 

[arnelgp]

...
...
    Select Case intAnswer
          Case vbYes
               EditCombo Me, "cboPayee", "popNameInfo"
               Response = acDataErrAdded
...
...

 

[ClaraBarton]

That's what I did. Still get the message.

Private Sub cboPayee_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("Add to the name list?", vbYesNo, "Missing Name")
    
    Select Case intAnswer
        Case vbYes
            EditCombo Me, "cboPayee", "popNameInfo"
            Response = acDataErrAdded
        Case vbNo
            Response = acDataErrContinue
    End Select
    
  
End Sub

 

[theDBguy]

Can you show us the code for EditCombo?

 

[ClaraBarton]

                'frm = This Form (me)
                'strCombo = This Combo (cboUserName)
                'strForm = the form used to add to the table or query
Public Sub EditCombo(frm As Form, strCombo As String, Optional strForm As String)

Dim strOpenArgs As String
Dim lCallsID As Long
Dim strSql As String
Dim tblqry As String
Dim tblfld As String
strOpenArgs = frm(strCombo).Text
frm(strCombo) = Null
lCallsID = DLookup("CBOCallsID", "tblCBOCalls", "CallingForm = '" & frm.name & "'")
         
 
If lCallsID <> 3 Or _
   lCallsID <> 4 Or _
   lCallsID <> 6 Then
        DoCmd.OpenForm strForm, _
            DataMode:=acFormAdd, _
            WindowMode:=acDialog, _
            OpenArgs:="New#" & strOpenArgs & "," & frm.name  'logic here?
        DoCmd.OpenForm strForm, _
            DataMode:=acFormAdd, _
            WindowMode:=acDialog, _
            OpenArgs:="New#" & strOpenArgs & "," & frm.name  'logic here?
    Else
        tblqry = DLookup("tblqry", "tblCBOCalls", "CBOCallsID = " & lCallsID)
        tblfld = DLookup("tblfld", "tblCBOCalls", "CBOCallsID = " & lCallsID)
        strSql = "INSERT INTO " & tblqry & " (" & tblfld & ") " & _
                "SELECT " & strOpenArgs & ""
            Debug.Print strSql
        CurrentDb.Execute strSql, dbFailOnError
    End If
     
            'Check if a value has been previously recorded and return that value to the Combo if it has
If lngInfoXchg <> 0 Then
    frm(strCombo) = lngInfoXchg
End If
'    frm(strCombo).Requery

End Sub

 

[theDBguy]

If lCallsID <> 3 Or 4 Or 6 Then

Have you stepped through your code? I am not certain the above line of code does what you may think/expect it's supposed to do. I'll try to test it later...

 

[Gasman]

I would have thought not, and that you needed to qualify each option?
Even then would you not need AND for any <> comparison?
You could try NOT IN(), but still walk your code.

 

[ClaraBarton]

Oh, you're right! It works for 3 which is what I've been testing.

 

[Gasman]

Walk your code. That will help a lot with your logic.

 

[ClaraBarton]

okaaay! The problem is actually in my pop up form, and not the NotInList. Although you helped me fix that too. Thank you for your guidance. I always try and Try and TRY to check everything before asking...

 

[ClaraBarton]

I've (again!) done my job and worked through this (walked the code) and I still need help

Private Sub cboPayee_NotInList(NewData As String, Response As Integer)
Dim intAnswer As Integer
intAnswer = MsgBox("Add to the name list?", vbYesNo, "Missing Name")
  
    Select Case intAnswer
        Case vbYes
            EditCombo Me, NewData, "popNameInfo"
            Response = acDataErrAdded
        Case vbNo
            Response = acDataErrContinue
    End Select
 
    Me.cboPayee = lngInfoXchg
   Me.cboPayee.Requery
End Sub

                'frm = This Form (me)
                'NewData = NotInList variable
                'strForm = the form used to add Data to the table or query
'Public Sub EditCombo(frm As Form, strCombo As String, Optional strForm As String)
Public Sub EditCombo(frm As Form, NewData As String, Optional strForm As String)

Dim strOpenArgs As String
Dim lCallsID As Long
Dim strSql As String
Dim tblqry As String
Dim tblfld As String

lCallsID = DLookup("CBOCallsID", "tblCBOCalls", "CallingForm = '" & frm.name & "'")
          
    If lCallsID <> 3 Or _
        lCallsID <> 4 Or _
        lCallsID <> 6 Then
        DoCmd.OpenForm strForm, _
            DataMode:=acFormAdd, _
            WindowMode:=acDialog, _
            OpenArgs:="New#" & NewData & "," & frm.name
    Else
        tblqry = DLookup("tblqry", "tblCBOCalls", "CBOCallsID = " & lCallsID)
        tblfld = DLookup("tblfld", "tblCBOCalls", "CBOCallsID = " & lCallsID)
        strSql = "INSERT INTO " & tblqry & " (" & tblfld & ") " & _
                "SELECT " & strOpenArgs & ""
            Debug.Print strSql
        CurrentDb.Execute strSql, dbFailOnError
    End If
      
End Sub

All variables are right and everything works. Form pops up with proper fields filled, returns the combo ID, etc... The first sub returns the right data and ID (in the variables) but when it's finished, the cbo is blank and I get the message "Do you want to add..."
And, yes I've read the combobox NotInList syntax. I'm 75 and things don't click like they used to.

 

[theDBguy]

If lCallsID <> 3 Or _ lCallsID <> 4 Or _ lCallsID <> 6 Then

You said you stepped through the code, but I still am not convinced the above update to the code does what you intended it to do. When CallsID is 3, then it's not 4, and when it's 4, then it's not 3. To me, the above code means it doesn't matter what CallsID is, that line of code will always return True.

Sent from phone...

 

[Gasman]

As you seem to be having problems with your logic and <>, why not swap it around and test for = as you did not read my post if at all, fully.

Just FYI using the immediate window

? 3 or 4 or 6
 7

? 3 and 4 and 6
 0

TBH I think you are trying to run before you can walk.

How often are these options for combos going to change? Only then would I need to see a need for your method, but I cannot see you wanting to change the form for a combo 6 months down the line. You might want to amend it for another control as you added another field to the table, but would keep the same form?
At least that is what I needed to do.

If you come back with a valid reason for having all this in a table, then I would love to hear the explanation?

 

[ClaraBarton]

If Not IsNull(" & strForm & ") Then ...

 

[Gasman]

How can strFom be Null. unless you set it as such?
A string variable is initialised as a ZLS?

If Not IsNull(strForm) Then ...

 

[ClaraBarton]

it's an optional variable. still working on it. Nevermind.
For anyone else with my original problem: I'd like to tell you what the problem was. Before I started all this NotInList code for my combos, I had set up a form in the "ListItemsEditForm" under data and forgot about it. After going through my code, this was kicking in.
I apologize and thank you for your help.