Solved

Can't get my Case Select to work properly

Posted on 2011-02-22
8
403 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
 

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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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
 

Author Closing Comment

by:gdunn59
ID: 34965639
Thanks!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
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…
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…

747 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

13 Experts available now in Live!

Get 1:1 Help Now