VBA Error trap required.

[Waddy]

Hello all again

I have some vba code below and I hope you can help, I want to add an error trap so if there is an error it deletes the current record and closes the window(Form) along with a messagebox.

Below is my code:

Private Sub Empselectsicklog_BeforeUpdate(Cancel As Integer)

Me.txtOracle.Value = Me.Empselectsicklog.Column(2)
Me.txtEmployeeName.Value = Me.Empselectsicklog.Column(1)
Me.txtAccount.Value = Me.Empselectsicklog.Column(3)
Me.txtFTPT.Value = Me.Empselectsicklog.Column(4)
Me.txtHours.Value = Me.Empselectsicklog.Column(5)
Me.txtManager.Value = Me.Empselectsicklog.Column(6)
Me.txtRegion.Value = Me.Empselectsicklog.Column(7)

End Sub

Thank you

 

[Waddy]

At a stab myself would it be like below.


Private Sub Empselectsicklog_BeforeUpdate(Cancel As Integer)

On Error GoTo HandleError

Me.txtOracle.Value = Me.Empselectsicklog.Column(2)
Me.txtEmployeeName.Value = Me.Empselectsicklog.Column(1)
Me.txtAccount.Value = Me.Empselectsicklog.Column(3)
Me.txtFTPT.Value = Me.Empselectsicklog.Column(4)
Me.txtHours.Value = Me.Empselectsicklog.Column(5)
Me.txtManager.Value = Me.Empselectsicklog.Column(6)
Me.txtRegion.Value = Me.Empselectsicklog.Column(7)

HandleError:
MsgBox Err.Description
DoCmd.RunCommand acCmdDeleteRecord
Resume HandleExit

End Sub

 

[Deleted member 147267]

why not move code to form's before update and cancel and Undo?

 

[theDBguy]

Hi. What error were you expecting to happen in the BeforeUpdate event of Empselectsicklog. Is it a combobox? If you're trying to make sure the user selects something, then you can check it first before assigning the values from its columns. Besides, you might want to use the AfterUpdate event instead.

 

[The_Doc_Man]

Private Sub Empselectsicklog_BeforeUpdate(Cancel As Integer)

On Error GoTo HandleError

Me.txtOracle.Value = Me.Empselectsicklog.Column(2)
Me.txtEmployeeName.Value = Me.Empselectsicklog.Column(1)
Me.txtAccount.Value = Me.Empselectsicklog.Column(3)
Me.txtFTPT.Value = Me.Empselectsicklog.Column(4)
Me.txtHours.Value = Me.Empselectsicklog.Column(5)
Me.txtManager.Value = Me.Empselectsicklog.Column(6)
Me.txtRegion.Value = Me.Empselectsicklog.Column(7)
[COLOR="Red"]LeaveSub:
Exit Sub[/COLOR]

HandleError:
MsgBox Err.Description
DoCmd.RunCommand acCmdDeleteRecord
Resume [COLOR="red"]LeaveSub[/COLOR]

End Sub

NEVER let yourself "fall into" an error handler. That leads to a world of hurt because of the massive difference in contexts between Exit Sub and Resume.

However, the other suggestions are more on point: If you decide that you didn't want to save that record, block it from a BeforeUpdate event. Then you can do an UnDo or take some other action. It is more effort to reverse an already-made error than it is to stop yourself from making the error.

 

[Waddy]

Thanks DBGuy,

This did the trick

 

[Waddy]

The Doc Man,

Wanted to do both for practicing my VBA skills and this also work.

Thank you all, you really are a massive help!!!

 

[theDBguy]

Thanks DBGuy,

This did the trick

Hi. You’re welcome. We’re all happy to assist. Good luck with your project.