Nested If and ElseIf Syntax

[scottbouley]

HELP!

I'm trying to check a form for missing values. Two of the fields are required and therefore the corresponding message boxes are VBOkOnly. The other two are for warning purposes and their message boxes are VBYesNo.

First the code asks if the user wants to save the record. If the user answers Yes, the code should proceed to check for errors and allow the user to correct them. Once all of the errors are corrected a message box display "End of Yes Test" should appear. if the user answers No, a message box displaying "End of No Test" should appear. The No part is working correctly but the Yes part is only partially working.

The checks for Date and CustomerID work fine. However, if a ShipMethodID other than 1 is selected, the process stops and doesn't check the final field (ShipCost) or display the "End of Yes Test" message box.

I'm think I have an End If in the wrong place.

My code is below:

Private Sub cmdTest_Click()

If MsgBox("Do You Want To Save The Sales Order?", vbDefaultButton1 + vbYesNo) = vbYes Then

    If ((IsNull(Date) Or (Date) = " ")) Then
        MsgBox "Please enter a date", vbOKOnly
        Date.SetFocus
        Exit Sub
                        
    ElseIf ((CustomerID) = 0) Then
        MsgBox "Please select a customer", vbOKOnly
        CustomerID.SetFocus
        Exit Sub

    ElseIf ((ShipMethodID) = 1) Then
        If MsgBox("You haven't selected a shipping method, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
        ShipMethodID.SetFocus
        Exit Sub
        Else
        End If
    
    ElseIf ((ShipCost) = 0) Then
        If MsgBox("You haven't entered a shipping cost, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
        ShipCost.SetFocus
        Exit Sub
        Else
        End If
    Else
    MsgBox "End of Yes Test", vbOKOnly
    End If
    Else
    MsgBox "End of No Test", vbOKOnly
    End If
        
End Sub

Thank you in advance.

[NeoPa]

Well, the first thing I would suggest is to indent your code properly. This isn't just applied randomly, but is a very real help in situations where you need to determine where you are in the code.

I strongly suspect the flaw will become obvious to you once this is done. If not, repost the corrected code and we will look at it for you.

[scottbouley]

I don't know how it is suppose to be indented, can you help?

[NeoPa]

OK.

Everything which is dependant on an If or an Else (or even an ElseIf - one word) should be indented from the lines which define their dependencies. Matching Elses, ElseIfs and End Ifs should all be at the same level.

Similar (if less complicated) indenting should also be done for With ... End With; For ... Next; While ... Wend; Do ... Loop; etc constructs. There are more, but I hope you get the concept and the idea.

EG

If A Then
  B
  C
ElseIf D Then
  E
Else
  F
  If G Then
    H
  End If
End If
I
J

Does that explain things clearly?

[NeoPa]

I understand that you find my last post hard to follow.

Let's try another approach. Have you compiled this code? Did it compile successfully?

[NeoPa]

By the way, your code correctly indented (using two spaces instead of four simply due to the restrictions on space in this forum - four is generally used) is this. It would be a good idea for you to look at it and understand how it works.

Private Sub cmdTest_Click()
  If MsgBox("Do You Want To Save The Sales Order?", vbDefaultButton1 + vbYesNo) = vbYes Then
    If ((IsNull(Date) Or (Date) = " ")) Then
      MsgBox "Please enter a date", vbOKOnly
      Date.SetFocus
      Exit Sub

    ElseIf ((CustomerID) = 0) Then
      MsgBox "Please select a customer", vbOKOnly
      CustomerID.SetFocus
      Exit Sub

    ElseIf ((ShipMethodID) = 1) Then
      If MsgBox("You haven't selected a shipping method, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
        ShipMethodID.SetFocus
        Exit Sub
      Else
      End If

    ElseIf ((ShipCost) = 0) Then
      If MsgBox("You haven't entered a shipping cost, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
        ShipCost.SetFocus
        Exit Sub
      Else
      End If
    Else
      MsgBox "End of Yes Test", vbOKOnly
    End If
  Else
    MsgBox "End of No Test", vbOKOnly
  End If

End Sub

Check the code carefully to see if you can now see some items that don't make good sense.

[scottbouley]

My code does compile correctly, I've got the correct number of parameters for how it is currently arranged. I think the logic is out of sequence. I'm working on this for a friend and have been on this part alone for almost two weeks.

[scottbouley]

I believe the problem is with the End If statements following Else in the checks for ShipMethodID and ShipCost since the code works fine as long as I choose No. I tried moving the End If statements to various places with no success.

[NeoPa]

The "End of Yes Test" code is put in the Else part of the If statement. This implies that it is one of the tests. It's not. It is due to execute AFTER all the tests regardless of which tests run.

An Else is never required if it contains no statements (See lines #17, #18, #24 & #25 of my posted version).

Let me know how you get on with these changes.

[NeoPa]

In future, it will probably be easier not to complicate the logic with ElseIfs or any Elses after Exit Subs.

An End If after an Exit Sub clears down the levels of logic. It allows you to start again as if from scratch.

[scottbouley]

I've incorporated most of the changes you recommended and the code works fine until all of the conditions are met. In which case the "End of Yes Test" messagebox doesn't execute. I tried eliminating the last Else but then other errors pop up. I might have fixed one thing and broken another. My new code is:

Private Sub cmdTest_Click()
If MsgBox("Do You Want To Save The Sales Order?", vbDefaultButton1 + vbYesNo) = vbYes Then
    If ((IsNull(Date) Or (Date) = " ")) Then
        MsgBox "Please enter a date", vbOKOnly
        Date.SetFocus
        Exit Sub
    End If
    If ((CustomerID) = 0) Then
        MsgBox "Please select a customer", vbOKOnly
        CustomerID.SetFocus
        Exit Sub
    End If
    If ((ShipMethodID) = 1) Then
        If MsgBox("You haven't selected a shipping method, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
            ShipMethodID.SetFocus
            Exit Sub
        End If
    End If
    If ((ShipCost) = 0) Then
        If MsgBox("You haven't entered a shipping cost, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
            ShipCost.SetFocus
            Exit Sub
        Else
            MsgBox "End of Yes Test", vbOKOnly
        End If
    End If
Else
    MsgBox "End of No Test", vbOKOnly
End If
End Sub

Any Ideas?

[NeoPa]

You should find the answer in the first paragraph of my post #9.

[scottbouley]

You should find the answer in the first paragraph of my post #9.

I don't know why I didn't see it last night, it was so obvious tonight. Thanks for the pointers. The corrected code is below. All need to do now is paste the save and undo code and I'm good.

Private Sub cmdTest_Click()
If MsgBox("Do You Want To Save The Sales Order?", vbDefaultButton1 + vbYesNo) = vbYes Then
    If ((IsNull(Date) Or (Date) = " ")) Then
        MsgBox "Please enter a date", vbOKOnly
        Date.SetFocus
        Exit Sub
    End If
    If ((CustomerID) = 0) Then
        MsgBox "Please select a customer", vbOKOnly
        CustomerID.SetFocus
        Exit Sub
    End If
    If ((ShipMethodID) = 1) Then
        If MsgBox("You haven't selected a shipping method, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
            ShipMethodID.SetFocus
            Exit Sub
        End If
    End If
    If ((ShipCost) = 0) Then
        If MsgBox("You haven't entered a shipping cost, do you want to continue anyway?", vbDefaultButton1 + vbYesNo) = vbNo Then
            ShipCost.SetFocus
            Exit Sub
        End If
    End If
        MsgBox "End of Yes Test", vbOKOnly
Else
    MsgBox "End of No Test", vbOKOnly
End If
End Sub

[NeoPa]

Excellent. Well done.