Non-Unique value error

  • Thread starter Thread starter Cameron Dockstader
  • Start date Start date
C

Cameron Dockstader

I have a funtion 'CheckEENum()' set as the BeforeUpdate property on an
'Employee Add' type of form. As far as I can tell, the function runs as
advertised, alerting the data entry team member that the Employee Number
entered is already in use. However, on the first atempt to enter a
non-unique value, the user also gets the following errer:

'The value in the field or record violates the validation rule for the
record or field.'

This error is obviously not needed, and confuses the data entry person, does
anyone know how to supress it? I've tried SetWarnings = false, and do not
use the Msgbox Err.Description function at all. I'm sure it's something
simple I've never ran into.

Thanks!


Function CheckEENum()
On Error GoTo ErrHand
DoCmd.SetWarnings False
Dim frm As String
Dim var As Variant
Dim frmB As Form
frm = Screen.ActiveForm.Name
frm = "Forms!" & frm
var = (DLookup("EmployeeNumber", "tblEmployees", "IDEmployee = " & frm &
"![IDEmployee]"))
Set frmB = Screen.ActiveForm
If frmB!EmployeeNumber = var Then
Exit Function
Else
If IsNull(DLookup("LName", "tblEmployees", "[EmployeeNumber] = " &
frm & "!EmployeeNumber")) = False Then
MsgBox "The Number Entered Is Currently In Use, Please Enter A
Unique Number"
DoCmd.CancelEvent
End If
End If
Exit Function

ErrHand:
ErrMsg ("eM-404")
End Function
 
Cameron,
It would be best to have a look at the table that the EENum is stored in.
Make sure you understand what the validation rule is for the field in
question. It sounds as if it is more than just demanding unique values. If
after you read the validation rule, you decide that it is not needed, you
can remove it. If however there is a good reason for the validation rule to
exist, then write a function to check if newly entered EENums comply - this
would be similar to the CheckEENum function but it would check the
validation rule and if the number doesn't pass, it would pop up a message
box to tell the user about the problem.

Jeanette Cunningham
 
Thanks for your response!

That's part of the reason I'm so confused by the secondary error. There is
not validation rule for the EENum field in the table, or on the form.

There is an input mask "P00"999999;0;_ and is set to index with no
duplicates. Should I set the index to duplicates allowed and rely on the
CheckEENum function?

Jeanette Cunningham said:
Cameron,
It would be best to have a look at the table that the EENum is stored in.
Make sure you understand what the validation rule is for the field in
question. It sounds as if it is more than just demanding unique values. If
after you read the validation rule, you decide that it is not needed, you
can remove it. If however there is a good reason for the validation rule to
exist, then write a function to check if newly entered EENums comply - this
would be similar to the CheckEENum function but it would check the
validation rule and if the number doesn't pass, it would pop up a message
box to tell the user about the problem.

Jeanette Cunningham

Cameron Dockstader said:
I have a funtion 'CheckEENum()' set as the BeforeUpdate property on an
'Employee Add' type of form. As far as I can tell, the function runs as
advertised, alerting the data entry team member that the Employee Number
entered is already in use. However, on the first atempt to enter a
non-unique value, the user also gets the following errer:

'The value in the field or record violates the validation rule for the
record or field.'

This error is obviously not needed, and confuses the data entry person,
does
anyone know how to supress it? I've tried SetWarnings = false, and do not
use the Msgbox Err.Description function at all. I'm sure it's something
simple I've never ran into.

Thanks!


Function CheckEENum()
On Error GoTo ErrHand
DoCmd.SetWarnings False
Dim frm As String
Dim var As Variant
Dim frmB As Form
frm = Screen.ActiveForm.Name
frm = "Forms!" & frm
var = (DLookup("EmployeeNumber", "tblEmployees", "IDEmployee = " & frm
&
"![IDEmployee]"))
Set frmB = Screen.ActiveForm
If frmB!EmployeeNumber = var Then
Exit Function
Else
If IsNull(DLookup("LName", "tblEmployees", "[EmployeeNumber] = " &
frm & "!EmployeeNumber")) = False Then
MsgBox "The Number Entered Is Currently In Use, Please Enter A
Unique Number"
DoCmd.CancelEvent
End If
End If
Exit Function

ErrHand:
ErrMsg ("eM-404")
End Function
Click to expand...
Click to expand...
 
Doc,
Keep the index as unique, no duplicates.
Try removing the input mask to see if that is causing a problem.
You can trap the error using the form's OnError event.

This code will show you the error number when you run the form.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
msgbox Err.Number & " " & Err.Description
End Sub

When you know the error number you can put code like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If Err.Number = 0000 Then
'handle error here
End If
End Sub

Replace 0000 with the error number from above.

Jeanette Cunningham

Doc said:
Thanks for your response!

That's part of the reason I'm so confused by the secondary error. There
is
not validation rule for the EENum field in the table, or on the form.

There is an input mask "P00"999999;0;_ and is set to index with no
duplicates. Should I set the index to duplicates allowed and rely on the
CheckEENum function?

Jeanette Cunningham said:
Cameron,
It would be best to have a look at the table that the EENum is stored in.
Make sure you understand what the validation rule is for the field in
question. It sounds as if it is more than just demanding unique values.
If
after you read the validation rule, you decide that it is not needed, you
can remove it. If however there is a good reason for the validation rule
to
exist, then write a function to check if newly entered EENums comply -
this
would be similar to the CheckEENum function but it would check the
validation rule and if the number doesn't pass, it would pop up a message
box to tell the user about the problem.

Jeanette Cunningham

in
message news:[email protected]...
I have a funtion 'CheckEENum()' set as the BeforeUpdate property on an
'Employee Add' type of form. As far as I can tell, the function runs
as
advertised, alerting the data entry team member that the Employee
Number
entered is already in use. However, on the first atempt to enter a
non-unique value, the user also gets the following errer:

'The value in the field or record violates the validation rule for the
record or field.'

This error is obviously not needed, and confuses the data entry person,
does
anyone know how to supress it? I've tried SetWarnings = false, and do
not
use the Msgbox Err.Description function at all. I'm sure it's
something
simple I've never ran into.

Thanks!


Function CheckEENum()
On Error GoTo ErrHand
DoCmd.SetWarnings False
Dim frm As String
Dim var As Variant
Dim frmB As Form
frm = Screen.ActiveForm.Name
frm = "Forms!" & frm
var = (DLookup("EmployeeNumber", "tblEmployees", "IDEmployee = " &
frm
&
"![IDEmployee]"))
Set frmB = Screen.ActiveForm
If frmB!EmployeeNumber = var Then
Exit Function
Else
If IsNull(DLookup("LName", "tblEmployees", "[EmployeeNumber] = "
&
frm & "!EmployeeNumber")) = False Then
MsgBox "The Number Entered Is Currently In Use, Please Enter
A
Unique Number"
DoCmd.CancelEvent
End If
End If
Exit Function

ErrHand:
ErrMsg ("eM-404")
End Function
Click to expand...
Click to expand...
Click to expand...
 
I'd tend to go along with your line of thinking, Doc. The reason you're
getting the warning message is that you're checking for duplicates in two
different places, at form AND table level. Unless there's a possibility of
data being entered from a source other than your form, I'd depend on your
form's dup checking routine and remove the "No duplicates" from the table def.
 
Thanks for the response!

I removed the input mask, no changed noted. Tried the Err.Number
suggestion, nothing ever came up. It doesn't seem to be triggering an error
on the form?? I took the AfterUpdate code off of the field, and entered a
duplicate number, and the same error was produced, only right before adding
the record instead of before updating the field. I removed the index, and
reapplied the code on the AfterUpdate on the field, and it seems to work
correctly. However, this approach kind of scares me, but it works for now. .
..

Any other ideas?

Jeanette Cunningham said:
Doc,
Keep the index as unique, no duplicates.
Try removing the input mask to see if that is causing a problem.
You can trap the error using the form's OnError event.

This code will show you the error number when you run the form.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
msgbox Err.Number & " " & Err.Description
End Sub

When you know the error number you can put code like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If Err.Number = 0000 Then
'handle error here
End If
End Sub

Replace 0000 with the error number from above.

Jeanette Cunningham

Doc said:
Thanks for your response!

That's part of the reason I'm so confused by the secondary error. There
is
not validation rule for the EENum field in the table, or on the form.

There is an input mask "P00"999999;0;_ and is set to index with no
duplicates. Should I set the index to duplicates allowed and rely on the
CheckEENum function?

Jeanette Cunningham said:
Cameron,
It would be best to have a look at the table that the EENum is stored in.
Make sure you understand what the validation rule is for the field in
question. It sounds as if it is more than just demanding unique values.
If
after you read the validation rule, you decide that it is not needed, you
can remove it. If however there is a good reason for the validation rule
to
exist, then write a function to check if newly entered EENums comply -
this
would be similar to the CheckEENum function but it would check the
validation rule and if the number doesn't pass, it would pop up a message
box to tell the user about the problem.

Jeanette Cunningham

in
message I have a funtion 'CheckEENum()' set as the BeforeUpdate property on an
'Employee Add' type of form. As far as I can tell, the function runs
as
advertised, alerting the data entry team member that the Employee
Number
entered is already in use. However, on the first atempt to enter a
non-unique value, the user also gets the following errer:

'The value in the field or record violates the validation rule for the
record or field.'

This error is obviously not needed, and confuses the data entry person,
does
anyone know how to supress it? I've tried SetWarnings = false, and do
not
use the Msgbox Err.Description function at all. I'm sure it's
something
simple I've never ran into.

Thanks!


Function CheckEENum()
On Error GoTo ErrHand
DoCmd.SetWarnings False
Dim frm As String
Dim var As Variant
Dim frmB As Form
frm = Screen.ActiveForm.Name
frm = "Forms!" & frm
var = (DLookup("EmployeeNumber", "tblEmployees", "IDEmployee = " &
frm
&
"![IDEmployee]"))
Set frmB = Screen.ActiveForm
If frmB!EmployeeNumber = var Then
Exit Function
Else
If IsNull(DLookup("LName", "tblEmployees", "[EmployeeNumber] = "
&
frm & "!EmployeeNumber")) = False Then
MsgBox "The Number Entered Is Currently In Use, Please Enter
A
Unique Number"
DoCmd.CancelEvent
End If
End If
Exit Function

ErrHand:
ErrMsg ("eM-404")
End Function
Click to expand...
Click to expand...
Click to expand...
 
I guess that will work for now, I hope MS comes up with a solution for the
next office database program!
 
That is right, how it should work. If you have the unique index on the
table, it will show the error message when the form tries to save the data.
However the code on the after update to stop the duplicate should be in the
before update event, not the after update event.
The before update event has a Cancel argument, which you can use to stop a
duplicate being saved in that field.
It is much safer to put the validation of the EEnum in the before update
event for the form than the before update event for the control.
You can use the after update event of the control to warn the user that they
have entered a duplicate value, without forcing them to change it right
away.
When they try to save the record, the form before update ensures that they
can't save the record with a duplicate in EEnum.
You should keep the unique index on the table, and we will have another go
at trapping the automatic Jet error message so it doesn't show.
Put this code in the Form's error event

Select Case DataErr
Case 2116, 3316, 3317
Response = acDataErrContinue
'Field validation, Table validation, Custom Validation
Case Else
Response = acDataErrDisplay
End Select

Jeanette Cunningham

Doc said:
Thanks for the response!

I removed the input mask, no changed noted. Tried the Err.Number
suggestion, nothing ever came up. It doesn't seem to be triggering an
error
on the form?? I took the AfterUpdate code off of the field, and entered a
duplicate number, and the same error was produced, only right before
adding
the record instead of before updating the field. I removed the index, and
reapplied the code on the AfterUpdate on the field, and it seems to work
correctly. However, this approach kind of scares me, but it works for
now. .
.

Any other ideas?

Jeanette Cunningham said:
Doc,
Keep the index as unique, no duplicates.
Try removing the input mask to see if that is causing a problem.
You can trap the error using the form's OnError event.

This code will show you the error number when you run the form.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
msgbox Err.Number & " " & Err.Description
End Sub

When you know the error number you can put code like this:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If Err.Number = 0000 Then
'handle error here
End If
End Sub

Replace 0000 with the error number from above.

Jeanette Cunningham

Doc said:
Thanks for your response!

That's part of the reason I'm so confused by the secondary error.
There
is
not validation rule for the EENum field in the table, or on the form.

There is an input mask "P00"999999;0;_ and is set to index with no
duplicates. Should I set the index to duplicates allowed and rely on
the
CheckEENum function?

:

Cameron,
It would be best to have a look at the table that the EENum is stored
in.
Make sure you understand what the validation rule is for the field in
question. It sounds as if it is more than just demanding unique
values.
If
after you read the validation rule, you decide that it is not needed,
you
can remove it. If however there is a good reason for the validation
rule
to
exist, then write a function to check if newly entered EENums comply -
this
would be similar to the CheckEENum function but it would check the
validation rule and if the number doesn't pass, it would pop up a
message
box to tell the user about the problem.

Jeanette Cunningham

"Cameron Dockstader" <[email protected]>
wrote
in
message I have a funtion 'CheckEENum()' set as the BeforeUpdate property on
an
'Employee Add' type of form. As far as I can tell, the function
runs
as
advertised, alerting the data entry team member that the Employee
Number
entered is already in use. However, on the first atempt to enter a
non-unique value, the user also gets the following errer:

'The value in the field or record violates the validation rule for
the
record or field.'

This error is obviously not needed, and confuses the data entry
person,
does
anyone know how to supress it? I've tried SetWarnings = false, and
do
not
use the Msgbox Err.Description function at all. I'm sure it's
something
simple I've never ran into.

Thanks!


Function CheckEENum()
On Error GoTo ErrHand
DoCmd.SetWarnings False
Dim frm As String
Dim var As Variant
Dim frmB As Form
frm = Screen.ActiveForm.Name
frm = "Forms!" & frm
var = (DLookup("EmployeeNumber", "tblEmployees", "IDEmployee = "
&
frm
&
"![IDEmployee]"))
Set frmB = Screen.ActiveForm
If frmB!EmployeeNumber = var Then
Exit Function
Else
If IsNull(DLookup("LName", "tblEmployees", "[EmployeeNumber]
= "
&
frm & "!EmployeeNumber")) = False Then
MsgBox "The Number Entered Is Currently In Use, Please
Enter
A
Unique Number"
DoCmd.CancelEvent
End If
End If
Exit Function

ErrHand:
ErrMsg ("eM-404")
End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top