Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 16th, 2003, 09:21 PM
Registered User
Join Date: Sep 2003
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via ICQ to elleetan Send a message via MSN to elleetan Send a message via Yahoo to elleetan
Default how to create duplicate error message???

i have 3 tables which name "TerminalFault" and "terminalFAultDEtail" and "TerminalFaultType"

for "TerminalFault" table, it is alos the main form call(FrmFAult) , it has 3 fields which are:
1)Fault date, datatype = date/time , indexed = no
2)Serial NUmber, datatype = text , indexed = yes(duplicate ok)
3)FaultNUmber(primary key), datatype = text , indexed = yes(duplicate no)

for "terminalFaultDetail" table, it is also the subform attahed on the mainform call(subform1), it has the fields
1) LinkId(primary key), datatype= aoutonumber, indexed = yes(no duplicate)
2)FaultNumber (which is link to the TerminalFault table's field FaultNUmber), datatype = text , indexed = no
3) FaultLink ,datatype = Number, indexed = yes(duplicate ok),
Which is link to other table(TerminalFaultType)'s fields call "Fault".

for "TerminalFaultType" table, it has 2 fields:
1)LinkID, datatype= autonumber, indexed = yes(no duplicate), which is link to "terminalFaultDetail"'s fields(FaultLink)
2)Faultdesc, datatype=text , indexed = no

from these table , i had created one main from and a subform from above tables except the TerminalFaultType

i think it is more clearer with this explaination:

The realtionship between the FaultNumber field in TerminalFAult to TerminalFAultDEtial is one to many.
here is the form that i had created:
Main forn(FrmFAult) has:
2) Serial NUmber

1)FaultNUmber which is link to the mainform "FaultNumber" many to one
2)Fault(which is choose from the other table call TErminalfaultDetail)

this is my form looks like:
MAin form:
FaultDate : 1/1/2003
Serial NUMber : 22
FaultNUmber: A001

""""can have multiple faultnumber and Fault:
FaultNUmber : A001 Fault: F1
FaultNUmber: A001 FAult: F2
FAultNUmber: A001 FAult: F3

eg2, FaultDate : 1/1/2003
Serial NUMber : 22
FaultNUmber: A002

""""can have multiple faultnumber and Fault:
FaultNUmber : A002 Fault: F1
FaultNUmber: A002 FAult: F2
FAultNUmber: A002 FAult: F3

what i want is with the on the subform same FaultNumber, it cannot have same FAult:
FaultDate : 1/1/2003
Serial NUMber : 22
FaultNUmber: A003

""""can have multiple faultnumber and Fault:
FaultNUmber : A003 Fault: F1
FaultNUmber: A003 FAult: F2
FAultNUmber: A003 FAult: F1 (i want to create duplicate value error to inform user that there shouldnt be duplicate cos it is duplicate the first Fault which is the first row" faultNUmber = A003 and Fault = 1"

Hope i wiould not confused you. I will appreciate your help! tHank you
Old September 16th, 2003, 10:47 PM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts

Hi elleetan,

Here's some info I posted a while back on how to use a form's (or subform's) Error event to trap Jet's duplicate record error message. See if you can get it going in your situation.

Build a multi-field, unique index on the table that includes the fields (as many as you like) which define a duplicate record in your subform's record source. This is done through the Indexes dialog in table design view. Just give the index a name, assign your "duplicate-record-defining" fields to it, and set its 'Unique' property to yes.

Second, add this code behind your subform:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const errDuplicateIndexValue = 3022
    Dim strMsg As String

    If DataErr = errDuplicateIndexValue 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."


        Response = acDataErrContinue
        Response = acDataErrDisplay
    End If

End Sub

This will display a more user friendly error message than the Jet default error string which reads:

"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."

Hope this is in the vacinity of what you're looking for.


Similar Threads
Thread Thread Starter Forum Replies Last Post
error message using ssl : "VS .NET cannot create.. connect2sandep ASP.NET 1.0 and 1.1 Professional 0 October 20th, 2005 11:37 AM
Back button create warning message Mantis HTML Code Clinic 0 May 19th, 2005 07:00 AM
Duplicate Error Message Stopping it ru1 Access 2 February 25th, 2005 04:53 PM
How to I create non-modal message boxes? Ciarano VB How-To 0 March 12th, 2004 12:01 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.