Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Can't get my Case Select to work properly

Posted on 2011-02-22
8
Medium Priority
?
418 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 2000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

564 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