Access error message for duplicate index values

I have a field in an access table which is indexed with no duplicates
I want to raise and error message in this aspvb page
at the page top I placed
<%On Error Resume Next %>
at the bottom of the page I placed
<%' to catch the error:
    '// Code to execute query
    If Err.number <> 0 Then
        If InStr(Err.Description, "changes you requested to the table were not successful because they would create duplicate values") >0 Then
            Response.Write "<strong>You are trying to insert a duplicate Fund Name into the DataBase. Please select another assignment from the drop down menu.</strong>"
            Response.Write "An error occurred. (Here you don't know what error has occured but it is NOT the PK violation error. So show the actual error) " & Err.Description
        End If
    End If
    On Error Goto 0

When a duplicate is added the message does not appear and the record is not inserted.
Can anyone see where I am going wrong please
Thanks John
Who is Participating?
johnhardyConnect With a Mentor Author Commented:
Thanks for your answer and your previous help.
The long time interval was due to a hard disc failure. Although I have a mirror disc as a first line back up, when it came to the restore it was not so simple!
I have now found what I think (for me  as a Dreamweaver user)  a very simple answer.
In Dreamweaver there is an extension called Check new user name.
I have removed  the no duplicates propoerty from the access field and use the DW extension Check new user name.
I had not thought to use this extension in this manner but it appears to work fine.
As I have used my own solution I will ask a moderator what I should do about awarding the points. I Hope that is OK

The code this extension produces is as follows
' *** Redirect if username exists
MM_flag = "MM_insert"
If (CStr(Request(MM_flag)) <> "") Then
  Dim MM_rsKey
  Dim MM_rsKey_cmd
  MM_dupKeyRedirect = "FundAdd_Duplicate.asp"
  MM_dupKeyUsernameValue = CStr(Request.Form("FundName"))
  Set MM_rsKey_cmd = Server.CreateObject ("ADODB.Command")
  MM_rsKey_cmd.ActiveConnection = MM_PrestConn_STRING
  MM_rsKey_cmd.CommandText = "SELECT FundName FROM Funds WHERE FundName = ?"
  MM_rsKey_cmd.Prepared = true
  MM_rsKey_cmd.Parameters.Append MM_rsKey_cmd.CreateParameter("param1", 200, 1, 100, MM_dupKeyUsernameValue) ' adVarChar
  Set MM_rsKey = MM_rsKey_cmd.Execute
  If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
    ' the username was found - can not add the requested username
    MM_qsChar = "?"
    If (InStr(1, MM_dupKeyRedirect, "?") >= 1) Then MM_qsChar = "&"
    MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
  End If
End If
Rey Obrero (Capricorn1)Commented:
try using the error number instead of the message

if err.number=?? then
I may be wrong here, but it seems I read a long time ago that the err.number can be reset to 0 by running a valid statement after an error has occured.

Have you tried adding something like the following to several places in your script? Especially where you suspect the errors will occur?

If err.number > 0 Then Response.Write "Line 123: " & err.number & " | " & err.description & "<br>"
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

johnhardyAuthor Commented:

If I insert a duplicate value into  the table no error number shows, just more of the above text

I am using Access 2003
Rey Obrero (Capricorn1)Commented:
if you want an error to show when duplicate value is inserted to the table
you have to set the unique id or primary key to No duplicates
johnhardyAuthor Commented:
Thanks capricorn1:

yes I did that pls see top line
If you comment out 'On Error Resume Next, does it bomb out?
If you receive a generic 500 error page, make sure you turn off friendly error messages:
johnhardyAuthor Commented:
If I comment out 'On Error Resume Next, then I get
Microsoft JET Database Engine (0x80004005)
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.
/prestigeadmin/FundAdd.asp, line 39
Line 39 is     MM_editCmd.Execute
Ok, let's find out what SQL statement is being passed in. Just prior to line 39, it should be assigning the SQL statement. Just after that line, put:

Response.Write <the sql statement variable>

Refresh the page. Is it trying to assign the ID instead of allowing the database to do it? Can you copy and paste that SQL statement directly into the database? If so, does it bomb there too?
Rey Obrero (Capricorn1)Commented:
try creating error handling routine

On error goto MyErrorHandler

     If Err.number <> 0 Then .....

     resume next

     end if
Can't do that in classic ASP capricorn
johnhardyAuthor Commented:
Line 39 now reads MM_editCmd.Execute
Response.Write MM_editCmd.Execute
When that is run the same page appears and is empty
johnhardyAuthor Commented:
I think I will have to find another method for this.
Any further ideas please
It's been a while....

Let's see.....

Instead of
Response.Write MM_editCmd.Execute

I wanted, instead, to output the actual SQL statement being executed. Have you found where that's being assigned?
If so, response.write it out, then run it within the database to see if the desired error occurs.
No worries.
I would request a PAQ and refund since you found your own solution. Up on the original question, there is now a 'Delete Question' button. Click on that and follow the directions.

Happy New Year!
Closed, 500 points refunded.
Friendly Neighbourhood Community Support Moderator
All Courses

From novice to tech pro — start learning today.