Change duplicate values Error Msg in the index, primary key, or relationship

Khalid_Afridi

Registered User.
Local time
Today, 18:55
Joined
Jan 25, 2009
Messages
491
Hi folk,

Does anybody knows how to control the the below msg to something like this through VBA:
"The Item: " & ItemNo & " already exists in the table."
instead of this:
Msg:
"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."

What is the Err.Number for this error?

Thanks!
 
Intercept it in the control's/Form's BeforeUpdate event on a form and cancel the save.

You can use a Dlookup on the table to check it for duplicates.

JR
 
Thanks Janr,

I have a table tblQty with the combination of Primery key for SOFID and ItemID field.
The reason is SOFID and ItemID should be unique and no Item could be repeated for the SOFID:

SOFID|ItemID
1|1
1|2
1|3
1|4

This works fine with the above combination but, if user select duplicate ItemID the access gives its default Err.Msg which looks odd, I want to trap that error msg with something I discribed im my 1st post.
The look up with single criteria will not solve the problem:
 
Dlookup and Dcount can use multiple criterias. You can do the test in the Form_BeforeUpdate.

ex:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tbltest", "CustFK=" & Me.CustFK & " AND ProductFK =" & Me.ProductFK) > 0 Then
    MsgBox "CustomerID " & Me.CustFK & " And PruductID " & Me.ProductFK & " already exist!!", vbCritical, "Duplicate entry"
    Cancel = True
    'Me.Undo
End If
End Sub

If you uncomment the Me.Undo the hole record will be disgarded. To check the individual controls to warn users before they fill out the rest of the form is a bit trickier, you have to test that both controls have entries in them before you do the check.

JR
 
Thanks dude

I was thinking to use the currentdb.OpenRecordset method on before update event of the control coz Dlookup will check only the first occurness of the Result,

I create the following FindDup function:

PHP:
On Error GoTo Err_Handler
    Call FindDup(Me.SOFID, Me.ItemId)
Exit_Err:
Exit Sub
Err_Handler:
MsgBox Err.Description & " Error No." & Err.Number
Resume Exit_Err

Function:
PHP:
Public Function FindDup(lngSOF As Long, lngItem As Long)
    If DLookup("[ItemID]", "tblQty", _
             "[SOFID] = " & lngSOF & " And [ItemID] = " & lngItem) > 0 Then
    MsgBox " The Item already exists in your list."
    Cancel = True
    End If
End Function

Thanks again!
 

Users who are viewing this thread

Back
Top Bottom