Pop up Message on Duplicates Error.

[Torinjr]

Hi there,

I have a form 'Item_types' that allows me to add new records to be saved in a table. The table does not allow duplicates, as intended and the form is working perfectly.



However, I am getting an error message when I try to save a duplicate using the form, which i would expect.

'The changes you requested to the table were not succesful 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.' Followed by a pop up box asking me to Stop All Macros.

My question is, what can i do to make this error pop up sequence more user friendly? In the macro builder/code builder, can I write something that will propduce only one message box, saying something like "You have entered a duplicate Item Type, please change."?

Thanks

[June7]

Options:

1. validate data in form BeforeUpdate event

2. error trapping - review http://allenbrowne.com/ser-23a.html

[Micron]

[In the macro builder/code builder,...] Based on June's response, she might be thinking that you are using code, but I think you are using a macro because you are prompted to stop all macros. Thus the information might seem strange. For such a long time I was under the impression that macros can't handle errors, so I never got to know them very well. Apparently they can, so you might also want to read https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx in case it suits your situation.

[Missinglinq]

...For such a long time I was under the impression that macros can't handle errors...

That's because they couldn't, until v2007! But even now, from what I hear, they don't do a really good job of it. Hard to tell, really, because very, very few experienced developers use Macros...they're simply not flexible enough!

And as June7 suggested...it's far better to check for duplicates before attempting to save a Record, than afterwards!

Linq ;0)>

[Torinjr]

Yes i was using a macro. I tried using the macro OnError on the link that micron provided. I tried following the example by calling on the macro ErrorHandler that i created but I get a message that, "MS Access can't find the macro 'ErrorHandler' in the macro group 'Item_UOM: Save Unit : Embedded Macro'." I have the ErrorHandler Macro saved under the same name (i doubled checked this), what might my problem be?

I have not tried Junes method yet because I already have my macros set up and i would need to convert them all using code. And im not a programmer/coder so I have lots of trouble.

Thanks and sorry for the delayed response.

[Micron]

Is it an embedded macro (because it is part of a group), or a macro object that you can see in the navigation pane? I believe it has to be the latter.

[Torinjr]

It is a macro i can see in the navigation pane. The macro looks like this

[Micron]

Sorry, I'm not up to date with A2013, but what you show looks like the macro itself, not an object in what I am familiar with as an object in the list of navigation pane objects. In case no one else comes to your rescue, all I can offer is that it's my understanding that there is a difference between an embedded macro and a macro object, and if you're trying to call an embedded macro, there is a specific syntax for the call. In any event, what you are trying to do with macros is easily done in code by trapping the duplicate record error number and just moving to the next record. Not sure that will help you though.

[Torinjr]

I have solved the problem two ways.
1) Using code and following the methods on http://allenbrowne.com/ser-23a.html

2) Using Macros. Because the OnError Macro was looking for an embedded function i had to add a submacro within the current macro. This sub macro would would then call on the created Macro in the navigation pane "ErrorHandler". The sub macro only executes if it is called on by the On Error being true. So in conclusion the OnError Macro requires a sub macro to call on other macroes in the navigation pane.

I'm sick of using the word macro now but hopefully this will help those who can't code/have all their macroes already set up like i did...



Thanks to those who helped.

[Micron]

As June said, most people here who are beyond the very basics don't use macros. Since I seem to be the only one guessing lately (Hey, no slight intended to anyone else) I'll take another stab. If I grasp what you wrote, I was right that the macro was embedded and not an object in the nav pane. OK, now what?
You must be generating an error, but not the one you are trying to trap. Your error macro doesn't seem to have anything to do with your original post of 'duplicate record'. Instead, it's checking for a null in a control.
Rename your error macro to save it and create a new one with the original name. Set it up to give you a message box to display the error number and message and run your procedure again. In code, I would write Msgbox "Error " & err.number & ": " & err.description. Don't know how you'd use that in a macro but should be easy.

[Torinjr]

Thanks Micron, I edited my previous post as I had figured out what i was trying to do using a sub macro. Have a look ^^^ And I agree this is not specific for duplicate record errors and will run on any error, but in my case since this is the only error i am getting it works.

[Micron]

Glad you got it solved!