Too many nested if...thens, is there a better way?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an if statement nesting nightmare. I got the basic statement, which
checks 3 criteria, to work but then I need it to check 2 additional controls
for the same 3 criteria and they are all intertwined because all criteria
essentially need to be checked. I can think thru how it needs to be set up
and nested but the more code I actually have the harder it is to follow and
there has got to be a better way. Can't I somehow set up the basic statements
for the 3 controls and somehow refer them to each other with a GoTo or
something?

If NameofFirsthing is not blank Then
If AmountofFirstthing is blank Then
If StrenghtofFirstthing is blank Then
Cancel, Warn, Refocus
Else If
Set Date, Refocus
End if
Else If
Set Date, Refocus
End If
Else if
Set Date, Refocus
End If

Obviously not true to coding but you get the idea. So, now I have a second &
third thing that need to be checked for all the same criteria whether any of
the criteria of the firstthing were met or not. EEeeeek! Any ideas? I'm
guessing there's a lot easier way to do this.

Any help is appreciated!
Kristine
 
I think what you are looking for is

If Not IsNull(NameofFirsthing) And IsNull(AmountofFirstthing) And
IsNull(StrenghtofFirstthing) Then
Cancel, Warn, Refocus
Else
Set Date, Refocus
End If
 
smilee8_28 said:
I have an if statement nesting nightmare. I got the basic statement, which
checks 3 criteria, to work but then I need it to check 2 additional controls
for the same 3 criteria and they are all intertwined because all criteria
essentially need to be checked. I can think thru how it needs to be set up
and nested but the more code I actually have the harder it is to follow and
there has got to be a better way. Can't I somehow set up the basic statements
for the 3 controls and somehow refer them to each other with a GoTo or
something?

If NameofFirsthing is not blank Then
If AmountofFirstthing is blank Then
If StrenghtofFirstthing is blank Then
Cancel, Warn, Refocus
Else If
Set Date, Refocus
End if
Else If
Set Date, Refocus
End If
Else if
Set Date, Refocus
End If

Obviously not true to coding but you get the idea. So, now I have a second &
third thing that need to be checked for all the same criteria whether any of
the criteria of the firstthing were met or not. EEeeeek! Any ideas? I'm
guessing there's a lot easier way to do this.
Click to expand...

Use the if/elseif/block to make decisions. That's what they
are for.

Write a function that does any actual work that needs to be done
and call it. That's what they are for.

In other words, take your duplicate inline code out of the if block.
It doesn't belong there.
 
Perfect! That streamlining made it doable! I got it to work except for one
glitch. The first line is actually:

If Not IsNull(Me.RX_1_Drug) Or Me.RX_1_Drug <> "" And
IsNull(Me.RX_1_Extra_Refills) Or Me.RX_1_Extra_Refills = "" And
Me.RX_1_Denied = No Then.....

RX_1_Denied is a checkbox and when it is clicked my thinking is RX_1_Denied
then = Yes and should no longer meet the above criteria statement but it
still follows the logic of 'Then'. I've tried = False and = "No" but they
don't work either. Ideas?
 
Try this

If (Not IsNull(Me.RX_1_Drug) Or Me.RX_1_Drug <> "") And
(IsNull(Me.RX_1_Extra_Refills) Or Me.RX_1_Extra_Refills = "") And
Me.RX_1_Denied = No Then
 
Found another glitch. I mistakenly had missed the 'Not' in the beginning If
Not IsNull(Me.RX_1_Drug) bit....and without it, it doesn't matter if RX 1
Drug is blank or not it follows the logic and actually works correctly, but
once I add the 'Not' it does catch the nonblank RX 1 Drug but then it follows
the 'Then' logic everytime. Do I need to group these somehow with parenthesis
or something so that it knows what to apply the 'Not' to and how to group the
'and's and 'or's?

Kristine
 
'------------------------------
If (Not IsNull(Me.RX_1_Drug) Or Me.RX_1_Drug <> "") And
(IsNull(Me.RX_1_Extra_Refills) Or Me.RX_1_Extra_Refills = "") And
Me.RX_1_Denied = No Then
'------------------------------

' is the same as

'------------------------------
If (RX_1_Drug & "" <> "") And
(RX_1_Extra_Refills & "" = "") And
Me.RX_1_Denied = No Then
'------------------------------

' is the same as

'------------------------------
If (RX_1_Drug & "" = "") Then
' Set control colors for missing info
Exit Sub
ElseIf (RX_1_Extra_Refills & "" <> "") Then

' Set control colors for missing info
Exit Sub
ElseIf RX_1_Denied <> No Then
' Set control colors for missing info
Exit Sub
End If
'====================
' Set control colors for info ok
' Do expected stuff here
'------------------------------

' is the same as

'------------------------------
Dim HasError As Boolean
HasError = False

If (RX_1_Drug & "" = "") Then
' Set control colors for missing info
HasError = True
ElseIf (RX_1_Extra_Refills & "" <> "") Then

' Set control colors for missing info
HasError = True
ElseIf RX_1_Denied <> No Then
' Set control colors for missing info
HasError = True
End If

If HasError Then
Exit Sub
End If
' Set control colors for info ok
'====================
' Do expected stuff here
'------------------------------

' is the same as

'------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = CheckControls()
End Sub

Private Sub Form_Current()
Dim Result As Boolean
Result = CheckControls()
End Sub

Private Sub RX_1_Drug_Exit(Cancel As Integer)
Cancel = CheckControls()
End Sub

Private Sub RX_1_Extra_Refills_Exit(Cancel As Integer)
Cancel = CheckControls()
End Sub
Private Sub RX_1_Denied_Exit(Cancel As Integer)
Cancel = CheckControls()
End Sub
Private Function CheckControls() As Boolean
Dim Result As Boolean ' called HasError before
Result = True

' Set control colors for info ok

If (RX_1_Drug & "" = "") Then
' Set control colors for missing info
Result = False
End If

If (RX_1_Extra_Refills & "" <> "") Then

' Set control colors for missing info
Result = False
End If

If RX_1_Denied <> No Then
' Set control colors for missing info
Result = False
End If

CheckControls = Result
End Function
'------------------------------

Note: MsgBox() is like a web popup and should be used sparingly.

Regards John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is there a better way? 7
IF function says too many nested 1
Nested Tab Controls - Bring to Front 9
Nested Subforms - Intermediate Table 0
Nested If 5
Nested if then else statement 3
Nested If problem 15
If Statements 2

Back
Top