Help, need to make code simple

[David Ehrenreich]

I have this code I made that checks a feild and if the
feild contains certain text it will enter data into
another feild automatically. The way I did it seems to
big, so if someone can help me do the same thing, but with
less code.

Here Goes
Private Sub FinishedTest_Change()
If (FinishedTest) = "none" Then
Me.Mail = "none"
LoginProctor.SetFocus

Else
If (FinishedTest) = "Professor Pickup" Then
Me.Mail = "PU"
LoginProctor.SetFocus

Else
If (FinishedTest) = "File Test and Mail Answer"
Then
Mail.SetFocus

Else
If (FinishedTest) = "Mail Test and Answer"
Then
If (Course) = "bpa254" Then
Me.Mail = "Crrs 205"

Else

If (Course) = "bpa111" Then
Me.Mail = "Crrs 205"

Else

If (Course) = "bpa142" Then
Me.Mail = "Crrs 205"
Mail.SetFocus

Else

If (Course) = "bpa162" Then
Me.Mail = "Crrs 205"

Else

If (Course) = "eng111" Then
Me.Mail = "Hum 102"

Else

If (Course) = "csi113" Then
Me.Mail = "Crrs 224"

Else

If (Course) = "his111" Then
Me.Mail = "Crrs 317"

Else

If (Course) = "his211" Then
Me.Mail = "Crrs 317"

Else

If (Course) = "phs109" Then
Me.Mail = "Drgn 238"

Else

If (Course) = "mat011" Then
Me.Mail = "Math"

Else

If (Course) = "bpa010" Then
Me.Mail = "Math"

Else

If (Course) = "bpa012" Then
Me.Mail = "Math"

Else

If (Course) = "hea111" Then
Me.Mail = "PE 208"

Else

If (Course) = "hea114" Then
Me.Mail = "PE 208"

Else

If (Course) = "hea150" Then
Me.Mail = "PE 208"

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

 

[Allen Browne]

See help on:
Select Case

 

[Guest]

Hi there,

Look at the "Select Case" bit in help - that should help!

 

[David Ehrenreich]

Hello,

Thank you for the hint. My question now is, is in the
help it states that a case statement is an alternative to
using ElseIf in If...Then...Else statements, so is their
any bennifit to what I already have? Also, does having
long expressions slow down the forms?

Thank you for your help

David Ehrenreich

 

[John Spencer (MVP)]

Well one thing you could do would be to use a case statement.

Private Sub FinishedTest_Change()

Select Case FinishedTest
Case "None"
me.Mail = "None"
LoginProctor.SetFocus
Case "Professor Pickup"
me.Mail = "PU"
LoginProctor.SetFocus
Case "File Test and Mail Answer"
Mail.SetFocus
Case "Mail Test and Answer"
Select Case Course
Case "BPA254","BPA111", "BPA142", "BPA162"
me.Mail = "Crrs 205"
Case "ENG111"
me.Mail = "Hum 102"
Case "His111", "His211"
Me.Mail = "Crrs 317"
Case ...
...
End Select 'Course

End Select 'Finished Test

Exit Sub

Of course, you might be better off having a table with the translations and just
using that for all but your exceptions.

 

[Guest]

Better still, build lookup table to map courses to mail-
stops.

 

[Allen Browne]

Run some timing tests on your code if you wish.
Select Case is so much easier to code and maintain.
It's a little less flexible, e.g. it doesn't handle multiple selection
options.