Solved

Problem with Case Select Statement

Posted on 2006-10-26
12
333 Views
Last Modified: 2006-11-18
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
Comment
Question by:gfortuna
  • 7
  • 5
12 Comments
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17814164
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17814176
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
 

Author Comment

by:gfortuna
ID: 17814573
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
 
LVL 6

Accepted Solution

by:
yhwhlivesinme earned 250 total points
ID: 17814626
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
 

Author Comment

by:gfortuna
ID: 17814833
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17814876
What is the error/problem that you're getting?
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:gfortuna
ID: 17815067
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17815103
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
 

Author Comment

by:gfortuna
ID: 17815325
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17818577
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
 

Author Comment

by:gfortuna
ID: 17835318
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
 
LVL 6

Expert Comment

by:yhwhlivesinme
ID: 17835502
no problem, sorry about the confusion!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now