If ...then ...else help

[Lorien2733]

I have code in the Before Update event of a form that checks the work date of
a data entry transaction against a history file and returns an error msg if
it finds a match. This code works fine. I then added code that would also
check the transaction against the current data file. This code works as well.
The problem is when I put the 2 together. For some reason it isn't falling
thru to do the second check.
Here's my code -

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varkey1 As Variant
Dim varkey2 As Variant
Dim varkey3 As Variant
Dim varkey4 As Variant
Dim Ans1 As Integer
Dim Ans2 As Integer
' Prompt for missing date
If IsNull(STARTDATE) Then
MsgBox "Please enter work date."
STARTDATE.SetFocus

' Check for proper date
Else
If (STARTDATE) > (ENDDATE) Then
MsgBox "Your Work End Date is prior to your Work Start Date. Please
correct."
STARTDATE.SetFocus

Else
' Check for duplicate in history
varkey1 = (DLookup("[tblhistory]![Budget Code]", "[tblHistory]", "
((([tblHistory]![EMPL NUMBER] = Forms![SBPREARN]![EMPL NUMBER]) and
(Forms![SBPREARN]![StartDate] = [tblHistory]![startdate])) or
(([tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and
(Forms![SBPREARN]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))
varkey2 = (DLookup("[tblhistory]![remote batch number]",
"[tblHistory]", " ((([tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL
NUMBER]) and (Forms![SBPREARN]![StartDate] = [tblHistory]![startdate])) or
(([tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and
(Forms![SBPREARN]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))
[POSSIBLEDUPE] = varkey2

If (varkey1) = BUDGET_CODE And Not IsNull(varkey2) Then
Ans1 = MsgBox("This employee already has a payment in the history
file for this work date. Check batch number " & varkey2, vbRetryCancel,
"Invalid Date")
If Ans1 = vbRetry Then
STARTDATE.SetFocus
If Ans1 = vbCancel Then
Exit Sub

Else
' Check for duplicate in current data
varkey3 = (DLookup("[tblEarnings]![Budget Code]", "[tblEarnings]", "
((([tblEarnings]![EMPL NUMBER] = Forms![SBPREARN]![EMPL NUMBER]) and
(Forms![SBPREARN]![StartDate] = [tblEarnings]![startdate])) or
(([tblEarnings]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and
(Forms![SBPREARN]![StartDate] between [tblEarnings]![startdate] and
[tblEarnings]![enddate]))) "))
varkey4 = (DLookup("[tblEarnings]![remote batch number]",
"[tblEarnings]", " ((([tblEarnings]![EMPL NUMBER] = Forms![Sbprearn]![EMPL
NUMBER]) and (Forms![SBPREARN]![StartDate] = [tblEarnings]![startdate])) or
(([tblEarnings]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and
(Forms![SBPREARN]![StartDate] between [tblEarnings]![startdate] and
[tblEarnings]![enddate]))) "))
[POSSIBLEDUPE] = varkey4

If (varkey3) = BUDGET_CODE And Not IsNull(varkey4) Then
Ans2 = MsgBox("This employee already has a payment in your current
data for this work date. Check batch number " & varkey4, vbRetryCancel,
"Invalid Date")
If Ans2 = vbRetry Then
STARTDATE.SetFocus
If Ans2 = vbCancel Then
Exit Sub

End If
End If
End If

End If
End If
End If
End If
End If
End Sub

If I comment out the first check, it will do the second. If I put the
current file check first, it does that one and not the history file check. I
figure it has to be something to do with that first Exit Sub but if it
doesn't find a match in whichever file its checking against it should skip
over that Exit Sub and fall thru to the next part ...right?
What am I not seeing here?
TIA
Lynne

 

[Wayne Morgan]

I've tried to take what you have and associate each End If and Else with its If...Then statement. I also tried to line them up so that the nesting is visible. As you can see, you aren't ending any of the If statements until the very end. This is causing a nesting that I don't think you want. The code editor is very good about doing this (without the color). With this many Ifs, I strongly recommend that you line them up as I've done here so that you can see what goes with what. Also, I recommend that you put a break point in near the top of this code and step through it, checking the values as you go (you can hover the mouse over a variable in the line of code to see what the value of the variable is) to see why it isn't doing what you want.


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim varkey1 As Variant
Dim varkey2 As Variant
Dim varkey3 As Variant
Dim varkey4 As Variant
Dim Ans1 As Integer
Dim Ans2 As Integer
' Prompt for missing date
If IsNull(STARTDATE) Then
MsgBox "Please enter work date."
STARTDATE.SetFocus

' Check for proper date
Else
If (STARTDATE) > (ENDDATE) Then
MsgBox "Your Work End Date is prior to your Work Start Date. Please correct."
STARTDATE.SetFocus

Else
' Check for duplicate in history
varkey1 = (DLookup("[tblhistory]![Budget Code]", "[tblHistory]", " ((([tblHistory]![EMPL NUMBER] = Forms![SBPREARN]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] = [tblHistory]![startdate])) or (([tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] between [tblHistory]![startdate] and [tblHistory]![enddate]))) "))
varkey2 = (DLookup("[tblhistory]![remote batch number]", "[tblHistory]", " ((([tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] = [tblHistory]![startdate])) or (([tblHistory]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] between [tblHistory]![startdate] and
[tblHistory]![enddate]))) "))
[POSSIBLEDUPE] = varkey2

If (varkey1) = BUDGET_CODE And Not IsNull(varkey2) Then
Ans1 = MsgBox("This employee already has a payment in the history
file for this work date. Check batch number " & varkey2, vbRetryCancel,
"Invalid Date")
If Ans1 = vbRetry Then
STARTDATE.SetFocus
If Ans1 = vbCancel Then
Exit Sub

Else
' Check for duplicate in current data
varkey3 = (DLookup("[tblEarnings]![Budget Code]", "[tblEarnings]", " ((([tblEarnings]![EMPL NUMBER] = Forms![SBPREARN]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] = [tblEarnings]![startdate])) or (([tblEarnings]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] between [tblEarnings]![startdate] and [tblEarnings]![enddate]))) "))
varkey4 = (DLookup("[tblEarnings]![remote batch number]", "[tblEarnings]", " ((([tblEarnings]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] = [tblEarnings]![startdate])) or (([tblEarnings]![EMPL NUMBER] = Forms![Sbprearn]![EMPL NUMBER]) and (Forms![SBPREARN]![StartDate] between [tblEarnings]![startdate] and [tblEarnings]![enddate]))) "))
[POSSIBLEDUPE] = varkey4

If (varkey3) = BUDGET_CODE And Not IsNull(varkey4) Then
Ans2 = MsgBox("This employee already has a payment in your current data for this work date. Check batch number " & varkey4, vbRetryCancel, "Invalid Date")
If Ans2 = vbRetry Then
STARTDATE.SetFocus
If Ans2 = vbCancel Then
Exit Sub

End If
End If
End If

End If
End If
End If
End If
End If
End Sub