Solved

Problem with Case Select Statement

Posted on 2006-10-26
12
334 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

920 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

12 Experts available now in Live!

Get 1:1 Help Now