Hiding error dialog box in Access

[Guest]

How can I hide the Action failed box that appears when a user enters a
duplicate record. I have a message box set to appear on error and I tried to
use the Stop Macro action as well as the SetWarnings action but it still
seems to show up.

I want the user to see the custom message box I created but have it cancel
the new record on error. Appreciate any help.

 

[tina]

not quite sure what you mean. is a "duplicate" record violating a unique key
set at the table level? and do you have a macro set to run on the form's
Error event? if so, you can't do this with a macro - unless you're using
A2007, then it might be possible, but i don't know. otherwise, you need to
use VBA code in the form's Error event procedure. something along the lines
of

Response = acDataErrContinue
Msgbox "This record already exists in the database."
Me.Undo

hth

 

[Guest]

Thanks tina.. I will give it a try.

not quite sure what you mean. is a "duplicate" record violating a unique key
set at the table level? and do you have a macro set to run on the form's
Error event? if so, you can't do this with a macro - unless you're using
A2007, then it might be possible, but i don't know. otherwise, you need to
use VBA code in the form's Error event procedure. something along the lines
of

Response = acDataErrContinue
Msgbox "This record already exists in the database."
Me.Undo

hth

 

[Guest]

Tina...

I tried the code you gave me and it worked. Can you take me one step
further. I have a Save Record button on the form window. when Ii click that
button it will save the record (I know I don't have to do that but my other
users don't know that) unless there is a duplicate and then your code "kicks
in". It still displays the "Halt Macro" dialog box after the code. Is there
code I can add to this error code that will cancel the save action without
displaying the Halt macro box but still allow the user to have that button to
save a new and unique record. Also... where do I assign it to... the OnError
event of the form or the save record button I placed at the bottom of the
form? Thanks for your help.

 

[tina]

well, i can't think of a way to handle the macro error (again, unless you're
using A2007; i've heard macros have been enhanced to include error handling
in the new version). instead of using a macro to save the record, try adding
the following code to the command button's Click event procedure, as

On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord

hth

 

[Guest]

Tina...

Thanks.. I will try it.

well, i can't think of a way to handle the macro error (again, unless you're
using A2007; i've heard macros have been enhanced to include error handling
in the new version). instead of using a macro to save the record, try adding
the following code to the command button's Click event procedure, as

On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord

hth

 

[Guest]

Tina...

just wanted to let you know I got my problem solved with this code attached
to the Form's save button.

Private Sub cmdSave_Click()

On Error GoTo Err_cmdSave_Click

DoCmd.RunCommand acCmdSaveRecord

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox "This record already exists in the database. You may have
previously assigned this document to this same position. Please re-enter
this record."
Me.Undo
Resume Exit_cmdSave_Click

End Sub

Thanks for your help. Couldn't have done it without you.

 

[tina]

very good! and you're welcome

Tina...

just wanted to let you know I got my problem solved with this code attached
to the Form's save button.

Private Sub cmdSave_Click()

On Error GoTo Err_cmdSave_Click

DoCmd.RunCommand acCmdSaveRecord

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox "This record already exists in the database. You may have
previously assigned this document to this same position. Please re-enter
this record."
Me.Undo
Resume Exit_cmdSave_Click

End Sub

Thanks for your help. Couldn't have done it without you.