error handling duplicate entry

[tizwaz]

I have a form and one of the fields in indexed (no duplicates allowed). If the user closes the form or moves to another record having entered a duplicate record they receive the standard access msg about not being able to save the record. This isn't very user friendly and I was wondering if it was possible to code it so that if this happened a msg would appear telling them specifically what was duplicated and asking them whether they wanted to change the data or close the form without saving it. If so what event would I need to put the code on?

 

[JimHorton]

Use the Form's Error Event. The DataError which is passed to that sub is the one you look at, I forgot the exact # offhand. You can then pop up a modal form or msgbox and upon the repsonse, set the Response argument to one of the constants, acDataErrContinue, etc. (see help for all the constants)
--Jim

 

[tizwaz]

how would I find out what error number this error is generating. In a run-time error it quotes the number so that you can say If Err.number = 3056 then msgbox etc but all this msg says is the data violates the index or primary field etc and record can't be saved

 

[JimHorton]

Put a debug.print in the form's Error event, ie:

Debug.print DataErr

Then in that same event, use a case stmt (or just an IF if that's the only error you care to deal with), and set the Response value accordingly
--Jim

 

[tizwaz]

Jim
Thanks for your help - I've tracked down the error number to 3022 although it gives 2169 after that when it says it cant save the record.
I've tried to code as you suggest but I'm new to this and can't get it to work. Here is my code - do you know what's wrong with it?

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim intResponse As Integer
Select Case DataErr
Case 3022
intResponse = MsgBox("This record contains a duplicate pay reference" & _
" do you wish to amend it?", vbYesNo, "Duplicate Record&quot
If intResponse = vbYes Then
Response = acDataErrContinue
Pay_number.SetFocus
ElseIf intResponse = vbNo Then
Response = acDataErrContinue
MsgBox ("This record will not be saved as it is a duplicate!&quot
Save = False
DoCmd.Close
End If
Case Else
Response = acDataErrContinue
MsgBox ("An error has occurred, Error Number " & Err.Number & Err.Description)
End Select
End Sub

 

[JimHorton]

tiz,
Can you describe what's happening with the code, ie why it doesn't work? I cut & pasted it and tried it, and it works *except* for the "Save = False" line. What is 'Save'? Is that a public variable? I commented that line out and all was fine.
--Jim

 

[tizwaz]

Jim
If I enter a record with a duplicate pay number and then try and close the record either with the X or with a close command button I get the msg asking to I want to amend the pay ref. If I say yes the focus doesn't move to the pay ref field and I get the msg box which says An error has occurred Error Number 1.
If I say no to the first msg box I get run time error 2501 the close action was cancelled and if I click debug it goes to the docmd.Close line in the code. Then I get the msg box which says an error has occurred Error Number 1 again.
This happens whether I comment out the save bit or not.
The idea of the save = false was an attempt to close the form without saving the record.

 

[JimHorton]

tiz,
Use the .Undo method then, ie:
Me.Undo

For the setfocus, that is a troublesome method, certain things will prevent that from happening, I can't recall the exact circumstances offhand, but I generally trap and resume next on setfocus errors. Something to look at, and this goes as a general rule for ALL controls on any form--MS Access' form wizard breaks many generally accepted rules and naming conventions. I always prefix textbox controls with "txt", comboboxes with "cbo", etc.

Whatever your preferred prefix or naming conventions--a *control* is very different from the *control source*, and the two should never be named the same. You'll see why when you try to trim the control source by, say, setting the controlsource of "custname" too "=trim([custname])"--you'll get an error every time because it's trying to trim itself now, instead of the control source, which started out as "custname" also.

Even though Access usually makes the right decision as to which object you're referring to (ie, a control Source does not have a .SetFocus method, but the control itself does), it's just good practice to name them different. But I digress...
--Jim