VBA Error Handling Clear

[josephff]

Hello to all,

My issue is i have a small process wrapped in an error handler which does the job it needs to. However once the error has been corrected it does not continue.

On Error GoTo errorname
    Dim myStr As String
    Dim NameSplit As String
    Dim myStr1 As String
    NameSplit = Me.EmployeeName.Value
 
    myStr = Split(NameSplit, " ")(1)
    myStr1 = Split(NameSplit, " ")(0)
 
    Me.LastNameLabel.Caption = myStr
    Me.FirstNameLabel.Caption = myStr1
errorname:
    Me.ErrorLabel.Caption = "Please Input Employee Name In This Format ""First Last"""
    Me.EmployeeName.BackColor = vbRed
    Err.Clear
    Exit Sub

so if someone inputs : "John" it will throw an error and i will catch it and remind them that the name must be in "First Last" format.

It does this fine, however when i go back into the text box and add the last name it is still acting as if there is an error.

So my question is how do i fix this? I added the err.clear statement hoping it would clear it out but no such luck.

Any ideas?

[RuralGuy]

How does the code get out of that procedure when the is *no* error?

[josephff]

How does the code get out of that procedure when the is *no* error?

Im not sure what you mean? If there is no error shouldnt it bypass the error handler all together and continue with the rest of the code underneath (not pictured)

[RuralGuy]

What is then next line of code to execute after this one:
Me.FirstNameLabel.Caption = myStr1
...when there is *no* error?

[josephff]

it goes into various validations then ulitmataly into the database input

heres the whole code....

ouch let me revamp this

[josephff]

What is then next line of code to execute after this one:
Me.FirstNameLabel.Caption = myStr1
...when there is *no* error?

Private Sub SaveBtn_Click()
'On Error GoTo errorname
Dim myStr As String
Dim NameSplit As String
Dim myStr1 As String
NameSplit = Form_NewEmployee.EmployeeName.Value
 
myStr = Split(NameSplit, " ")(1)
myStr1 = Split(NameSplit, " ")(0)
 
Me.LastNameLabel.Caption = myStr
Me.FirstNameLabel.Caption = myStr1
'errorname:
' Me.ErrorLabel.Caption = "Please Input Employee Name In This Format ""First Last"""
' Me.EmployeeName.BackColor = vbRed
'' Err.Clear
' Exit Sub
 
If IsNull(Me.EmployeeName) Then
Me.EmployeeName.BackColor = vbRed
End If
If IsNull(Me.EmployeeTitle) Then
Me.EmployeeTitle.BackColor = vbRed
End If
If IsNull(Me.EmployeeNumber) Then
Me.EmployeeNumber.BackColor = vbRed
End If
If IsNull(Me.EmployeeBirthdate) Then
Me.EmployeeBirthdate.BackColor = vbRed
End If
If IsNull(Me.EmployeePhone) Then
Me.EmployeePhone.BackColor = vbRed
End If
If IsNull(Me.EmployeeSSN) Then
Me.EmployeeSSN.BackColor = vbRed
End If
If IsNull(Me.EmployeeAddress1) Then
Me.EmployeeAddress1.BackColor = vbRed
End If
If IsNull(Me.EmployeeCity) Then
Me.EmployeeCity.BackColor = vbRed
End If
If IsNull(Me.EmployeeSt) Then
Me.EmployeeSt.BackColor = vbRed
End If
If IsNull(Me.EmployeeZip) Then
Me.EmployeeZip.BackColor = vbRed
End If
If IsNull(Me.EmployeeHireDate) Then
Me.EmployeeHireDate.BackColor = vbRed
End If
If IsNull(Me.EmployeeRate) Then
Me.EmployeeRate.BackColor = vbRed
End If
If Me.SexOptionFemale = False And Me.SexOptionMale = False Then
Me.Label47.BackColor = vbRed
Me.Label51.BackColor = vbRed
End If
If Me.StatusOptionMarried = False And Me.StatusOptionSingle = False Then
Me.Label53.BackColor = vbRed
Me.Label55.BackColor = vbRed
End If
 
If Me.EmployeeName.BackColor = vbRed Or Me.EmployeeTitle.BackColor = vbRed Or "" _
& Me.EmployeeNumber.BackColor = vbRed Or Me.EmployeeBirthdate.BackColor = vbRed Or "" _
& Me.EmployeePhone.BackColor = vbRed Or Me.EmployeeSSN.BackColor = vbRed Or "" _
& Me.EmployeeAddress1.BackColor = vbRed Or Me.EmployeeCity.BackColor = vbRed Or "" _
& Me.EmployeeZip.BackColor = vbRed Or Me.EmployeeSt.BackColor = vbRed Or "" _
& Me.EmployeeHireDate.BackColor = vbRed Or Me.EmployeeRate.BackColor = vbRed Or "" _
& Me.Label47.BackColor = vbRed Or Me.Label51.BackColor = vbRed Or Me.Label53.BackColor = vbRed Or "" _
& Me.Label55.BackColor = vbRed Then
Me.ErrorLabel.Caption = "Please Fill In The Needed Information"
Exit Sub
Else
DoCmd.SetWarnings False
Dim SaveInfo As String
SaveInfo = "INSERT INTO Employee (EmployeeNumber) VALUES ( " & Me.EmployeeNumber.Value & ");"
DoCmd.RunSQL SaveInfo
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeFullName] = " & Chr(34) & Me.EmployeeName.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeFirstName] = " & Chr(34) & Me.FirstNameLabel.Caption & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeLastName] = " & Chr(34) & Me.LastNameLabel.Caption & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeePosition] = " & Chr(34) & Me.EmployeeTitle.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeBirthdate] = " & Chr(34) & Me.EmployeeBirthdate.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeePhone] = " & Chr(34) & Me.EmployeePhone.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeSSN] = " & Chr(34) & Me.EmployeeSSN.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeAddress1] = " & Chr(34) & Me.EmployeeAddress1.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeAddress2] = " & Chr(34) & Me.EmployeeAddress2.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeCity] = " & Chr(34) & Me.EmployeeCity.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
Me.EmployeeSt.SetFocus
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeSt] = " & Chr(34) & Me.EmployeeSt.Text & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeZip] = " & Chr(34) & Me.EmployeeZip.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [HireDate] = " & Chr(34) & Me.EmployeeHireDate.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [LastRateChange] = " & Chr(34) & Me.EmployeeRateChange.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [PayCardNumber] = " & Me.EmployeePayCard.Value & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [RateOfPay] = " & Chr(34) & Me.EmployeeRate.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
If Me.SexOptionFemale = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeSex] = 'Female' WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.SexOptionMale = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeSex] = 'Male' WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.StatusOptionMarried = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeStatus] = 'Married' WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.StatusOptionSingle = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [EmployeeStatus] = 'Single' WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.CheckTools = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceTools] = True WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.CheckTools = False Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceTools] = False WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.CheckSecurity = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceSecurity] = True WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.CheckSecurity = False Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceSecurity] = False WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.CheckKeys = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceKeys] = True WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.CheckKeys = False Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceKeys] = False WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.CheckComputer = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceComputer] = True WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.CheckComputer = False Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceComputer] = False WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.CheckCash = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceCash] = True WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.CheckCash = False Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceCash] = False WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.CheckCredit = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceCreditCard] = True WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.CheckCredit = False Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceCreditCard] = False WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
If Me.CheckOther = True Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceOther] = True WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceOtherDetail] = " & Chr(34) & Me.Other.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
ElseIf Me.CheckOther = False Then
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceOther] = False WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
DoCmd.RunSQL "UPDATE [Employee] SET [ClearanceOtherDetail] = " & Chr(34) & Me.Other.Value & Chr(34) & " WHERE [Employee].[EmployeeNumber] = " & Me.EmployeeNumber & ";"
End If
DoCmd.SetWarnings True
End If
DoCmd.OpenForm ["Form_EmployeeMain"]
End Sub

[josephff]

you see i changed the variable to explicitly call the right one from the right form...

one thing that i didnt think of was the simplest answer...maybe its throwing another error...

i am going to be trying this in a few and ill check back in

[RuralGuy]

Now it is commented out so the code will run. That is different that what you posted.

[josephff]

its commented out because im working on other parts at the end of the code so i still want to be able to test it. The only thing that is different is i changed

NameSplit = me.EmployeeName.Value

to

NameSplit = Form_NewEmployee.EmployeeName.Value

anyways i tested the other and still get the same thing. I know the code works because if i enter a first name and last name the code functions properly..If i enter only a first name then i get a subscript out of range error. Hence why i want to capture that error and remind a user to input both first and last name. It just seems as though once the error handler picks up the error its not resetting. So when i go back and input the last name, its still throwing the same error even though now there isnt one

[RuralGuy]

You have nothing in the code to "skip" the error handler if there is no error. VBA will not do this on its own.

[josephff]

OK that makes sense, but i always thought it would just automatically do it if no error. How would i go about fixing it? Maybe throw in a resume somewhere?

[RuralGuy]

This is how we usually do error handling:

Private Sub Foo()
   On Error GoTo ErrorHandler
   '
   '  Your procedure code
   '
Exit_Foo:
   Exit Sub
ErrorHandler:
   MsgBox "Error No:    " & lngErrNumber & vbCrLf & _
          "Description: " & strErrDescription
   Resume Exit_Foo
End Sub

[josephff]

ok i will incorporate this one into the code...ive read a lot and have seen this a lot but i dont understand the chain of events in it.

on error goto error handler
show messagebox with error
resume exit_foo
then exit the sub

How will it go back up and try the actual procedure code again? Or does it not have too?

[RuralGuy]

The way you have it set up, you tell them to fix the problem and then they should push the button again.

[josephff]

yeah, thats how i want it to be. Once the problem is fixed hit the button and the code shoud work