Select Case Help

[KatcDolly]

I have not worked with Select Case before, but I have a gnarly nested If..then..else statement. I needed to try to do something different. I am working in MS Access 2010.

Description:

I created a form that has several fields on it. A person selects a Visit type (1 or 2)and then How Late (1,2 or 3) based upon the selection in the option group, I need other objects to become visible in the form. I placed it on the AfterUpdate of the last field a person selects How Late.

I came up with this idea after looking at other examples on this site of Select Case. I could get my if then to work, but when I moved it into a Select Case then nothing works. Perhaps this is not the correct way to use Select Case. Any wisdom would be greatly appreciated.

Here is the Code:

Private Sub opgHowLate_AfterUpdate()
Select Case Me.opgVisitType.Value And Me.opgHowLate.Value

    Case (Me.opgVisitType = "1" And Me.opgHowLate = "1")
        Me.ckbFastTrackPaper.Visible = True
        Me.lblFastTrackPaper.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
    Case (Me.opgVisitType = "1" And Me.opgHowLate = "2")
        Me.ckbEvalShortTreat.Visible = True
        Me.lblEvalShortTreat.Visible = True
        Me.lblEvalOnly.Visible = True
        Me.ckbEvalOnly.Visible = True
        Me.lblReschedule.Visible = True
        Me.ckbReschedule.Visible = True
        Me.lblRescheduleWhen.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblRescheduleProvider.Visible = True
        Me.opgRescheduleProvider.Visible = True
        
     Case (Me.opgVisitType = "1" And Me.opgHowLate = "3")
        Me.lblEvalOnly.Visible = True
        Me.ckbEvalOnly.Visible = True
        Me.lblReschedule.Visible = True
        Me.ckbReschedule.Visible = True
        Me.lblRescheduleWhen.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblRescheduleProvider.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
    Case (Me.opgVisitType = "2" And Me.opgHowLate = "1")
        Me.lblShorterVisit.Visible = True
        Me.ckbShorterVisit.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
    Case (Me.opgVisitType = "2" And Me.opgHowLate = "2")
        Me.lblShorterVisit.Visible = True
        Me.ckbShorterVisit.Visible = True
        Me.lblReschedule.Visible = True
        Me.ckbReschedule.Visible = True
        Me.lblRescheduleWhen.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblRescheduleProvider.Visible = True
        Me.opgRescheduleProvider.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
     Case (Me.opgVisitType = "2" And Me.opgHowLate = "3")
        Me.lblShorterVisit.Visible = True
        Me.ckbShorterVisit.Visible = True
        Me.lblReschedule.Visible = True
        Me.ckbReschedule.Visible = True
        Me.lblRescheduleWhen.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblRescheduleProvider.Visible = True
        Me.opgRescheduleProvider.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
End Select
    
    
End Sub

[zmbd]

So let's start with the proper syntax
MS VBA Reference >Select Case Statement (read more?)

Select Case testexpression 
   [Case expressionlist-n
     [statements-n]] 
   [Case Else
     [elsestatements]]
End Select

The problem is going to be that the first match between the testexpression and one of the CASE conditions is going to be the condition executed and your test expression is most likely going to evaluate to 0, 1, or 2 which isn't going to match the CASE statement conditions unless the testexpression is zero because the CASE statements are going to evaluate to either true or false.

Worse, Access evaluates the True value as a negative one, (-1); thus, even when your expression evaluates to a 1 it will not match against the CASE=True conditions, only on the False which evaluates to zero and even that isn't assured with the way the logic is working here.

instead of trying to test for both conditions at the same time in the testexpression, as we're only looking for the true condition then modify your code to reflect this by only looking for when the CASE Condition is true by altering your line 2 to:

Private Sub opgHowLate_AfterUpdate()
 Select Case [B][U]True
[/U][/B]      Case (Me.opgVisitType = "1" And Me.opgHowLate = "1")
         Me.ckbFastTrackPaper.Visible = True
         Me.lblFastTrackPaper.Visible = True
         Me.lblNotes.Visible = True
         Me.txtNotes.Visible = True
      Case (Me.opgVisitType = "1" And Me.opgHowLate = "2")
         Me.ckbEvalShortTreat.Visible = True
         Me.lblEvalShortTreat.Visible = True
         Me.lblEvalOnly.Visible = True
         Me.ckbEvalOnly.Visible = True
         Me.lblReschedule.Visible = True
         Me.ckbReschedule.Visible = True
         Me.lblRescheduleWhen.Visible = True
         Me.opgRescheduleWhen.Visible = True
         Me.lblRescheduleProvider.Visible = True
         Me.opgRescheduleProvider.Visible = True
       Case (Me.opgVisitType = "1" And Me.opgHowLate = "3")
         Me.lblEvalOnly.Visible = True
         Me.ckbEvalOnly.Visible = True
         Me.lblReschedule.Visible = True
         Me.ckbReschedule.Visible = True
         Me.lblRescheduleWhen.Visible = True
         Me.opgRescheduleWhen.Visible = True
         Me.lblRescheduleProvider.Visible = True
         Me.opgRescheduleWhen.Visible = True
         Me.lblNotes.Visible = True
         Me.txtNotes.Visible = True
      Case (Me.opgVisitType = "2" And Me.opgHowLate = "1")
         Me.lblShorterVisit.Visible = True
         Me.ckbShorterVisit.Visible = True
         Me.lblNotes.Visible = True
         Me.txtNotes.Visible = True
      Case (Me.opgVisitType = "2" And Me.opgHowLate = "2")
         Me.lblShorterVisit.Visible = True
         Me.ckbShorterVisit.Visible = True
         Me.lblReschedule.Visible = True
         Me.ckbReschedule.Visible = True
         Me.lblRescheduleWhen.Visible = True
         Me.opgRescheduleWhen.Visible = True
         Me.lblRescheduleProvider.Visible = True
         Me.opgRescheduleProvider.Visible = True
         Me.lblNotes.Visible = True
         Me.txtNotes.Visible = True
       Case (Me.opgVisitType = "2" And Me.opgHowLate = "3")
         Me.lblShorterVisit.Visible = True
         Me.ckbShorterVisit.Visible = True
         Me.lblReschedule.Visible = True
         Me.ckbReschedule.Visible = True
         Me.lblRescheduleWhen.Visible = True
         Me.opgRescheduleWhen.Visible = True
         Me.lblRescheduleProvider.Visible = True
         Me.opgRescheduleProvider.Visible = True
         Me.lblNotes.Visible = True
         Me.txtNotes.Visible = True
  End Select
  Me.Repaint
End Sub

My other thought is a nested select

Select Case Me.opgVisitType.Value
    Case 1
        Select Case Me.opgHowLate
        Case 1
            'Action 1
        Case 2
            'Action 2
        Case 3
            'Action 3
        Case Else
            'Action 4
        End Select
    Case 2
        Select Case Me.opgHowLate
        Case 1
            'Action 5
        Case 2
            'Action 6
        Case 3
            'Action 7
        Case Else
            'Action 8
        End Select
    Case Else
            'Action 9
End Select

Here I have a CASE ELSE so there are three more actions than your original code; however, these are optional so omitting them would reduce the actions from nine to six as in your original code.

another option might be:

[KatcDolly]

Hi zmdb

I decided to return to using the If...then statements. From what I understand Case is not the best use for this. Here is what I am doing. I will try again and see if I can make better sense for you.

I am capturing data for people who are late. I am trying to make the form as easy at possible for data entry.

Here are the fields on my form:
IDNum
DateofSvc
Location
VisitType (This is a button 1 = Eval, 2 = Follow up)
HowLate (This is a button 1=10 min, 2=11to20, 3= >20)

Now based upon what a person selects, I need certain info. So my plan was to have those questions appear in the lower part of the form. So behind the HowLate afterUpdate() I put this if then. I had it working before trying the Case stuff and now it is not. Can you help me with this?

'Selects for Evaluation and 10 min late
   If Me.opgVisitType = "1" And Me.opgHowLate = "1" Then
        Me.lblFastTrackPaper.Visible = True
        Me.ckbFastTrackPaper.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
        Else
            Me.lblFastTrackPaper.Visible = False
            Me.ckbFastTrackPaper.Visible = False
            Me.ckbFastTrackPaper.Value = Null
            Me.lblNotes.Visible = False
            Me.txtNotes.Visible = False
            Me.txtNotes.Value = Null
        
    End If
    
'Selects for Evaluation and 11 to 20 min late.
    If Me.opgVisitType = "1" And Me.opgHowLate = "2" Then
        Me.ckbEvalShortTreat.Visible = True
        Me.lblEvalShortTreat.Visible = True
        Me.lblEvalOnly.Visible = True
        Me.ckbEvalOnly.Visible = True
        Me.lblReschedule.Visible = True
        Me.ckbReschedule.Visible = True
        Me.lblRescheduleWhen.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblRescheduleProvider.Visible = True
        Me.opgRescheduleProvider.Visible = True
        
        Else
            Me.lblEvalShortTreat.Visible = False
            Me.ckbEvalShortTreat.Visible = False
            Me.ckbEvalShortTreat.Value = Null
            Me.lblEvalOnly.Visible = False
            Me.ckbEvalOnly.Visible = False
            Me.ckbEvalOnly.Value = Null
            Me.lblReschedule.Visible = False
            Me.ckbReschedule.Visible = False
            Me.ckbReschedule.Value = Null
            Me.lblRescheduleWhen.Visible = False
            Me.opgRescheduleWhen.Visible = False
            Me.opgRescheduleWhen.Value = Null
            Me.lblRescheduleProvider.Visible = False
            Me.opgRescheduleProvider.Visible = False
            Me.opgRescheduleProvider.Value = Null
            
    End If
    
'Selects for Evaluation and 21 or more min late
    If Me.opgVisitType = "1" And Me.opgHowLate = "3" Then
        Me.lblEvalOnly.Visible = True
        Me.ckbEvalOnly.Visible = True
        Me.lblReschedule.Visible = True
        Me.ckbReschedule.Visible = True
        Me.lblRescheduleWhen.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblRescheduleProvider.Visible = True
        Me.opgRescheduleProvider.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
        Else
            Me.lblEvalOnly.Visible = False
            Me.ckbEvalOnly.Visible = False
            Me.ckbEvalOnly.Value = Null
            Me.lblReschedule.Visible = False
            Me.ckbReschedule.Visible = False
            Me.ckbReschedule.Value = Null
            Me.lblRescheduleWhen.Visible = False
            Me.opgRescheduleWhen.Visible = False
            Me.opgRescheduleWhen.Value = Null
            Me.lblRescheduleProvider.Visible = False
            Me.opgRescheduleProvider.Visible = False
            Me.opgRescheduleProvider.Value = Null
            Me.lblNotes.Visible = False
            Me.txtNotes.Visible = False
            Me.txtNotes.Value = Null
    
    End If
    
    
'Selects for Follow-up and 10 min late
    If Me.opgVisitType = "2" And Me.opgHowLate = "1" Then
        Me.lblShorterVisit.Visible = True
        Me.ckbShorterVisit.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
        Else
            Me.lblShorterVisit.Visible = False
            Me.ckbShorterVisit.Visible = False
            Me.ckbShorterVisit.Value = Null
            Me.lblNotes.Visible = False
            Me.txtNotes.Visible = False
            Me.txtNotes.Value = Null
    End If
    
    
'Selects for Follow-up and 11 to 20 min late
    If Me.opgVisitType = "2" And Me.opgHowLate = "2" Then
        Me.lblShorterVisit.Visible = True
        Me.ckbShorterVisit.Visible = True
        Me.lblReschedule.Visible = True
        Me.ckbReschedule.Visible = True
        Me.lblRescheduleWhen.Visible = True
        Me.opgRescheduleWhen.Visible = True
        Me.lblRescheduleProvider.Visible = True
        Me.opgRescheduleProvider.Visible = True
        Me.lblNotes.Visible = True
        Me.txtNotes.Visible = True
        
        Else
            Me.lblShorterVisit.Visible = False
            Me.ckbShorterVisit.Visible = False
            Me.ckbShorterVisit.Value = Null
            Me.lblReschedule.Visible = False
            Me.ckbReschedule.Visible = False
            Me.ckbReschedule.Value = Null
            Me.lblRescheduleWhen.Visible = False
            Me.opgRescheduleWhen.Visible = False
            Me.opgRescheduleWhen.Value = Null
            Me.lblRescheduleProvider.Visible = False
            Me.opgRescheduleProvider.Visible = False
            Me.opgRescheduleProvider.Value = Null
            Me.lblNotes.Visible = False
            Me.txtNotes.Visible = False
            Me.txtNotes.Value = Null
    
    End If

Thank you!

[zmbd]

+ Did you try the revised code I posted?
It is a very simple change.
Select Case Me.opgVisitType .Value And Me.opgHowLate.V alue

Goes to

Select Case True

The select-case will work just fine, one simply needs to follow the logic. In your case, you are actually testing to see when a given set of conditions are true.

+ If you want to switch to the "nested if-then," personally I wouldn't (notice, using the if-then you have some 130ish lines of code whereas with the select-case you have around 50ish), then please start a new thread... we try to keep each thread to a single topic.

[KatcDolly]

I did try it. So the case works. But does not turn off the fields. I have a sampe to send but do not know how to attached it. Can you tell me how to attach a zipped access db?

[zmbd]

See if inserting a Me.Repaint after the select case, between lines 57/58, of the revised code in my previous post. I've updated that post to reflect this tweak.

Sorry missed that little thing, :) , forms like to be "static" in nature and sometimes require a push to get things to update when one changes the visibility of the controls.

[KatcDolly]

repaint did not work entirely. When I move to a new record the screen does not go back to having nothing showing. It will work once I select for the next record.

Oh how I wish I could send you a sample db. I think we are very close.

[PhilOfWalton]

I think zmbd's solution with the nested select is spot on, but it should be in a sub of it's own and called on the AfterUpdate of opgHowLate or opgVisitType, and possibly for good luck on the OnCurrent of the form.

Phil

[KatcDolly]

Added it to the onCurrent. Makes complete sense.

However, my repaint is still not clearing out the form when I move to a new record.

Fickle form, any ideas?

[zmbd]

We need to start a new thread if the question regarding how the select-case has been answered.

When you start the new thread you can insert a link back to this thread so that there's proper context :)

-Z