Remove or edit message "The text you entered isn't an item in the list'

[pbouk]

Hi there,

I have a series of combo boxes on my form. If the user adds a new entry that isn't in the list, a message comes up asking them if they want to add it, but if they select 'No', the error message "The text you entered isn't an item in the list..etc" pops up.

I'd like to suppress this message, OR edit it (a message is fine, but this one is unclear and repetitive).

So, ideally I'd like a message to appear remindning the user to add something from the list, and then, once they say OK, have the code clear the combo box ready for a new selection.

Does anyone have any suggestions?

Here is my code:

Private Sub Categories_NotInList(NewData As String, Response As Integer)
Dim strSQL1 As String
Dim i As Integer
Dim Msg As String


'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"


i = MsgBox(Msg, vbQuestion + vbYesNo, "Category")
If i = vbYes Then
strSQL1 = "Insert Into tblCategories ([Category]) " & _
"values ('" & NewData & "');"


CurrentDb.Execute strSQL1, dbFailOnError
Response = acDataErrAdded

End If






End Sub

[burrina]

Cancel = True

[pbouk]

Thank you for your response, but can you explain further?
Apologies, this is the first database I've built so I'm fumbling around...

[burrina]

If i = vbNo Then
Cancel = True


This should get rid of the message!

Also you may wish to try and add

DoCmd.SetWarnings False

[pbouk]

Hi there,
If you could possibly add it to my code, that would be great. I've tried placing it in there, and it's not working, so it's probably not in the right place.

Here is the code again, and you'll see where I've placed it:

Private Sub Combo80_NotInList(NewData As String, Response As Integer)
Dim strSQL1 As String
Dim i As Integer
Dim Msg As String


'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub


Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"


i = MsgBox(Msg, vbQuestion + vbYesNo, "City")
If i = vbYes Then
strSQL1 = "Insert Into tbluCity ([CityName]) " & _
"values ('" & NewData & "');"

If i = vbNo Then Cancel = True

CurrentDb.Execute strSQL1, dbFailOnError
Response = acDataErrContinue
DoCmd.SetWarnings False


End If




End Sub

If i = vbNo Then
Cancel = True


This should get rid of the message!

Also you may wish to try and add

DoCmd.SetWarnings False

[burrina]

You mean you want the record to be added automatically without asking the user because this is what your code does, it asks?

[pbouk]

Well....the code tells the user it's not in the list, and do they want to add it? If they say yes, well and good, the code works fine, they can add it.

But if the user doesn't want to add it (because the entry has actually been typed incorrectly, for example), the user clicks 'no', but then the automatic error message "The text you entered isn't an item in the list..." comes up.

Now that I think about it, it could be because the code doesn't immediately clear the entry? So if the code included something that cleared the mistyped entry (or whatever mistake the user has made) as soon as the user clicks "no", then that error message would not be triggered?

Am I making sense?

[burrina]

' If user chooses Cancel, suppress error message and undo changes.

If i = vbNo Then
Response = acDataErrContinue
Me.Combo80.Undo
End If

[pbouk]

I finally had a chance to play around with this and it's working! thanks so much.

[burrina]

Your Welcome.