Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 18th, 2006, 01:53 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Handling the no duplicates index error

I know how to handle errors and provide custom error messages for errors generated within a sub or function. However, if the error is thrown by a no duplicates index, I don't know where or how to catch the error in order to provide a more user friendly error message.

Thanks in advance for your help

Enoch
__________________
Enoch
Reply With Quote
  #2 (permalink)  
Old January 18th, 2006, 02:42 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Don't know exactly how you're doing the insert, but the Jet error you're interested in is 3022.

Code:
Sub DuplicateValueError(strNewValue As String)

   On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblRecords", dbOpenDynaset)

   With rst
      .AddNew
      !Field1 = strNewValue
      .Update  '<--- Error 3022 thrown here.
   End With

   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Exit_Here:
   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Err_Handler:

   If Err.Number = 3022 Then
      strMsg = "Error occurred when you called rst.Update." & vbCrLf & vbCrLf
      strMsg = strMsg & "Please enter a unique value for this field."

      MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Value Error"
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   End If

   Resume Exit_Here

End Sub
HTH,

Bob

Reply With Quote
  #3 (permalink)  
Old January 18th, 2006, 03:02 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

On a stylistic note, if I know the error code I'm trapping for, I like to use a constant just to make the code more self-documenting:

Private Const ERR_DUPLICATE_VALUE = 3022

Sub DuplicateValueError(strNewValue As String)

   On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblRecords", dbOpenDynaset)

   With rst
      .AddNew
      !Field1 = strNewValue
      .Update '<--- Error 3022 thrown here.
   End With

   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Exit_Here:
   rst.Close
   db.Close
   Set rst = Nothing
   Set db = Nothing
   Exit Sub

Err_Handler:

   If Err.Number = ERR_DUPLICATE_VALUE Then
      strMsg = "Error occurred when you called rst.Update." & vbCrLf & vbCrLf
      strMsg = strMsg & "Please enter a unique value for this field."

      MsgBox "Error Number: " & Err.Number & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Value Error"
   Else
      MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   End If

   Resume Exit_Here

End Sub

Reply With Quote
  #4 (permalink)  
Old January 18th, 2006, 03:12 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Then again it occurs to me that maybe your talking about trapping the error at the Form level, in which case you would use the Form's error event:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

   Select Case DataErr
      Case 3022
           MsgBox "You added a record which duplicates an existing value."
           Response = acDataErrContinue
      Case Else
           Response = acDataErrDisplay
   End Select

End Sub

Bob

Reply With Quote
  #5 (permalink)  
Old January 18th, 2006, 03:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Then set focus back to the offending control:

Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Const ERR_DUPLICATE_INDEX_VALUE = 3022
    Dim strMsg As String

    If DataErr = ERR_DUPLICATE_INDEX_VALUE Then
        strMsg = "This record cannot be added to the database." & vbCrLf
        strMsg = strMsg & "It would create a duplicate record." & vbCrLf & vbCrLf
        strMsg = strMsg & "Changes were uncuccessful."

        MsgBox "Error Number: " & DataErr & vbCrLf & vbCrLf & strMsg, _
            vbCritical + vbOKOnly, "Duplicate Record."

        'Set focus to the offending control
        Me.txtText1.SetFocus

        Response = acDataErrContinue 'prevent Access from displaying its own error message
    Else
        Response = acDataErrDisplay 'cause Access to display its own error message
    End If

End Sub
Bob

Reply With Quote
  #6 (permalink)  
Old January 18th, 2006, 03:41 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks, that's just what I needed.


Enoch
Reply With Quote
  #7 (permalink)  
Old January 18th, 2006, 03:57 PM
Authorized User
 
Join Date: Dec 2005
Location: , , .
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It was within a form, sorry I wasn't more specific, Thanks for writing out both answers though.
I didn't realize there was a built in event to just handle errors in forms.


Enoch
Reply With Quote
  #8 (permalink)  
Old January 18th, 2006, 05:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

No problem, Enoch. You did mention you already knew procedure error handling, so I figured you must have meant the form level stuff. Glad you found what you needed.

Best,

Bob

Reply With Quote
  #9 (permalink)  
Old March 22nd, 2006, 09:14 PM
Registered User
 
Join Date: Mar 2006
Location: , , .
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Could someone please explain why this error (3022) is being generated at all. I am getting the same error but see no reason for it. Isn't .addnew supposed to create a new, unique record with a new, unique primary key (indexed, no duplicates, autonumber)? If so, then where is the error coming from?

I have been battling this for days and making no neadway. I'm afraid I may have a corrupt table or something. There are over 1000 records in the table (more or less in sequential numeric order) but the new PK ID is in the 500's.

Any help greatly appreciated.

Penn
Reply With Quote
  #10 (permalink)  
Old March 22nd, 2006, 10:28 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Quote:
quote:
Isn't .addnew supposed to create a new, unique record with a new, unique primary key (indexed, no duplicates, autonumber)?
Yes it is. If you have two fields in a table named ID(autonumber) and Field1 and run:

 rst.AddNew
 rst!Field1 = "NewData"
 rst.Update

The ID field will be automatically incremented with a new unique ID. You can retrive the new ID by immediately running (after the update):

Dim rst As ADODB.Recordset
Dim intNewAutoNumber As Integer
Set rst = New ADODB.Recordset
rst.Open "SELECT @@IDENTITY", CurrentProject.Connection
intNewAutoNumber = rst.Fields(0)

Sounds to me like your table is toast. Can you import the records you have into a new table:

Code:
INSERT INTO Table2 ( ID, Field1 )
SELECT Table1.ID, Table1.Field1
FROM Table1;
That sort of thing...

HTH,

Bob

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
error handling Abhinav_jain_mca General .NET 5 December 21st, 2005 12:12 AM
Error handling ppenn Access VBA 3 September 2nd, 2005 06:58 AM
Error handling Hudson40 Access VBA 2 February 11th, 2005 04:32 AM
error handling Abhinav_jain_mca SQL Server 2000 1 August 24th, 2004 06:13 AM
Avoid duplicates error screen Varg_88 Access ASP 2 August 3rd, 2004 08:58 AM



All times are GMT -4. The time now is 11:46 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.