Error Handler problem - Please help

[stockwell43]

Hello,

I have some code behind a form. What I want to do is set it up so if the
user enters a Property Code other than what I coded, I want a message to pop
up saying "Incoorect Property Code". For some reason mine doesn't work. When
I enter a code that is not coded it accepts it and does nothing. Here is the
Code:

Private Sub CollateralCode_AfterUpdate()
'Field named Property Code
On Error GoTo EH

If Me.CollateralCode = "17" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "18" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "30" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "41" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "42" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "43" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "44" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "45" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "47" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "48" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "49" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "50" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "51" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "52" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "55" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "56" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "57" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "58" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "59" Then
Me.Rate = "0.1000"
Else
EH:
If Err.Number = 2501 Then
MsgBox "Incorrect Property Code"
End If
End If

End Sub

Any help would be most appreciated.

Thanks!!!

 

[Jeanette Cunningham]

Hi stockwell,

the problem lies in the last few lines of the code

ElseIf Me.CollateralCode = "59" Then
Me.Rate = "0.1000"
Else
EH:
If Err.Number = 2501 Then
MsgBox "Incorrect Property Code"
End If
End If

End Sub


Change the position of the error handler so it's outside the If ... End If
for the property code.

ElseIf Me.CollateralCode = "59" Then
Me.Rate = "0.1000"
Else
End If

EH:
If Err.Number = 2501 Then
MsgBox "Incorrect Property Code"
End If

End Sub


Or use a different approach without an error handler

ElseIf Me.CollateralCode = "59" Then
Me.Rate = "0.1000"
Else
MsgBox "Incorrect Property Code"
End If

End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John W. Vinson]

I have some code behind a form. What I want to do is set it up so if the
user enters a Property Code other than what I coded, I want a message to pop
up saying "Incoorect Property Code". For some reason mine doesn't work. When
I enter a code that is not coded it accepts it and does nothing. Here is the
Code:

Private Sub CollateralCode_AfterUpdate()
'Field named Property Code
On Error GoTo EH

If Me.CollateralCode = "17" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "18" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "30" Then
Me.Rate = "0.0833"
ElseIf Me.CollateralCode = "41" Then
Me.Rate = "0.1000"
ElseIf Me.CollateralCode = "42" Then

This example is just crying out for a table driven solution! You could do this
with no code at all if you had a Rates table with two fields, CollateralCode
and Rate. You could display the rate on the form with a textbox with control
source

=NZ(DLookUp("Rate", "Rates", "[CollateralCode] = " & [CollateralCode]),
"Incorrect Property Code")

This would have the further advantage that if you add a code, or change a
rate, you merely need to edit a table entry rather than dig down into some
lengthy VBA code.

 

[stockwell43]

Hi Jeanette,

I took out the error Handler and just used the message box and it works
great! Thank you so much for your help. )