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
| 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
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 16th, 2003, 09:21 PM
Registered User
Join Date: Sep 2003
Location: , , Singapore.
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
Reply With Quote
  #2 (permalink)  
Old September 16th, 2003, 10:47 PM
Friend of Wrox
Join Date: Jun 2003
Location: , , USA.
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.


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
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 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

All times are GMT -4. The time now is 01:38 PM.

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