Variables

[corinereyes]

Dim cmb As String
Dim evaltype() As Variant
Dim f1 As Integer
Dim f2 As Integer

cmb = cmbContractType.Text

f1 = Me.Frame112.Value
f2 = Me.Frame134.Value

If cmb = "Medical" Then
    Select Case evaltype(f1, f2)
        Case evaltype(1, 1)
        DoCmd.OpenReport "Medical", acViewReport
    End Select
End If

Hello Everyone!

I'm trying to get the values in frame 1 and frame 2 (they are group of option buttons separated by frames). I have used select case statements because they will be a lot of scenarios. Basically if a user selects option button 1 (frame1) and option 1 (frame2) a report will be displayed. However i can/t get it work. Type mismtach or subscript out of Range .

Any shed of light will help me a lot. Thank you.

 

[Gasman]

Does that even compile?

I would have coded it like this, which I have just tested and works?

Select Case Me.Frame10 And Me.Frame19
    Case 1 And 1
        Debug.Print "1and1"
    Case 1 And 2
        Debug.Print "1and2"
End Select

HTH

 

[June7]

Apparently it compiles if getting subscript out of range error. However, array is declared but not populated nor referenced properly.

How many combinations of choices will there be?

Can also do something like

If cmb = "Medical" Then
    Select Case Me.Frame112 & Me.Frame134
        Case 11
            DoCmd.OpenReport "Medical", acViewReport
    End Select
End If

 

[corinereyes]

Hello June and Gasman,

Thank you for your reply.

They are both working fine. However , there are instances that the user will not select 2 option buttons rather 1 option button only. Like

There are about 10 combinations

Private Sub cmbContractType_Change()
Dim cmb As String

cmb = cmbContractType.Text

If cmb = "Medical" Then
    
    Select Case Me.Frame112 And Me.Frame134
        
        Case 1 And Null
        If MsgBox("Do you want to open Medical EPE forms?", vbOKCancel, "Print Evaluation Forms") = vbOK Then
            DoCmd.OpenReport "Medical", acViewReport
        End If
        
        Case 1 And 1
        If MsgBox("Do you want to open Medical Category 1000 EPE forms?", vbOKCancel, "Print Evaluation Forms") = vbOK Then
            DoCmd.OpenReport "Medical 1000", acViewReport
        End If
    End Select
End If
End Sub

However , how to reference option value that is null? It is returning the wrong report.

 

[corinereyes]

Hello Guys!,

I got it! You just have to use & rather than And

and refer to button 1 as

  Case 1&

Thank you for all your help!

 

[Minty]

Comparing anything to Null is not going to give you the result you think it will ;
? 1 and Null
Null

I think I would substitute a value for the null and use that in your case statement;

Select Case Nz(Me.frame112,0) And Nz(Me.Frame134,0) 
 
     Case 1 And 0 etc, etc

Edit - Pretty sure your solution won't work as you are now concatenating strings.

 

[June7]

1 & Null will return 1

That would be Case 1 in the Select Case

But Null & 1 would be the same so maybe this won't be correct way to go.

 

[gemma-the-husky]

I would nest the case statements You aren't going to have to do this very often.
If there's another syntax that works, then fine, but this layout is easy to deal.

select case frame1
case 1:
      select case frame2
      case 1:
      case 2:
      end select
     
case 2:
      select case frame2
      case 1:
      case 2:
      end select

case 3:
      select case frame2
      case 1:
      case 2:
      end select
end select

 

[arnelgp]

there is also an alternative case:

Private Sub cmbContractType_Change()
Dim cmb As String

cmb = cmbContractType.Text

If cmb = "Medical" Then
    
    Select Case True
        
        Case Nz(Me.Frame112, 0) = 1 And Nz(Me.Frame, 134) = 0
        If MsgBox("Do you want to open Medical EPE forms?", vbOKCancel, "Print Evaluation Forms") = vbOK Then
            DoCmd.OpenReport "Medical", acViewReport
        End If
        
        Case Nz(Me.Frame112, 0) = 1 And Nz(Me.Frame, 134) = 1
        If MsgBox("Do you want to open Medical Category 1000 EPE forms?", vbOKCancel, "Print Evaluation Forms") = vbOK Then
            DoCmd.OpenReport "Medical 1000", acViewReport
        End If
    End Select
End If
End Sub

 

[Gasman]

arnelgp,

Is that meant to be

Case Nz(Me.Frame112, 0) = 1 And Nz(Me.Frame134, 0) = 0

 

[arnelgp]

you got me, there.

 

[Gasman]

Took me a while, I was wondering what the significance of the 134 was.

 

[corinereyes]

Comparing anything to Null is not going to give you the result you think it will ;
? 1 and Null
Null

I think I would substitute a value for the null and use that in your case statement;

Select Case Nz(Me.frame112,0) And Nz(Me.Frame134,0) 
 
     Case 1 And 0 etc, etc

Edit - Pretty sure your solution won't work as you are now concatenating strings.

A good suggest. Thank you.

 

[corinereyes]

[SOLVED] Re: Variables

Hi All,

Thank you all for your suggestions. Solves my problem! You are all been very helpful. Anyhow i'm still at around 50% of my project so please expect me to post again.