Solved

Problem with Case Select Statement

Posted on 2006-10-26
12
336 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

776 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