Custom error for the duplicate value error 1

[ImStuk]

How can I over ride the "Duplicate value in the primary key or index etc..." error, and add a custom error explaining in detail.

I have a form that has a date that can never be duplicated. I want to tell users the following: "There is already an Activity Sheet for this date, please search by date to locate..." Something like that.

 

[PHV]

Take a look at either the DLookUp function or the Error event procedure of the Form object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[randy700]

Try to make an entry that causes the error to appear. When it does, note the error number displayed in the message box. With that done, include something like the following in your code....

Private Sub ButtonName_Click()
On Error GoTo OOPS
Place your code here.
BackFromError:
Code to run after error goes here.
Exit Sub
OOPS:
This is the error handling code
If err.number = # you found earlier Then
MsgBox "There is all ready an activiy sheet..."
Else
MsgBox "Please report error to... " & err.num & " " & err.description
End If
Resume BackFromError
End Sub


Randy

 

[ImStuk]

It is saying the error number is "0". I can't imagine that is right. Iput some for error number 0, but I get the custom error no matter what date I put in there.

 

[PHV]

In the BeforeUpdate event procedure of your date control:
If Not IsNull(DLookUp("[Activity Sheet]", "[your table]", "[data field]=#" & Format([date control], "yyy-mm-dd") & "#")) Then
MsgBox "There is already an Activity Sheet for this date, please search by date to locate..."
Cancel = True
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ImStuk]

Is this DLookUp searching for duplicates? My table is "tblActivitySheet", the field in the table is "Date", and the text box for the date on the form is "txtDate".

I found the error. It is error number 3022, but I can't seem to trap it anywhere. I've tried Before_Update on the form as well as the field, but the system error seems to trigger before anything I do to trap it.

 

[PHV]

Have you tried to play with the Error event procedure of the form as I suggested you ?
You really don't see how to implement the DLookUp way ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[randy700]

You should also consider changing the field name. "Date" is a reserved word in Access and can create other troubles down the road.


Randy

 

[ImStuk]

I'm not that familiar with DLookUp, and thanks for the suggestion on the date Randy.

 

[randy700]

So, substituting the table/field names you provided in the code PHV suggested, yields.....

If Not IsNull(DLookUp("[Date]", "[tblActivitySheet]", "[Date]=#" & Format([txtDate], "yyy-mm-dd") & "#")) Then
MsgBox "There is already an Activity Sheet for this date, please search by date to locate..."
Cancel = True
End If

What does it do?
The DLookUp determines if the data in txtDate all ready exists in the table. If it does not, null is returned and the if statement is evaluated as false, allowing you to continue with the data you entered. If the data is found in the table, null is NOT returned, the if statement is true, the message box informs the user, and you do not continue with the entered data.

Hope that explains the procedure for you.....


Randy

 

[PHV]

Sorry for the typo: "yyy[highlight]y[/highlight]-mm-dd"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244