Solved

Can't get my Case Select to work properly

Posted on 2011-02-22
8
408 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
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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