seanlhall
asked on
How can cancel a Not on List error in a combo box?
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
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
Private Sub Combo178_NotInList(NewData
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
this is not correct
DoCmd.setwarning = False
it should be
DoCmd.setwarnings False
DoCmd.setwarning = False
it should be
DoCmd.setwarnings False
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
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
ASKER
No luck I tried everything.
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')"
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')"
<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.
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.
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.
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.
ASKER
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.
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
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
seanlhall,
post the codes that YOU are using..
post the codes that YOU are using..
ASKER
Here is a test file. It will produce the same error. open frmsubject. NotinList.mdb
ASKER
If you want to type in a case type that you dont want to save in the lookup table it is not possible.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
then why not just set the limit to list property to NO.
ASKER
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.
ASKER
If I did that the Not on List Event does no work.
Private Sub Combo178_NotInList(NewData
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