Variables (1 Viewer)

corinereyes

New member
Local time
Today, 23:47
Joined
Mar 3, 2013
Messages
12
Code:
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

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
14,047
Does that even compile?

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

Code:
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

AWF VIP
Local time
Today, 12:47
Joined
Mar 9, 2014
Messages
5,423
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
Code:
If cmb = "Medical" Then
    Select Case Me.Frame112 & Me.Frame134
        Case 11
            DoCmd.OpenReport "Medical", acViewReport
    End Select
End If
 
Last edited:

corinereyes

New member
Local time
Today, 23:47
Joined
Mar 3, 2013
Messages
12
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 :(

Code:
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

New member
Local time
Today, 23:47
Joined
Mar 3, 2013
Messages
12
Hello Guys!,

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

and refer to button 1 as

Code:
  Case 1&

Thank you for all your help!
 

Minty

AWF VIP
Local time
Today, 20:47
Joined
Jul 26, 2013
Messages
10,355
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;

Code:
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

AWF VIP
Local time
Today, 12:47
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 20:47
Joined
Sep 12, 2006
Messages
15,614
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.

Code:
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
there is also an alternative case:
Code:
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

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
14,047
arnelgp,

Is that meant to be

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:47
Joined
May 7, 2009
Messages
19,169
you got me, there.
 

Gasman

Enthusiastic Amateur
Local time
Today, 20:47
Joined
Sep 21, 2011
Messages
14,047
Took me a while, I was wondering what the significance of the 134 was. :D
 

corinereyes

New member
Local time
Today, 23:47
Joined
Mar 3, 2013
Messages
12
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;

Code:
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

New member
Local time
Today, 23:47
Joined
Mar 3, 2013
Messages
12
[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. :)
 
Last edited:

Users who are viewing this thread

Top Bottom