Using An Error Handler to handle 2 Errors

[smherron1]

Hello,

I am using 2 queries to try and retieve a first name and surname for the correct employee using, the first query is for current employees and the second is for employees who have left the company. What i want the code to do is to check the first query to check if the employee is current and if there is an error check the 2nd query and then if there is still an error i want it to show a message box to the user to check that they have refreshed the queries and also that thye have entered the correct number.

Private Sub cmdInput_Click()

Dim INum As String
Dim FOLW As String
Dim Tday As Date
Dim Rw As Double
Dim RNW As Double
Dim Note As String

Note = txtNote.Text
Tday = FormatDateTime(Now(), vbShortDate)
GCI = txtGCI.Text
INum = txtINum.Text
FOLW = txtFOLW.Text

Sheets("Files On Loan").Protect UserInterfaceonly:=True
Sheets("Person Data").Visible = True
Sheets("Person Data").Select

Application.ScreenUpdating = False
Sheets("Files On Loan").Select

Rw = Range("A65536").End(xlUp).Offset(1, 0).Row

Sheets("Person Data").Visible = True
Sheets("Terminations").Visible = True

On Error GoTo Terminated
Sheets("Person Data").Select
Range("A1:A65536").Select
Selection.Find(What:=GCI, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select
Firstname = Selection.Offset(0, 4).Value
Surname = Selection.Offset(0, 5).Value
Ans = MsgBox("This GCI corresponds to " & Firstname & " " & Surname & " Is this Correct?", vbYesNo)
If Ans = vbNo Then
GoTo NOGCI
ElseIf Ans = vbYes Then

    Sheets("Files On Loan").Unprotect
    Sheets("Files On Loan").Select
    
    If cboxMF = False Then
    Cells(Rw, 1).Value = GCI
    Cells(Rw, 1).Value = GCI + 0
    Cells(Rw, 12).Value = "File On Loan"
    Cells(Rw, 13).Value = FOLW
    Cells(Rw, 14).Value = INum
    Cells(Rw, 15).Value = Tday
    Cells(Rw, 16).Value = Note
    
    Else
    Cells(Rw, 1).Value = GCI
    Cells(Rw, 1).Value = GCI + 0
    Cells(Rw, 12).Value = "Missing No File"
    Cells(Rw, 13).Value = FOLW
    Cells(Rw, 14).Value = INum
    Cells(Rw, 15).Value = Tday
    Cells(Rw, 16).Value = Note
    
    End If

'Put Formulas in to Lookup Details
    
'Status
    Range("B" & Rw).FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(VLOOKUP(RC[-1],'Person Data'!C[-1]:C[12],12,FALSE)="""",""Current"",IF(VLOOKUP(RC[-1],'Person Data'!C[-1]:C[12],12,FALSE)>=TODAY(),""Future Leaver"",""Leaver"")))"
'Employee Number
    Range("C" & Rw).FormulaR1C1 = _
        "=IF(RC[-2]="""","""",VLOOKUP(RC[-2],'Person Data'!C[-2]:C[11],3,FALSE))"
'First Name
    Range("D" & Rw).FormulaR1C1 = _
        "=IF(RC[-3]="""","""",VLOOKUP(RC[-3],'Person Data'!C[-3]:C[10],5,FALSE))"
'Surname
    Range("E" & Rw).FormulaR1C1 = _
        "=IF(RC[-4]="""","""",VLOOKUP(RC[-4],'Person Data'!C[-4]:C[9],6,FALSE))"
'Business
    Range("F" & Rw).FormulaR1C1 = _
        "=IF(RC[-5]="""","""",VLOOKUP(RC[-5],'Person Data'!C[-5]:C[8],7,FALSE))"
'Business Area
    Range("G" & Rw).FormulaR1C1 = _
        "=IF(RC[-6]="""","""",VLOOKUP(RC[-6],'Person Data'!C[-6]:C[7],8,FALSE))"
'Site
    Range("H" & Rw).FormulaR1C1 = _
        "=IF(RC[-7]="""","""",(VLOOKUP('Files On Loan'!RC1,'Person Data'!C[-7]:C[6],10,FALSE)))"
'NI number
 Range("I" & Rw).FormulaR1C1 = _
        "=IF(RC[-8]="""","""",(VLOOKUP('Files On Loan'!RC1,'Person Data'!C[-8]:C[5],2,FALSE)))"
'Start Date
    Range("J" & Rw).FormulaR1C1 = _
        "=IF(RC[-9]="""","""",(VLOOKUP('Files On Loan'!RC1,'Person Data'!C[-9]:C[4],11,FALSE)))"
'End Date
    Range("K" & Rw).FormulaR1C1 = _
        "=IF(RC[-10]="""","""",IF(VLOOKUP(RC[-10],'Person Data'!C[-10]:C[3],12,FALSE)="""","""",VLOOKUP('Files On Loan'!RC[-10],'Person Data'!C[-10]:C[3],12,FALSE)))"

Rows(Rw).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False

frm_Add.hide
txtFOLW.Text = ""
txtINum.Text = ""
txtGCI.Text = ""
txtNote.Text = ""

Rows(Rw).Select
Sheets("Files On Loan").Protect
Sheets("Person Data").Visible = False
Sheets("Terminations").Visible = False

Exit Sub
End If


Terminated:
Sheets("Terminations").Select
Range("A1:A65536").Select
On Error GoTo NOGCI
Selection.Find(What:=GCI, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Select [b]' This is where the debug happens each time an incorrect number is entered [/b]
Firstname = Selection.Offset(0, 4).Value
Surname = Selection.Offset(0, 5).Value
Ans1 = MsgBox("This GCI corresponds to " & Firstname & " " & Surname & " Is this Correct?", vbYesNo)
If Ans1 = vbNo Then
GoTo NOGCI
ElseIf Ans1 = vbYes Then
    Sheets("Files On Loan").Unprotect
    Sheets("Files On Loan").Select
    If cboxMF = False Then
    Cells(Rw, 1).Value = GCI
    Cells(Rw, 1).Value = GCI + 0
    Cells(Rw, 12).Value = "File On Loan"
    Cells(Rw, 13).Value = FOLW
    Cells(Rw, 14).Value = INum
    Cells(Rw, 15).Value = Tday
    Cells(Rw, 16).Value = Note
    
    Else
    Cells(Rw, 1).Value = GCI
    Cells(Rw, 1).Value = GCI + 0
    Cells(Rw, 12).Value = "Missing No File"
    Cells(Rw, 13).Value = FOLW
    Cells(Rw, 14).Value = INum
    Cells(Rw, 15).Value = Tday
    Cells(Rw, 16).Value = Note

On Error GoTo 0
End If

'Put Formulas in to Lookup Details
    
'Status
    Range("B" & Rw).FormulaR1C1 = _
        "=IF(RC[-1]="""","""",IF(VLOOKUP(RC[-1],'Person Data'!C[-1]:C[12],12,FALSE)="""",""Current"",IF(VLOOKUP(RC[-1],'Person Data'!C[-1]:C[12],12,FALSE)>=TODAY(),""Future Leaver"",""Leaver"")))"
'Employee Number
    Range("C" & Rw).FormulaR1C1 = _
        "=IF(RC[-2]="""","""",VLOOKUP(RC[-2],'Person Data'!C[-2]:C[11],3,FALSE))"
'First Name
    Range("D" & Rw).FormulaR1C1 = _
        "=IF(RC[-3]="""","""",VLOOKUP(RC[-3],'Person Data'!C[-3]:C[10],5,FALSE))"
'Surname
    Range("E" & Rw).FormulaR1C1 = _
        "=IF(RC[-4]="""","""",VLOOKUP(RC[-4],'Person Data'!C[-4]:C[9],6,FALSE))"
'Business
    Range("F" & Rw).FormulaR1C1 = _
        "=IF(RC[-5]="""","""",VLOOKUP(RC[-5],'Person Data'!C[-5]:C[8],7,FALSE))"
'Business Area
    Range("G" & Rw).FormulaR1C1 = _
        "=IF(RC[-6]="""","""",VLOOKUP(RC[-6],'Person Data'!C[-6]:C[7],8,FALSE))"
'Site
    Range("H" & Rw).FormulaR1C1 = _
        "=IF(RC[-7]="""","""",(VLOOKUP('Files On Loan'!RC1,'Person Data'!C[-7]:C[6],10,FALSE)))"
'NI number
 Range("I" & Rw).FormulaR1C1 = _
        "=IF(RC[-8]="""","""",(VLOOKUP('Files On Loan'!RC1,'Person Data'!C[-8]:C[5],2,FALSE)))"
'Start Date
    Range("J" & Rw).FormulaR1C1 = _
        "=IF(RC[-9]="""","""",(VLOOKUP('Files On Loan'!RC1,'Person Data'!C[-9]:C[4],11,FALSE)))"
'End Date
    Range("K" & Rw).FormulaR1C1 = _
        "=IF(RC[-10]="""","""",IF(VLOOKUP(RC[-10],'Person Data'!C[-10]:C[3],12,FALSE)="""","""",VLOOKUP('Files On Loan'!RC[-10],'Person Data'!C[-10]:C[3],12,FALSE)))"

Rows(Rw).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False

frm_Add.hide
txtFOLW.Text = ""
txtINum.Text = ""
txtGCI.Text = ""
txtNote.Text = ""

Rows(Rw).Select
Sheets("Files On Loan").Protect
Sheets("Person Data").Visible = False
Sheets("Terminations").Visible = False

End If
Exit Sub

NOGCI:
MsgBox "This GCI is not listed on the query, please make sure that the query has been refreshed and the GCI is correct", vbInformation
Sheets("Person Data").Visible = False
Sheets("Terminations").Visible = False


End Sub

If anyone can help with this it would be greatly appreciated.