On Error Goto....

[Guest]

Hi,

Can u pls.anybody solve this problem....?

i have set primary key for one fields, which indicates duplicate message
when i enter duplicate value. The message access display is too lengthy like
as below.

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

I wanted to display as my desire message, for which i used the following
code which is not working...

Private Sub Text18_AfterUpdate()

On Error GoTo Err_Text18_Click
Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_Text18_Click:
Exit Sub

Err_Text18_Click:
If InStr(UCase(Err.Description), "DUPLICATE") > 0 Then
MsgBox "Eh..!..Its Duplicate value.."
text18.text = ""
Resume Next
End If

End Sub

 

[Guest]

Hi.

Your code doesn't work because it doesn't execute. Your form checks for
duplicate values before actually updating the field, therefore Text18 never
updates and Text18_AfterUpdate() never executes. If you want to catch and
fix the problem, use the Form_BeforeUpdate() event instead like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As Recordset
Set rs = RecordsetClone
rs.FindFirst "ID=" & CStr(ID)
If Not rs.NoMatch Then MsgBox ("Duplicate Value!")
'Or instead of the msgbox, you could fix the problem like this
ID = DMax("ID", "Table1") + 1
End Sub
Replace ID with your specific primary key and replace Table1 with your
applicable table.

I'm not sure how your database works, but I think a better fix would be to
change your Primary Key field to an Autonumber type. This would eliminate
the cumbersome code above as well as annoying errors you described.

-Cliff