Duplicate error message help

goju

Registered User.
Local time
Today, 11:46
Joined
Apr 7, 2005
Messages
77
hi,

the below scrip was taken from another forum member.

i have put this in the Forms / error VBA

but i still a little lost

i have set 3 fields to be indexed not to allow duplicate information (Forename, Surename, DOB)

i was then advised to error trap so it would highlight that i have enterd a duplicate record on typing in the DOB (As that as third feild to be entered)

so i then used the below script, however this doesnt highlight that it is a duplicate it just doesnt let me go on to the next record, the only time the error message is highlighted is when i go into design mode.

anyideas on where to go from here.


Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3022 Then
Response = acDataErrContinue
MsgBox "Tis MerID alredy exist!"
Exit Sub
End If
If DataErr = 2169 Then
Response = acDataErrContinue
MsgBox "Tis record will not be saved!"
Exit Sub
End If

End Sub
 
I suggest that you use the forms BeforeUpdate event to trap the error and cancel the event if the error is tripped [record already exists]. Search around for there are tons of threads related to what you want to do.
 
ok did what you suggested, moved it to before update

cant cancel it it doesnt have a cancel button just an ok button,

i have searched the forum for the last 2 hours, but still lost.
 
goju said:
ok did what you suggested, moved it to before update

cant cancel it it doesnt have a cancel button just an ok button,

i have searched the forum for the last 2 hours, but still lost.
It has been less than one hour since I posted so I doubt your two hour search based on the info I provided. :p

There are a few ways to do. Here is one using the forms BeforeUpdate event where I am trapping for the 3022 error number...

Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
    
    'any code here if you want to provide some type if Null checking, like this
    If IsNull(Field1) Or Me.Field1 = "" Then
        MsgBox "Field 1 can not be empty.", vbExclamation, "Invalid Null"
        DoCmd.CancelEvent
        Exit Sub
    End If
    
Exit_Form_BeforeUpdate:
    Exit Sub
    
Err_Form_BeforeUpdate:
    ElseIf Err.Number = 3022 Then 'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship.
        MsgBox "You can not save this record because that record already exists.", vbCritical, "Duplicate Record"
        Exit Sub
    Else
        MsgBox Err.Number & " - " & Err.Description
        Resume Exit_Form_BeforeUpdate
    End If
    
End Sub
 

Users who are viewing this thread

Back
Top Bottom