Is there a better way of doing an IF (with multiple ors)??

[newbyvba]

I’m popping a bit of code behind a combo box called comboobjective on a form. If the user chooses certain objectives numbers, I want one text box to become visible, but if they pick any one of another list of values, then the other text becomes visible.

The IF does it (as I’ve put below) but I’m sure there must be a better way to achieve this. I’ve been looking at Case but not really sure… I’ve still a lot to learn. Can you point met in the right direction please?

Private Sub Comboobjective_AfterUpdate()
'tests drop down list for values and makes the appropriate object visible

If Me.Comboobjective = 9 Or Me.Comboobjective = 10 Then
MsgBox ("Please select the TFC check box and enter a TFC reason.")

If Me.Comboobjective = 1 Or Me.Comboobjective = 2 Or Me.Comboobjective = 3 Or Me.Comboobjective = 5 Or Me.Comboobjective = 8 Then
Me.term_text.Visible = True
Else
If Me.Comboobjective = 4 Or Me.Comboobjective = 6 Or Me.Comboobjective = 7 Or Me.Comboobjective = 9 Or Me.Comboobjective - 10 Then
Me.Retirement_Age.Visible = True
End If
End If
End If
End Sub

 

[Andrzejek]

Yes, select case would be nicer:

Me.term_text.Visible = False
Me.Retirement_Age.Visible = False

Select Case Me.Comboobjective 
    Case 9, 10
        MsgBox ("Please select the TFC check box and enter a TFC reason.")
    Case 1, 2, 3, 5, 8
        Me.term_text.Visible = True
    Case 4, 6, 7, 9, 10
        Me.Retirement_Age.Visible = True
    Case Else
        ....
End Select

Have fun.

---- Andy

 

[newbyvba]

Wow! that simple... thank you, you make it look so easy. I wish the book I was reading had!

May I ask what the Case Else .... means

 

[newbyvba]

Ah, I see I need a Case else if others not met!
The case isn't quite working for the 9, 10. I do get the message box but I also want to make the term visible. I tried the following but it didn't like it! You can't have 2 results for the condition set then?


Private Sub Comboobjective_AfterUpdate()
Me.term_text.Visible = False
Me.Retirement_Age.Visible = False

Select Case Me.Comboobjective
Case 9, 10
MsgBox ("Please select the TFC check box and enter a TFC reason.")
Me.Retirement_Age.Visible
Case 1, 2, 3, 5, 8
Me.term_text.Visible = True
MsgBox "Please enter term information"
Case 4, 6, 7, 9, 10
Me.Retirement_Age.Visible = True
MsgBox "Please enter retirement age"
Case Else
MsgBox ("Choose an objective")
End Select

End Sub

 

[MajP]

A select case short circuits so you can not have two conditions. So

Case 9, 10
MsgBox ("Please select the TFC check box and enter a TFC reason.")
Me.Retirement_Age.Visible = True
Case 1, 2, 3, 5, 8
Me.term_text.Visible = True
Case 4, 6, 7
Me.Retirement_Age.Visible = True
Case Else

 

[Andrzejek]

I tried the following but it didn't like it!

Maybe because you forgot:

Select Case Me.Comboobjective
    Case 9, 10
        MsgBox ("Please select the TFC check box and enter a TFC reason.")
        Me.Retirement_Age.Visible [red]= True[/red]

Have fun.

---- Andy

 

[TheAceMan1]

[blue]The case isn't quite working for the 9, 10. I do get the message box but I also want to make the [purple]term[/purple] visible.[/blue]

Well ... in [blue]Case 9,10[/blue] your making [blue].Retirement_Age[/blue] visible!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

IMO, there should be something in the Row Source table that identifies how to handle the values. I don't care to see hard-coded values like 9, 10 vs 1,2,3,5,8 vs 4,6,7,9,10. If there is a difference in how you treat these values, it should be stored in a table, not stored in your code.

Duane
Hook'D on Access
MS Access MVP