Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How can cancel a Not on List error in a combo box?

Posted on 2011-02-22
18
Medium Priority
?
358 Views
Last Modified: 2012-06-27
I have a combo box that has a control source called casestatus. If a value entered is not on the list and event fires. If I click ok it entered the new value in the lookup table. If I cancel I keep getting promt with the not on list error form access. How can I click cancel on adding to lookup tale and move on?
Private Sub Combo178_NotInList(NewData As String, Response As Integer)
   Dim strTmp As String
    DoCmd.setwarning = False

    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Case Type?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
    
        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO tblSubLookup ( Thetext, TheDropdown) " & _
            "SELECT """ & NewData & """ AS TheText,('Case Type');"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
        
        Else
                Exit Sub
    
        'Notify Access about the new record, so it requeries the combo.
        Response = acDataErrAdded
        
               
    End If

End Sub

Open in new window

0
Comment
Question by:seanlhall
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
18 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34957556
try this

Private Sub Combo178_NotInList(NewData As String, Response As Integer)
   Dim strTmp As String
    DoCmd.setwarning = False

    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Case Type?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
   
        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO tblSubLookup ( Thetext, TheDropdown) " & _
            "SELECT """ & NewData & """ AS TheText,('Case Type');"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
       
        Else
 
   
        'Notify Access about the new record, so it requeries the combo.
       ' Response = acDataErrAdded
       
         Response = acDataErrContinue

        Exit Sub        
               
    End If

End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34957564
or this one

Private Sub Combo178_NotInList(NewData As String, Response As Integer)
   Dim strTmp As String
    DoCmd.setwarning = False

    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Case Type?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
   
        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO tblSubLookup ( Thetext, TheDropdown) " & _
            "SELECT """ & NewData & """ AS TheText,('Case Type');"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
       
        Else
 
   
        'Notify Access about the new record, so it requeries the combo.
       ' Response = acDataErrAdded

        me.Combo178=""
         Response = acDataErrContinue

        Exit Sub        
               
    End If

End Sub
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34957581
this is not correct
      DoCmd.setwarning = False

it should be  

       DoCmd.setwarnings  False
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34957671
also, if you set this
 DoCmd.setwarnings  False


you must trun that to true after using it

 DoCmd.setwarnings  true

in your codes you really don't need it



Private Sub Combo178_NotInList(NewData As String, Response As Integer)
   Dim strTmp As String


    'Get confirmation that this is not just a spelling error.
    strTmp = "Add '" & NewData & "' as a new Case Type?"
    If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
   
        'Append the NewData as a record in the Categories table.
        strTmp = "INSERT INTO tblSubLookup ( Thetext, TheDropdown) " & _
            "SELECT """ & NewData & """ AS TheText,('Case Type');"
        DBEngine(0)(0).Execute strTmp, dbFailOnError
       
        Else
 
   
        'Notify Access about the new record, so it requeries the combo.
       ' Response = acDataErrAdded

        me.Combo178=""
         Response = acDataErrContinue

        Exit Sub        
               
    End If

End Sub

Open in new window

0
 

Author Comment

by:seanlhall
ID: 34957826
No luck I tried everything.
0
 
LVL 85
ID: 34957884
You need to include a second response OUTSIDE the If block for your MsgBox:

If MsgBox(strTmp, vbYesNo + vbDefaultButton2 + vbQuestion, "Not in list") = vbYes Then
  <your code here>
   
   Exit Sub        
   
Else
  Response = acDataErrContinue            
End If

Also, you really don't need the "SELECT" in your insert statement. It can be written like this:

strTmp = "INSERT INTO tblSubLookup (Thetext, TheDropdown) VALUES('" & NewData & "', 'Case Type')"



0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34957903
<No luck I tried everything. >

what exactly do you mean?

this is the problem you posted
   <If I cancel I keep getting promt with the not on list error form access. How can I click cancel on adding to lookup tale and move on?>

and the codes i revised prevents the error.
0
 
LVL 85
ID: 34960489
Exactly what do you mean by "Cancel"? Your MsgBox shows Yes and No buttons. Do you mean you get this error when you click "No", or do you get it when you do something else?

Note also that my suggestion is the same as Cap's - i.e. including the acDataErrContinue in the Else section of your IF msgBox block. I just couldn't read it, with the indentations of the code block. Only change I'd make would be to remove the "Exit Sub" from that block, since it's meaningless (and could cause unexpected results). Your logic flow will take you to the End Sub statement eventually, so unless you have a specific reason to use "Exit Sub", it's best to remove it.
0
 

Author Comment

by:seanlhall
ID: 34960872
The error I receive after clicking NO is the following. "The text you entered isn't an item in the list" "Select an item in the list or select an item in the list. I think it is because the the limit to list property is yes. If I select no on this property the not in list event will not fire.
0
 
LVL 85
ID: 34961040
If you set a Breakpoint on this line:

Response = acDataErrcontinue

Do you actually get to that line?

You might also try commenting out this line:

me.Combo178=""

or replace it with this:

Me.Combo178.Undo
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34961173
seanlhall,

post the codes that YOU are using..
0
 

Author Comment

by:seanlhall
ID: 34962788
Here is a test file. It will produce the same error. open frmsubject. NotinList.mdb
0
 

Author Comment

by:seanlhall
ID: 34962801
If you want to type in a case type that you dont want to save in the lookup table it is not possible.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 34962874
test this and post your comment
NotinList.mdb
0
 

Author Comment

by:seanlhall
ID: 34962997
When I type something that is not on the list, i get the popup to add it or not. If I chose no it clears the box. I want to click no and move on storing the value in the control source case status and not store it in the lookup table.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34963023
then why not just set the limit to list property to NO.
0
 

Author Comment

by:seanlhall
ID: 34963024
I am going to use what you sent. If the user does not like what they have added to the lookup table they can just remove it later. Clearing the combo forces them to select a value from the list or adding on to it with out getting the error. Thanks.
0
 

Author Comment

by:seanlhall
ID: 34963113
If I did that the Not on List Event does no work.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

730 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