Solved

Can't get my Case Select to work properly

Posted on 2011-02-22
8
414 Views
Last Modified: 2012-05-11
I have the following Select Case statement (see below in the Code box), but I can't seem to get it to work properly.

When I choose the Region "East" from the field cboRegion on the main form (frmEmployee_Audits"), it gives me a specific list for the dropdown (cboQualRevCriteria) on subform (frmQuality_Review_Subform) - this part is working fine.

Depending on what I choose from the dropdown (cboQualRevCriteria) on the subform,  there is a field (txtSection) on the subform (frmQuality_Review_Subform) that populates with a specific item based off of what was chosen from the dropdown (cboQualRevCriteria) also on the subform.

It is not working that way.  Its not populating the txtSection field on the subform at all.

What am I doing wrong?

Thanks,
gdunn59


Private Sub cboQualRevCriteria_Change()

'Central
  Select Case Central
    Case Forms!frmEmployee_Audits.Form.cboRegion = "Central" And [cboQualRevCriteria] Like "1.*"
        txtSection = "New Enrollment Set-up / Member Enrollment"
    Case [cboQualRevCriteria] Like "2.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Member Disenrollment"
    Case [cboQualRevCriteria] Like "3.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Member Plan Change"
    Case [cboQualRevCriteria] Like "4.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Documentation - Disenrollment"
    Case [cboQualRevCriteria] Like "5.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Address/OOA (out of area)"
    Case [cboQualRevCriteria] Like "6.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Maintenance"
    Case [cboQualRevCriteria] Like "7.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Member Touch Point"
    Case [cboQualRevCriteria] Like "8.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Member Reinstatement"
    Case [cboQualRevCriteria] Like "9.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Status Reports"
    Case [cboQualRevCriteria] Like "10.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "LEP"
    Case [cboQualRevCriteria] Like "12.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "BAE"
    Case [cboQualRevCriteria] Like "13.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "EFT forms East only"
    Case [cboQualRevCriteria] Like "14.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Completeness and Accuracy for Disenrollment"
    Case [cboQualRevCriteria] Like "15.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Completeness and Accuracy - All other with Exceptions"
    Case [cboQualRevCriteria] Like "16.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Completeness and Accuracy - All other with Exceptions"
    Case [cboQualRevCriteria] Like "17.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Documentation - BAE"
    Case [cboQualRevCriteria] Like "18.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Completeness and Accuracy - All other with Exceptions"
    Case [cboQualRevCriteria] Like "19.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Documentation - Status Reports"
    Case [cboQualRevCriteria] Like "20.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Completeness and Accuracy"
    Case [cboQualRevCriteria] Like "21.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "COB"
    Case Else
        MsgBox "Section Not Found"
End Select

'West
  Select Case West
    Case [cboQualRevCriteria] Like "1.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Welcome kit requested"
    Case [cboQualRevCriteria] Like "2.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "New Enrollment Set-up / Member Enrollment"
    Case [cboQualRevCriteria] Like "3.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Member Disenrollment"
    Case [cboQualRevCriteria] Like "4.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Member Plan Change"
    Case [cboQualRevCriteria] Like "5.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Completeness and Accuracy  (All others except enrollment & plan changes)"
    Case [cboQualRevCriteria] Like "6.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Documentation"
    Case [cboQualRevCriteria] Like "7.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Member Reinstatements"
    Case [cboQualRevCriteria] Like "8.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Address / OOA"
    Case [cboQualRevCriteria] Like "9.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Maintenance"
    Case [cboQualRevCriteria] Like "10.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "ACS"
    Case [cboQualRevCriteria] Like "11.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Member Touch Point"
    Case [cboQualRevCriteria] Like "12.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "POA"
    Case [cboQualRevCriteria] Like "14.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Completeness & Accuracy (POA)"
    Case [cboQualRevCriteria] Like "15.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "COB Processing"
    Case Else
            MsgBox "Section Not Found"
End Select

End Sub

Open in new window

0
Comment
Question by:gdunn59
[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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 8

Expert Comment

by:crysallus
ID: 34957170
Is this all your code?

You've only got case statements which handle central and west listed here, not east, so if selecting east nothing will happen. You'd need to add another select case statement block to handle east as well.
0
 
LVL 1

Author Comment

by:gdunn59
ID: 34957328
Yes this is all I have right now.  I understand that East isn't there, but it is not working if I choose Central or West either.  Actually if I choose Central it gives me the options for the txtSection field that it should be giving me if I choose West.
0
 
LVL 8

Accepted Solution

by:
crysallus earned 500 total points
ID: 34957381
You'd probably be better off just with a nested if..else structure.

If Forms!frmEmployee_Audits.Form.cboRegion = "Central" Then

    If [cboQualRevCriteria] Like "1.*" Then
        txtSection = "New Enrollment Set-up / Member Enrollment"
    Else If...
    ...
    End If

Else If Forms!frmEmployee_Audits.Form.cboRegion = "West" Then

    If [cboQualRevCriteria] Like "1.*" Then
    ...
    End If

Else If...

End If

Open in new window

I think that suits your logic better as you're testing values for true or false. A select case statement is used when an expression your testing can have multiple values of a basic data type, like different integer values, which is a little different to what you're trying to do here.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:gdunn59
ID: 34957390
Ok.  I started out with an If statement, but thought it would be better with a case statement.

I will give the If statement a try and let you know if it works out.

Thanks,
gdunn59
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 34957585
You can use the SELECT CASE statement like this
Private Sub cboQualRevCriteria_Change()

  Dim strQualRevCriteria as String
  strQualRevCriteria = Left([cboQualRevCriteria], InStr(1, [cboQualRevCriteria], "."))

  Select Case Forms!frmEmployee_Audits.Form.cboRegion
    Case "Central"
      'Central
       Select Case strQualRevCriteria
         Case "1."
           txtSection = "New Enrollment Set-up / Member Enrollment"
         Case "2."
           txtSection = "Member Disenrollment"
       End Select

    Case "west"
      'West
       Select Case strQualRevCriteria
         Case "1."
           txtSection = "Welcome kit requested"
         Case "2."
           txtSection = "New Enrollment Set-up / Member Enrollment"
       End Select

  End Select

End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 34963544
Forgive the longwinded nature of this replay, but there are a lot of things wrong with your code. Hopefully this will help....


You control structures in you code reduce to this…


  Select Case Central
    Case Forms!frmEmployee_Audits.Form.cboRegion = "Central" And [cboQualRevCriteria] Like "1.*"
        txtSection = "New Enrollment Set-up / Member Enrollment"
    Case [cboQualRevCriteria] Like "2.*" And Forms!frmEmployee_Audits.Form.cboRegion = "Central"
        txtSection = "Member Disenrollment"
            ' snip

    Case Else
        MsgBox "Section Not Found"
End Select

'West
  Select Case West
    Case [cboQualRevCriteria] Like "1.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "Welcome kit requested"
    Case [cboQualRevCriteria] Like "2.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
            txtSection = "New Enrollment Set-up / Member Enrollment"
' snip
    Case Else
            MsgBox "Section Not Found"
End Select

I’m actually surprised that the code compiles at all unless one of two circumstances are true:

1.      Your form has a control named ‘Central’ on it and also a control named ‘West’.
2.      Your code module does NOT include the statements “Option Explicit” at the beginning.

The reason I say this is that the two lines

  Select Case Central

And

  Select Case West

would simply not compile otherwise. My suspicion is that you are missing the Option Explicit statement which is a very bad thing, and so when the compiler hits the line “Select Case Central” it just creates a variable called Central – which is never used..

If appears that your intention is to take different actions depending of what the value of the combo cboRegion in which case that control needs to be referenced in the initial Selcec Case statement. That’s just how the Select thing works. So is should look like…

Select Case cboRegion
Case “Central” ‘ you are testing the text value of the combo

Case “West”

Case Else

End Select

That is your Outer Case statement. It says if the cboRegion value is “Central” then do stuff, otherwise if it is “West” then do other stuff, else if nothing is selected inform the user.

Then, inside each Case statement (Case “Central”, Case “West”) you need another nested Select Case construct.

As written your code says things like

Select Case Central
Case [cboQualRevCriteria] Like "1.*" And Forms!frmEmployee_Audits.Form.cboRegion = "West"
     txtSection = "Welcome kit requested"


First you need to open another Select Case block after the test for the region.

Second the region test should be
Select Case cboRegion

Third the “And Forms!frmEmployee_Audits.Form.cboRegion = "West"” is not required at all, as that test was already done in the outer Case Select statement.(as rewritten above).

Fourth using Like is Select Case statements does not seem to work, but this will.

Select Case Central  ‘ <- Outer Select Case
      Case “Central”
      Select Case Left(cboQualRevCriteria,Instr(cboQualRevCriteria,”.”)-1) ‘ <- Inner Select Case
            Case “1”
            Case “2”
            …
            Case “10”
            …
      End Select
      Case “West”
      Select Case Left(cboQualRevCriteria,Instr(cboQualRevCriteria,”.”)-1) ‘ <- Inner Select Case
            Case “1”
            Case “2”
            …
            Case “10”
      End Select
End Select

You can make that a little easier to read by grabbing the text up to the decimal point in the cbo and stuffing it into a variable and using that in later code….


Dim strCriteraPrefix as string

If instr(cboQualRevCriteria,”.”)> 0 then
strCriteraPrefix = Left(cboQualRevCriteria,Instr(cboQualRevCriteria,”.”)-1)
else
strCriteraPrefix = “”
End If

Select Case Central  ‘ <- Outer Select Case
      Case “Central”
      Select Case strCriteraPrefix ‘ <- Inner Select Case
            Case “1”
            txtSection = "New Enrollment Set-up / Member Enrollment"
            Case “2”
            txtSection = "Member Disenrollment"

            …
            Case “10”
            …
      End Select
      Case “West”
      Select Case strCriteraPrefix ‘ <- Inner Select Case
            Case “1”
            Case “2”
            …
            Case “10”
      End Select
End Select



0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 34963584
Sorry , correction. That last piece of code should be

Select Case cboRegion‘ <- Outer Select Case
      Case “Central”
      Select Case strCriteraPrefix ‘ <- Inner Select Case
            Case “1”
            txtSection = "New Enrollment Set-up / Member Enrollment"
            Case “2”
            txtSection = "Member Disenrollment"

            …
            Case “10”
            …
      End Select
      Case “West”
      Select Case strCriteraPrefix ‘ <- Inner Select Case
            Case “1”
            Case “2”
            …
            Case “10”
      End Select
End Select

0
 
LVL 1

Author Closing Comment

by:gdunn59
ID: 34965639
Thanks!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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 …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

726 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