asked on

Error handling for 'Runtime error 2427' in MS Access

Hi there,

I have a form that onload makes a few calculations based on text boxes in the form as per attached code. I would like to know how I can trap the error 'Runtime error 2427' or prevent it from disrupting the onload event. I have tried and On Error Go To 0 but it hasn't worked?

Please help?

Private Sub Form_Load()

If Me.Amount1.Value > Me.Amount2.Value Then
Me.txt1.ControlSource = "=Chr(113)"
Me.txt1.ForeColor = vbRed
ElseIf Me.Amount1.Value < Me.Amount2.Value Then
Me.txt1.ControlSource = "=Chr(112)"
Me.txt1.ForeColor = vbGreen
ElseIf Me.Amount1.Value = Me.Amount2.Value Then
Me.txt1.ControlSource = "=Chr(117)"
Me.txt1.ForeColor = vbOrange
Else
End If

'-----------Feb'

If Me.Amount2.Value > Me.Amount3.Value Then
Me.txt2.ControlSource = "=Chr(113)"
Me.txt2.ForeColor = vbRed
ElseIf Me.Amount2.Value < Me.Amount3.Value Then
Me.txt2.ControlSource = "=Chr(112)"
Me.txt2.ForeColor = vbGreen
ElseIf Me.Amount2.Value = Me.Amount3.Value Then
Me.txt2.ControlSource = "=Chr(117)"
Me.txt2.ForeColor = vbOrange
Else
End If


'-----------March'

If Me.Amount3.Value > Me.Amount4.Value Then
Me.txt3.ControlSource = "=Chr(113)"
Me.txt3.ForeColor = vbRed
ElseIf Me.Amount3.Value < Me.Amount4.Value Then
Me.txt3.ControlSource = "=Chr(112)"
Me.txt3.ForeColor = vbGreen
ElseIf Me.Amount3.Value = Me.Amount4.Value Then
Me.txt3.ControlSource = "=Chr(117)"
Me.txt3.ForeColor = vbOrange
Else
End If


'-----------April

If Me.Amount4.Value > Me.Amount5.Value Then
Me.txt4.ControlSource = "=Chr(113)"
Me.txt4.ForeColor = vbRed
ElseIf Me.Amount4.Value < Me.Amount5.Value Then
Me.txt4.ControlSource = "=Chr(112)"
Me.txt4.ForeColor = vbGreen
ElseIf Me.Amount4.Value = Me.Amount5.Value Then
Me.txt4.ControlSource = "=Chr(117)"
Me.txt4.ForeColor = vbOrange
Else
End If


'-----------May'

If Me.Amount5.Value > Me.Amount6.Value Then
Me.txt5.ControlSource = "=Chr(113)"
Me.txt5.ForeColor = vbRed
ElseIf Me.Amount5.Value < Me.Amount6.Value Then
Me.txt5.ControlSource = "=Chr(112)"
Me.txt5.ForeColor = vbGreen
ElseIf Me.Amount5.Value = Me.Amount6.Value Then
Me.txt5.ControlSource = "=Chr(117)"
Me.txt5.ForeColor = vbOrange
Else
End If


'-----------June'

If Me.Amount6.Value > Me.Amount7.Value Then
Me.txt6.ControlSource = "=Chr(113)"
Me.txt6.ForeColor = vbRed
ElseIf Me.Amount6.Value < Me.Amount7.Value Then
Me.txt6.ControlSource = "=Chr(112)"
Me.txt6.ForeColor = vbGreen
ElseIf Me.Amount6.Value = Me.Amount7.Value Then
Me.txt6.ControlSource = "=Chr(117)"
Me.txt6.ForeColor = vbOrange
Else
End If


'-----------July'

If Me.Amount7.Value > Me.Amount8.Value Then
Me.txt7.ControlSource = "=Chr(113)"
Me.txt7.ForeColor = vbRed
ElseIf Me.Amount7.Value < Me.Amount8.Value Then
Me.txt7.ControlSource = "=Chr(112)"
Me.txt7.ForeColor = vbGreen
ElseIf Me.Amount7.Value = Me.Amount8.Value Then
Me.txt7.ControlSource = "=Chr(117)"
Me.txt7.ForeColor = vbOrange
Else
End If


'-----------August'

If Me.Amount8.Value > Me.Amount9.Value Then
Me.txt8.ControlSource = "=Chr(113)"
Me.txt8.ForeColor = vbRed
ElseIf Me.Amount8.Value < Me.Amount9.Value Then
Me.txt8.ControlSource = "=Chr(112)"
Me.txt8.ForeColor = vbGreen
ElseIf Me.Amount8.Value = Me.Amount9.Value Then
Me.txt8.ControlSource = "=Chr(117)"
Me.txt8.ForeColor = vbOrange
Else
End If


'-----------September'

If Me.Amount9.Value > Me.Amount10.Value Then
Me.txt9.ControlSource = "=Chr(113)"
Me.txt9.ForeColor = vbRed
ElseIf Me.Amount9.Value < Me.Amount10.Value Then
Me.txt9.ControlSource = "=Chr(112)"
Me.txt9.ForeColor = vbGreen
ElseIf Me.Amount9.Value = Me.Amount10.Value Then
Me.txt9.ControlSource = "=Chr(117)"
Me.txt9.ForeColor = vbOrange
Else
End If


'-----------October'

If Me.Amount10.Value > Me.Amount11.Value Then
Me.txt10.ControlSource = "=Chr(113)"
Me.txt10.ForeColor = vbRed
ElseIf Me.Amount10.Value < Me.Amount11.Value Then
Me.txt10.ControlSource = "=Chr(112)"
Me.txt10.ForeColor = vbGreen
ElseIf Me.Amount10.Value = Me.Amount11.Value Then
Me.txt10.ControlSource = "=Chr(117)"
Me.txt10.ForeColor = vbOrange
Else
End If


'-----------November'

If Me.Amount11.Value > Me.Amount12.Value Then
Me.txt11.ControlSource = "=Chr(113)"
Me.txt11.ForeColor = vbRed
ElseIf Me.Amount11.Value < Me.Amount12.Value Then
Me.txt11.ControlSource = "=Chr(112)"
Me.txt11.ForeColor = vbGreen
ElseIf Me.Amount11.Value = Me.Amount12.Value Then
Me.txt11.ControlSource = "=Chr(117)"
Me.txt11.ForeColor = vbOrange

Else
End If
If Me.Amount1.Value > 1000 Then
Me.Text198.ControlSource = "=Chr(181)"
Else
Me.Text198.ControlSource = ""
End If

End Sub

Open in new window

Jez Walters

Try using "On Error Goto ErrorHandler", and then add a block of code starting with the label "ErrorHandler:" to handle your errors.

databarracks

ASKER

Could you help with the block of code? Would it be like this:

ErrHandler:
If Err <> False Then 'False' Boolean constant = 0
Select Case Err.Number
Case ERR_MYERROR
[...]
Case Else
[...]
End Select
Err.Clear
End If
End Sub

And would I put this at the bottom of the attached code.

ASKER CERTIFIED SOLUTION

Jez Walters

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

databarracks

ASKER

I copied that code from somewhere but don't really understand it to be frank

Jez Walters

It would be a good idea to give your controls more meaningful names too!

Jez Walters

I amended your code to remove the superfluous "Else" keywords, "Me." prefixes and ".Value" suffixes and also indented it better.

Adding the "On Error" statement tells Access to go to the code starting with "ErrorHandler:" if there's an error (from that point onwards in the subroutine).

The code I put as a starting point in the error handler examines the error number (Err.Number) and acts accordingly - either doing nothing (currently) or displaying a MsgBox.

Make sense now?

Jez Walters

The "normal code" of your Sub needs to end with an explicit "Exit Sub" to stop it blundering into the error handler when there isn't any error.

databarracks

ASKER

Ok thank you please give me a minute to try it

databarracks

ASKER

Have tried it and has worked perfectly. I cannot thank you enough for your help on this and have learnt a lot about clean coding and error handling.

Thank you once again for your very clear and concise explanation

databarracks

ASKER

Wonderful work by Jez. Very prompt with his replies and spot on with the solution and explanation. Brilliant job all round