Automate data input with If, Elseif Statement

[Exmark1]

Automate data input with If, Elseif Statement
I am working with a access 2007 Database for a Lawn Service Company, I have everyone assigned to a Treatment program based on the number of applications to apply. e.g. Program 1= Round 1-7 Program 4 =Round 1,3,5,& 6 Program 5 =Round 1,2,3, & 6. When a customer is assignd to a program a group of fields named round 1 thru 7 are populated with yes or no based on the applications to apply E.G. Program 1 would be all yes, Program 5= round 1 yes, round 2 yes, round 3 yes , round 4 no, round 5 no, and round 6 yes. I am able to use a If, Elseif statement to automatically get access to enter the NSD(Nxt Service Date) and Nxt App No. based on the value entered in the App No Field and the Program assigned. The If, Elseif Statement works fine as long as I only skip 1 Application. This is the code entered in the On Exit property of the app no field.
Private Sub App_No_Exit(Cancel As Integer)
If [App No] = 1 And [Round 2] = True Then
[NSD] = "3/15/" & Year(Date)
ElseIf [App No] = 1 And [Round 2] = False Then
[NSD] = "5/1/" & Year(Date)
ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False Then
[NSD] = "6/15/" & Year(Date)


ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False And [Round 4] = False Then
[NSD] = "8/1/" & Year(Date)
ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False And [Round 4] = False And [Round 5] = False Then
[NSD] = "9/15/" & Year(Date)
ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False And [Round 4] = False And [Round 5] = False And [Round 6] = False Then
[NSD] = "11/1" & Year(Date)
End If

If [App No] = 1 And [Round 2] = True Then
[Nxt App No] = 2
ElseIf [App No] = 1 And [Round 2] = False Then
[Nxt App No] = 3
ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False Then
[Nxt App No] = 4
ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False And [Round 4] = False Then
[Nxt App No] = 5
ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False And [Round 4] = False And [Round 5] = False Then
[Nxt App No] = 6
ElseIf [App No] = 1 And [Round 2] = False And [Round 3] = False And [Round 4] = False And [Round 5] = False And [Round 6] = False Then
[Nxt App No] = 7
End If

[NTC]

well you might check out Case Select method - it is a little cleaner for multiple choices such as this. What exactly is the question?

[rpeare]

Try this:

Private Sub App_No_Exit(Cancel As Integer)
EvaluateNext
End Sub

Function EvaluateNext()
Dim iCurrStep As Integer
Dim iMonth As Integer
Dim iDay As Integer

iCurrStep = [app no] + 1
For i = iCurrStep To 7

    If Me.Controls("[Round " & i & "]") = -1 Then
        If i Mod 2 = 0 Then
            iDay = 15
            iMonth = i * 1.5
        Else
            iDay = 1
            If (i / 2) - (Int(i / 2)) > 0 Then
                iMonth = i + ((i + 1) / 2)
            Else
                iMonth = i + (i / 2)
            End If
        End If
        NSD = DateSerial(Year(Date), iMonth, iDay)
        [Nxt App No] = i
        Exit Function
    End If
Next i
End Function

Though I would be inclined to put your application month/day in a table and have the function look it up, that way if your dates change over time the code would be more flexible and you wouldn't have to recode.

[Exmark1]

When I enter this code get error variable not define with i highlited. in line
For i = iCurrStep To 7

[rpeare]

are you sure your fields are being interpreted as numbers

app no in particular (as a side note stay away from using spaces or special characters other than underscore (_) in your object names (tables, fields, forms, etc) they cause no end of grief in programming)

to convert icurrstep to a number (integer) you can use

icurrstep = cint([app no]) + 1

then debug.print icurrstep to make sure you're getting an integer