Cancel NotInList Error

  • Thread starter Thread starter David C. Holley
  • Start date Start date
D

David C. Holley

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
 
Still getting the message 'THE TEXT YOU ENTERED ISN'T AN ITEM IN THE LIST.'

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

David C. Holley said:
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
Click to expand...
Click to expand...
 
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 said:
Still getting the message 'THE TEXT YOU ENTERED ISN'T AN ITEM IN THE LIST.'

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

David C. Holley said:
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
Click to expand...
Click to expand...
Click to expand...
 
Minus the initial IF...THEN which does the lookup, the code works as-is.
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 said:
Still getting the message 'THE TEXT YOU ENTERED ISN'T AN ITEM IN THE LIST.'

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

:


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
Click to expand...
Click to expand...
Click to expand...
 
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.
Minus the initial IF...THEN which does the lookup, the code works as-is.
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 said:
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

:


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top