[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Problem with Case Select Statement

I'm new to using Case statements and can't figure out why this one isn't working.

I've tried it a couple ways, but below is the way I think it should work.  I want this statement to disable the Group Field and insert the value N/A if the field if the FuncIden = E and the TypeIden does not equal F.  It doesn't work right.  

The default value of Me.Group.Enabled is False.  If someone selects E and anything else but F I want the Group to become enabled.

Is the way I wrote this correct?


Private Sub tblGEFSAdd_TypeIden_AfterUpdate()
Select Case False
   Case Me.FuncIden.Value = "E" And Me.TypeIden.Value <> "F"
   Me.Group.Value = "N/A"
   Me.Group.Enabled = False
   Me.TitlebyType.Enabled = True
   Me.TitlebyType.SetFocus
Exit Sub

Case Else
  Me.Group.Enabled = True
  Me.Group.SetFocus
   
End Select

End Sub
0
gfortuna
Asked:
gfortuna
  • 7
  • 5
1 Solution
 
yhwhlivesinmeCommented:
I think this is what you want:

Private Sub tblGEFSAdd_TypeIden_AfterUpdate()
Select Case Me.FuncIden.Value = "E" And Me.TypeIden.Value <> "F"
   Me.Group.Value = "N/A"
   Me.Group.Enabled = False
   Me.TitlebyType.Enabled = True
   Me.TitlebyType.SetFocus
Exit Sub

Case Else
  Me.Group.Enabled = True
  Me.Group.SetFocus
End Select
End Sub
0
 
yhwhlivesinmeCommented:
Sorry a bit confused there....here is what you should want

Private Sub tblGEFSAdd_TypeIden_AfterUpdate()
Select Case Me.FuncIden.Value = "E" And Me.TypeIden.Value <> "F"
Case True
   Me.Group.Value = "N/A"
   Me.Group.Enabled = False
   Me.TitlebyType.Enabled = True
   Me.TitlebyType.SetFocus
Exit Sub

Case False
  Me.Group.Enabled = True
  Me.Group.SetFocus
End Select
End Sub
0
 
gfortunaAuthor Commented:
Thanks for your help.  That worked the way I wanted it to.
 
One last quick question: what if I want multiple criteria for one field in a Case Statement.

For Example the TypeIden in the following statement:

Select Case Me.FuncIden = "F" And Me.TypeIden <> "F" Or Me.TypeIden <> "P"
Case True
  Me.Group.Value = "N/A"
  Me.Group.Enabled = False
  Me.TitlebyType.Enabled = True
  Me.TitlebyType.SetFocus
 Exit Sub
Case False
  Me.Group.Enabled = True
  Me.Group.SetFocus
End Select
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
yhwhlivesinmeCommented:
it looks to me like you've got it right there, you might need to use parenthesis:

Select Case Me.FuncIden = "F" And (Me.TypeIden <> "F" Or Me.TypeIden <> "P")

but really this isn't how select case is supposed to be used, it's more used for something like this:

Select case me.lst10
Case 1
  Msgbox "you selected the first item in the listbox"
Case 2
  Msgbox "you selected the second item in the listbox"
Case 3
  Msgbox "you selected the third item in the listbox
Case Null
  Msgbox "you didn't select anything"
Case Else
  Msgbox "hrmmm looks like we don't know what you selected"
End Select

In your case we can just use if...then statements, so in your case it would be:
if Me.FuncIden = "F" And (Me.TypeIden <> "F" Or Me.TypeIden <> "P") then
  Me.Group.Value = "N/A"
  Me.Group.Enabled = False
  Me.TitlebyType.Enabled = True
  Me.TitlebyType.SetFocus
  Exit Sub
else
  Me.Group.Enabled = True
  Me.Group.SetFocus
End If
0
 
gfortunaAuthor Commented:
Okay, so maybe I should have asked this to begin with.  I'm sorry that I'm making this harder than it needs to be.

The following is the code all together.  I took out the Case and replaced it with a if then elseif statement.  It is not working correctly the first part works fine.  But the ElseIf does not work.

Sorry to make a pain out of this.  I appreciate your help.


Private Sub tblGEFSAdd_TypeIden_AfterUpdate()
If Me.FuncIden.Value = "E" And Me.TypeIden.Value <> "F" Then

  Me.Group.Value = "N/A"
  Me.Group.Enabled = False
  Me.TitlebyType.Enabled = True
  Me.TitlebyType.SetFocus
Exit Sub

ElseIf Me.FuncIden = "F" And (Me.TypeIden <> "F" Or Me.TypeIden <> "P") Then
  Me.Group.Value = "N/A"
  Me.Group.Enabled = False
  Me.TitlebyType.Enabled = True
  Me.TitlebyType.SetFocus
  Exit Sub


Else
 Me.Group.Enabled = True
 Me.Group.SetFocus
End If


End Sub
0
 
yhwhlivesinmeCommented:
What is the error/problem that you're getting?
0
 
gfortunaAuthor Commented:
No matter what I select for TypeIden, when FuncIdec is selected as F is keeps the Group Value as N/A and Enabled as False.  It does Enable and SetFocus to the TitleByType.
0
 
yhwhlivesinmeCommented:
What are your choices for typeIden?

before your if statement put:
msgbox "funciden" & me.funcIden & "  TypeIden:" & me.typeIden

let me know what pops up as those values.....
0
 
gfortunaAuthor Commented:
Had a meeting to go to, sorry.

My Choices are FuncIden: E F And G (Engineering / Facilities / Guideine)

TypeIden: F G P R (Form / Guideline / Procedure / Requirement)

When I choose Engineering and Form The message box states funcidenE TypeIden:F
0
 
yhwhlivesinmeCommented:
Those choices don't validate your if or your elseif statements, so your else statement should be executed.  is your group enabled? if not put a msgbox in there:

....
Else
 msgbox "Else Statements Executed"
 Me.Group.Enabled = True
 Me.Group.SetFocus
End If

and let me know if those get executed
0
 
gfortunaAuthor Commented:
For some reason it's working correctly now. I think I had an end if statement in the wrong place.  I guess I just needed the weekend to get away from it.  

Thanks for all your help.
0
 
yhwhlivesinmeCommented:
no problem, sorry about the confusion!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now