Solved

Can't get my Case Select to work properly

Posted on 2011-02-22
8
413 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 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