Elegant If(Or...) Solution

[SpaceEd]

Hi,

I have 5 categories that need to be ranked from 1-5 with an A, B, C, or D attached. For example, Crit1 could be ranked 1A, or 1B, or 1C, and so on until 5D. However, there are fields later in the form that I want to blank out unless any of the 5 categories is in the 1 or a 2 rankings.

I have a very long If/or statement, but I was wondering if anyone had a more elegant solution. I've played around with instr statements, but it's seemed to be a bit buggy and not work. Could anybody provide any suggestions? I'd appreciate any help.

Here is my code so far:

If ([Crit1] = "1A" Or [Crit1] = "1B" Or [Crit1] = "1C" Or [Crit1] = "1D" Or [Crit2] = "1A" Or [Crit2] = "1B" Or [Crit2] = "1C" Or [Crit 2] = "1D" .............) Then
Me.Field1.Enabled = True
Me.Field2.Enabled = True
Me.lblDetermineIfNeeded1.Visible = False
Else
Me.Field1.Enabled = False
Me.Field2.Enabled = False
End If

 

[Alansidman]

Consider using a Case Select Statement

Here is some info on that. It will result in a cleaner VBA

http://www.techonthenet.com/access/functions/advanced/case.php

Alan

 

[SpaceEd]

Thanks Alan! That worked great! But is there a way to run several cases at 1 time? In other words, is there a way for it to check if any of the criteria have a 1 or 2 in it, then disable? ie. Select Case [Crit1] [Crit2] Crit3]


Right now, I have this, but could I change the top line to do this? Or will i just need to copy paste for all the criteria?

Select Case [Crit1]
Case "1A", "1B", "1C", "1D", "2A", "2B", "2C", "2D"
Me.Field1.Enabled = True
Me.Field2.Enabled = True
Me.lblDetermineIfNeeded1.Visible = False
Me.lblDetermineIfNeeded1.BackTint = 100
Case Else
lngBlue = RGB(31, 73, 125)
Me.Field1.Enabled = False
Me.Field2.Enabled = False
Me.lblDetermineIfNeeded1.Visible = True
Me.lblDetermineIfNeeded1.BackColor = lngBlue
Me.lblDetermineIfNeeded1.BackTint = 75
End Select

 

[Simon_MT]

You could actually link the table with your rankings into this Form using a Query and add a few of fields, let the data decide what to do. Besides it is very flexible.

F1_Enabled (boolean)
F2_Enabled (boolean)
If_Needed_Visible (boolean)
If_Needed_BackTint (integer)

Simon

 

[nanscombe]

How about something like ...

dim has1or2 as Boolean ' Place me at the top of the code

has1or2 = FALSE
has1or2 = has1or2 OR (left([Crit1], 1) = "1")
has1or2 = has1or2 OR (left([Crit1], 1) = "2")
has1or2 = has1or2 OR (left([Crit2], 1) = "1")
has1or2 = has1or2 OR (left([Crit2], 1) = "2")
has1or2 = has1or2 OR (left([Crit3], 1) = "1")
has1or2 = has1or2 OR (left([Crit3], 1) = "2")
has1or2 = has1or2 OR (left([Crit4], 1) = "1")
has1or2 = has1or2 OR (left([Crit4], 1) = "2")
has1or2 = has1or2 OR (left([Crit5], 1) = "1")
has1or2 = has1or2 OR (left([Crit5], 1) = "2")

' The Boolean variable [B]has1or2[/B] will only become true if any of the above conditions are met

if has1or2 Then
  Me.Field1.Enabled = True
  Me.Field2.Enabled = True
  Me.lblDetermineIfNeeded1.Visible = False
  Me.lblDetermineIfNeeded1.BackTint = 100
Else
  lngBlue = RGB(31, 73, 125)
  Me.Field1.Enabled = False
  Me.Field2.Enabled = False
  Me.lblDetermineIfNeeded1.Visible = True
  Me.lblDetermineIfNeeded1.BackColor = lngBlue
  Me.lblDetermineIfNeeded1.BackTint = 75
End If

Or a more compact form ...

dim strAllCrit as String ' Place me at the top of the code

strAllCrit = [Crit1] & [Crit2] & [Crit3] & [Crit4] & [Crit5]

if (instr(strAllCrit, "1") > 0) OR (instr(strAllCrit, "2") > 0) Then
  Me.Field1.Enabled = True
  Me.Field2.Enabled = True
  Me.lblDetermineIfNeeded1.Visible = False
  Me.lblDetermineIfNeeded1.BackTint = 100
Else
  lngBlue = RGB(31, 73, 125)
  Me.Field1.Enabled = False
  Me.Field2.Enabled = False
  Me.lblDetermineIfNeeded1.Visible = True
  Me.lblDetermineIfNeeded1.BackColor = lngBlue
  Me.lblDetermineIfNeeded1.BackTint = 75
End If

 

[SpaceEd]

Thanks! I actually implemented a solution somewhat similar to that nanscombe. It seems to work great so far.

Thanks for all your responses