[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Problem with Case Select Statement

Posted on 2006-10-26
12
Medium Priority
?
355 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Accepted Solution

by:
yhwhlivesinme earned 1000 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
 

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

650 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