The following code is attached to the OnNotInList Event of a combo box
that I have. The idea behind the code is to check wether or not the
value entered exists in a field DIFFERENT from the bound filed of the
combo box. The idea is that if I want to select DAVID C. HOLLEY, I can
enter dch3 (lan ID) and the comboBox would automatically be set to DAVID
C. HOLLEY. Since dch3 is not in the list and the LimitToList is set to
true, the OnNotInList fires which it should to allow a new name to be
entered. The function as originally written (without the initial If
DCount() then... statement) works, the problem is that once the value is
pulled from the table and put in the comboBox, Access returns an error
message that the value is not in the list.

How can I tell Access to ignore the error and supress the error message?
I tried Err.Clear, but the message persists?

FYI - The code was pulled from an example in Access help from a few
versions ago.

David H

Private Sub cboOrigination_NotInList(NewData As String, response As Integer)

On Error GoTo Err_cboOrigination_NotInList

If DCount("txtPPCLocationAlias", "tblLocations",
"[txtPPCLocationAlias] = '" & NewData & "'") = 1 Then
[Forms]![frmReservations]![cboOrigination] =
DLookup("txtLocationName", "tblLocations", "[txtPPCLocationAlias] = '" &
"'")
response = DATA_ERRADDED
Exit Sub
Else
MsgText = ""
MsgText = MsgText & "The location entered has not been" &
Chr$(10) & Chr$(13)
MsgText = MsgText & "set-up for use in the database. " &
Chr$(10) & Chr$(13)
MsgText = MsgText & "Do you want to add the location?"
If MsgBox(MsgText, 324) = 7 Then
MsgText = "Please select a location from the list."
MsgBox MsgText, 64
response = DATA_ERRCONTINUE
GoTo Exit_cboOrigination_NotInList
End If
DoCmd.Echo False
DoCmd.OpenForm "frmLocations", , , , A_ADD, A_DIALOG, Me.Name
DoCmd.Echo True
If [Forms]![frmReservations]![response] = DATA_ERRADDED Then
'Test if a new record has been added
'Set the combo box here
[Forms]![frmReservations]![response] = DATA_ERRCONTINUE
response = DATA_ERRADDED
[Forms]![frmReservations]![cboDestination].Requery
Exit Sub
End If
MsgText = "Please select a location from the list."
MsgBox MsgText, 64
response = DATA_ERRCONTINUE
Exit Sub
End If

Exit_cboOrigination_NotInList:
DoCmd.Hourglass False
[Forms]![frmReservations]![response] = DATA_ERRCONTINUE
Exit Sub

Err_cboOrigination_NotInList:
Exit Sub


End Sub

Try using the constant acDataErrAdded in place of Data_ErrAdded and
acDataErrContinue for Data_ErrContinue

"David C. Holley" wrote:
>
> The following code is attached to the OnNotInList Event of a combo box
> that I have. The idea behind the code is to check wether or not the
> value entered exists in a field DIFFERENT from the bound filed of the
> combo box. The idea is that if I want to select DAVID C. HOLLEY, I can
> enter dch3 (lan ID) and the comboBox would automatically be set to DAVID
> C. HOLLEY. Since dch3 is not in the list and the LimitToList is set to
> true, the OnNotInList fires which it should to allow a new name to be
> entered. The function as originally written (without the initial If
> DCount() then... statement) works, the problem is that once the value is
> pulled from the table and put in the comboBox, Access returns an error
> message that the value is not in the list.
>
> How can I tell Access to ignore the error and supress the error message?
> I tried Err.Clear, but the message persists?
>
> FYI - The code was pulled from an example in Access help from a few
> versions ago.
>
> David H
>
> Private Sub cboOrigination_NotInList(NewData As String, response As Integer)
>
> On Error GoTo Err_cboOrigination_NotInList
>
> If DCount("txtPPCLocationAlias", "tblLocations",
> "[txtPPCLocationAlias] = '" & NewData & "'") = 1 Then
> [Forms]![frmReservations]![cboOrigination] =
> DLookup("txtLocationName", "tblLocations", "[txtPPCLocationAlias] = '" &
> "'")
> response = DATA_ERRADDED
> Exit Sub
> Else
> MsgText = ""
> MsgText = MsgText & "The location entered has not been" &
> Chr$(10) & Chr$(13)
> MsgText = MsgText & "set-up for use in the database. " &
> Chr$(10) & Chr$(13)
> MsgText = MsgText & "Do you want to add the location?"
> If MsgBox(MsgText, 324) = 7 Then
> MsgText = "Please select a location from the list."
> MsgBox MsgText, 64
> response = DATA_ERRCONTINUE
> GoTo Exit_cboOrigination_NotInList
> End If
> DoCmd.Echo False
> DoCmd.OpenForm "frmLocations", , , , A_ADD, A_DIALOG, Me.Name
> DoCmd.Echo True
> If [Forms]![frmReservations]![response] = DATA_ERRADDED Then
> 'Test if a new record has been added
> 'Set the combo box here
> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
> response = DATA_ERRADDED
> [Forms]![frmReservations]![cboDestination].Requery
> Exit Sub
> End If
> MsgText = "Please select a location from the list."
> MsgBox MsgText, 64
> response = DATA_ERRCONTINUE
> Exit Sub
> End If
>
> Exit_cboOrigination_NotInList:
> DoCmd.Hourglass False
> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
> Exit Sub
>
> Err_cboOrigination_NotInList:
> Exit Sub
>
> End Sub

Still getting the message 'THE TEXT YOU ENTERED ISN'T AN ITEM IN THE LIST.'

David H

John Spencer (MVP) wrote:
> Try using the constant acDataErrAdded in place of Data_ErrAdded and
> acDataErrContinue for Data_ErrContinue
>
> "David C. Holley" wrote:
>
>>The following code is attached to the OnNotInList Event of a combo box
>>that I have. The idea behind the code is to check wether or not the
>>value entered exists in a field DIFFERENT from the bound filed of the
>>combo box. The idea is that if I want to select DAVID C. HOLLEY, I can
>>enter dch3 (lan ID) and the comboBox would automatically be set to DAVID
>>C. HOLLEY. Since dch3 is not in the list and the LimitToList is set to
>>true, the OnNotInList fires which it should to allow a new name to be
>>entered. The function as originally written (without the initial If
>>DCount() then... statement) works, the problem is that once the value is
>>pulled from the table and put in the comboBox, Access returns an error
>>message that the value is not in the list.
>>
>>How can I tell Access to ignore the error and supress the error message?
>>I tried Err.Clear, but the message persists?
>>
>>FYI - The code was pulled from an example in Access help from a few
>>versions ago.
>>
>>David H
>>
>>Private Sub cboOrigination_NotInList(NewData As String, response As Integer)
>>
>>On Error GoTo Err_cboOrigination_NotInList
>>
>> If DCount("txtPPCLocationAlias", "tblLocations",
>>"[txtPPCLocationAlias] = '" & NewData & "'") = 1 Then
>> [Forms]![frmReservations]![cboOrigination] =
>>DLookup("txtLocationName", "tblLocations", "[txtPPCLocationAlias] = '" &
>>"'")
>> response = DATA_ERRADDED
>> Exit Sub
>> Else
>> MsgText = ""
>> MsgText = MsgText & "The location entered has not been" &
>>Chr$(10) & Chr$(13)
>> MsgText = MsgText & "set-up for use in the database. " &
>>Chr$(10) & Chr$(13)
>> MsgText = MsgText & "Do you want to add the location?"
>> If MsgBox(MsgText, 324) = 7 Then
>> MsgText = "Please select a location from the list."
>> MsgBox MsgText, 64
>> response = DATA_ERRCONTINUE
>> GoTo Exit_cboOrigination_NotInList
>> End If
>> DoCmd.Echo False
>> DoCmd.OpenForm "frmLocations", , , , A_ADD, A_DIALOG, Me.Name
>> DoCmd.Echo True
>> If [Forms]![frmReservations]![response] = DATA_ERRADDED Then
>> 'Test if a new record has been added
>> 'Set the combo box here
>> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
>> response = DATA_ERRADDED
>> [Forms]![frmReservations]![cboDestination].Requery
>> Exit Sub
>> End If
>> MsgText = "Please select a location from the list."
>> MsgBox MsgText, 64
>> response = DATA_ERRCONTINUE
>> Exit Sub
>> End If
>>
>>Exit_cboOrigination_NotInList:
>> DoCmd.Hourglass False
>> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
>> Exit Sub
>>
>>Err_cboOrigination_NotInList:
>> Exit Sub
>>
>>End Sub

Hmm, are you using the correct constant in the correct place. If you add a new
item then you should be setting "Response" to acDataErrAdded. If you are not
adding data and you have handled the error in your code, then you should be
setting "Response: to acDataErrContinue.

I apologize for not examining your code more closely.

"David C. Holley" wrote:
>
> Still getting the message 'THE TEXT YOU ENTERED ISN'T AN ITEM IN THE LIST.'
>
> David H
>
> John Spencer (MVP) wrote:
> > Try using the constant acDataErrAdded in place of Data_ErrAdded and
> > acDataErrContinue for Data_ErrContinue
> >
> > "David C. Holley" wrote:
> >
> >>The following code is attached to the OnNotInList Event of a combo box
> >>that I have. The idea behind the code is to check wether or not the
> >>value entered exists in a field DIFFERENT from the bound filed of the
> >>combo box. The idea is that if I want to select DAVID C. HOLLEY, I can
> >>enter dch3 (lan ID) and the comboBox would automatically be set to DAVID
> >>C. HOLLEY. Since dch3 is not in the list and the LimitToList is set to
> >>true, the OnNotInList fires which it should to allow a new name to be
> >>entered. The function as originally written (without the initial If
> >>DCount() then... statement) works, the problem is that once the value is
> >>pulled from the table and put in the comboBox, Access returns an error
> >>message that the value is not in the list.
> >>
> >>How can I tell Access to ignore the error and supress the error message?
> >>I tried Err.Clear, but the message persists?
> >>
> >>FYI - The code was pulled from an example in Access help from a few
> >>versions ago.
> >>
> >>David H
> >>
> >>Private Sub cboOrigination_NotInList(NewData As String, response As Integer)
> >>
> >>On Error GoTo Err_cboOrigination_NotInList
> >>
> >> If DCount("txtPPCLocationAlias", "tblLocations",
> >>"[txtPPCLocationAlias] = '" & NewData & "'") = 1 Then
> >> [Forms]![frmReservations]![cboOrigination] =
> >>DLookup("txtLocationName", "tblLocations", "[txtPPCLocationAlias] = '" &
> >>"'")
> >> response = DATA_ERRADDED
> >> Exit Sub
> >> Else
> >> MsgText = ""
> >> MsgText = MsgText & "The location entered has not been" &
> >>Chr$(10) & Chr$(13)
> >> MsgText = MsgText & "set-up for use in the database. " &
> >>Chr$(10) & Chr$(13)
> >> MsgText = MsgText & "Do you want to add the location?"
> >> If MsgBox(MsgText, 324) = 7 Then
> >> MsgText = "Please select a location from the list."
> >> MsgBox MsgText, 64
> >> response = DATA_ERRCONTINUE
> >> GoTo Exit_cboOrigination_NotInList
> >> End If
> >> DoCmd.Echo False
> >> DoCmd.OpenForm "frmLocations", , , , A_ADD, A_DIALOG, Me.Name
> >> DoCmd.Echo True
> >> If [Forms]![frmReservations]![response] = DATA_ERRADDED Then
> >> 'Test if a new record has been added
> >> 'Set the combo box here
> >> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
> >> response = DATA_ERRADDED
> >> [Forms]![frmReservations]![cboDestination].Requery
> >> Exit Sub
> >> End If
> >> MsgText = "Please select a location from the list."
> >> MsgBox MsgText, 64
> >> response = DATA_ERRCONTINUE
> >> Exit Sub
> >> End If
> >>
> >>Exit_cboOrigination_NotInList:
> >> DoCmd.Hourglass False
> >> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
> >> Exit Sub
> >>
> >>Err_cboOrigination_NotInList:
> >> Exit Sub
> >>
> >>End Sub

Minus the initial IF...THEN which does the lookup, the code works as-is.

John Spencer (MVP) wrote:
> Hmm, are you using the correct constant in the correct place. If you add a new
> item then you should be setting "Response" to acDataErrAdded. If you are not
> adding data and you have handled the error in your code, then you should be
> setting "Response: to acDataErrContinue.
>
> I apologize for not examining your code more closely.
>
> "David C. Holley" wrote:
>
>>Still getting the message 'THE TEXT YOU ENTERED ISN'T AN ITEM IN THE LIST.'
>>
>>David H
>>
>>John Spencer (MVP) wrote:
>>
>>>Try using the constant acDataErrAdded in place of Data_ErrAdded and
>>>acDataErrContinue for Data_ErrContinue
>>>
>>>"David C. Holley" wrote:
>>>
>>>
>>>>The following code is attached to the OnNotInList Event of a combo box
>>>>that I have. The idea behind the code is to check wether or not the
>>>>value entered exists in a field DIFFERENT from the bound filed of the
>>>>combo box. The idea is that if I want to select DAVID C. HOLLEY, I can
>>>>enter dch3 (lan ID) and the comboBox would automatically be set to DAVID
>>>>C. HOLLEY. Since dch3 is not in the list and the LimitToList is set to
>>>>true, the OnNotInList fires which it should to allow a new name to be
>>>>entered. The function as originally written (without the initial If
>>>>DCount() then... statement) works, the problem is that once the value is
>>>>pulled from the table and put in the comboBox, Access returns an error
>>>>message that the value is not in the list.
>>>>
>>>>How can I tell Access to ignore the error and supress the error message?
>>>>I tried Err.Clear, but the message persists?
>>>>
>>>>FYI - The code was pulled from an example in Access help from a few
>>>>versions ago.
>>>>
>>>>David H
>>>>
>>>>Private Sub cboOrigination_NotInList(NewData As String, response As Integer)
>>>>
>>>>On Error GoTo Err_cboOrigination_NotInList
>>>>
>>>> If DCount("txtPPCLocationAlias", "tblLocations",
>>>>"[txtPPCLocationAlias] = '" & NewData & "'") = 1 Then
>>>> [Forms]![frmReservations]![cboOrigination] =
>>>>DLookup("txtLocationName", "tblLocations", "[txtPPCLocationAlias] = '" &
>>>>"'")
>>>> response = DATA_ERRADDED
>>>> Exit Sub
>>>> Else
>>>> MsgText = ""
>>>> MsgText = MsgText & "The location entered has not been" &
>>>>Chr$(10) & Chr$(13)
>>>> MsgText = MsgText & "set-up for use in the database. " &
>>>>Chr$(10) & Chr$(13)
>>>> MsgText = MsgText & "Do you want to add the location?"
>>>> If MsgBox(MsgText, 324) = 7 Then
>>>> MsgText = "Please select a location from the list."
>>>> MsgBox MsgText, 64
>>>> response = DATA_ERRCONTINUE
>>>> GoTo Exit_cboOrigination_NotInList
>>>> End If
>>>> DoCmd.Echo False
>>>> DoCmd.OpenForm "frmLocations", , , , A_ADD, A_DIALOG, Me.Name
>>>> DoCmd.Echo True
>>>> If [Forms]![frmReservations]![response] = DATA_ERRADDED Then
>>>> 'Test if a new record has been added
>>>> 'Set the combo box here
>>>> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
>>>> response = DATA_ERRADDED
>>>> [Forms]![frmReservations]![cboDestination].Requery
>>>> Exit Sub
>>>> End If
>>>> MsgText = "Please select a location from the list."
>>>> MsgBox MsgText, 64
>>>> response = DATA_ERRCONTINUE
>>>> Exit Sub
>>>> End If
>>>>
>>>>Exit_cboOrigination_NotInList:
>>>> DoCmd.Hourglass False
>>>> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
>>>> Exit Sub
>>>>
>>>>Err_cboOrigination_NotInList:
>>>> Exit Sub
>>>>
>>>>End Sub

The problem was that I was using the wrong value for the response in the
initial IF..THEN, after consulting Access Help*, I realized that
acDataErrContinue would do the trick. I will NEVER complain about people
complaining about legacy code - the original routine was written nearly
seven years ago.

David H
*Never thought that Help in a Microsoft product would actually be helpful.

David C. Holley wrote:
> Minus the initial IF...THEN which does the lookup, the code works as-is.
>
> John Spencer (MVP) wrote:
>
>> Hmm, are you using the correct constant in the correct place. If you
>> add a new
>> item then you should be setting "Response" to acDataErrAdded. If you
>> are not
>> adding data and you have handled the error in your code, then you
>> should be
>> setting "Response: to acDataErrContinue.
>> I apologize for not examining your code more closely.
>> "David C. Holley" wrote:
>>
>>> Still getting the message 'THE TEXT YOU ENTERED ISN'T AN ITEM IN THE
>>> LIST.'
>>>
>>> David H
>>>
>>> John Spencer (MVP) wrote:
>>>
>>>> Try using the constant acDataErrAdded in place of Data_ErrAdded and
>>>> acDataErrContinue for Data_ErrContinue
>>>>
>>>> "David C. Holley" wrote:
>>>>
>>>>
>>>>> The following code is attached to the OnNotInList Event of a combo box
>>>>> that I have. The idea behind the code is to check wether or not the
>>>>> value entered exists in a field DIFFERENT from the bound filed of the
>>>>> combo box. The idea is that if I want to select DAVID C. HOLLEY, I can
>>>>> enter dch3 (lan ID) and the comboBox would automatically be set to
>>>>> DAVID
>>>>> C. HOLLEY. Since dch3 is not in the list and the LimitToList is set to
>>>>> true, the OnNotInList fires which it should to allow a new name to be
>>>>> entered. The function as originally written (without the initial If
>>>>> DCount() then... statement) works, the problem is that once the
>>>>> value is
>>>>> pulled from the table and put in the comboBox, Access returns an error
>>>>> message that the value is not in the list.
>>>>>
>>>>> How can I tell Access to ignore the error and supress the error
>>>>> message?
>>>>> I tried Err.Clear, but the message persists?
>>>>>
>>>>> FYI - The code was pulled from an example in Access help from a few
>>>>> versions ago.
>>>>>
>>>>> David H
>>>>>
>>>>> Private Sub cboOrigination_NotInList(NewData As String, response As
>>>>> Integer)
>>>>>
>>>>> On Error GoTo Err_cboOrigination_NotInList
>>>>>
>>>>> If DCount("txtPPCLocationAlias", "tblLocations",
>>>>> "[txtPPCLocationAlias] = '" & NewData & "'") = 1 Then
>>>>> [Forms]![frmReservations]![cboOrigination] =
>>>>> DLookup("txtLocationName", "tblLocations", "[txtPPCLocationAlias] =
>>>>> '" &
>>>>> "'")
>>>>> response = DATA_ERRADDED
>>>>> Exit Sub
>>>>> Else
>>>>> MsgText = ""
>>>>> MsgText = MsgText & "The location entered has not been" &
>>>>> Chr$(10) & Chr$(13)
>>>>> MsgText = MsgText & "set-up for use in the database. " &
>>>>> Chr$(10) & Chr$(13)
>>>>> MsgText = MsgText & "Do you want to add the location?"
>>>>> If MsgBox(MsgText, 324) = 7 Then
>>>>> MsgText = "Please select a location from the list."
>>>>> MsgBox MsgText, 64
>>>>> response = DATA_ERRCONTINUE
>>>>> GoTo Exit_cboOrigination_NotInList
>>>>> End If
>>>>> DoCmd.Echo False
>>>>> DoCmd.OpenForm "frmLocations", , , , A_ADD, A_DIALOG, Me.Name
>>>>> DoCmd.Echo True
>>>>> If [Forms]![frmReservations]![response] = DATA_ERRADDED Then
>>>>> 'Test if a new record has been added
>>>>> 'Set the combo box here
>>>>> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
>>>>> response = DATA_ERRADDED
>>>>> [Forms]![frmReservations]![cboDestination].Requery
>>>>> Exit Sub
>>>>> End If
>>>>> MsgText = "Please select a location from the list."
>>>>> MsgBox MsgText, 64
>>>>> response = DATA_ERRCONTINUE
>>>>> Exit Sub
>>>>> End If
>>>>>
>>>>> Exit_cboOrigination_NotInList:
>>>>> DoCmd.Hourglass False
>>>>> [Forms]![frmReservations]![response] = DATA_ERRCONTINUE
>>>>> Exit Sub
>>>>>
>>>>> Err_cboOrigination_NotInList:
>>>>> Exit Sub
>>>>>
>>>>> End Sub