Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-15-2019, 12:22 AM   #1
corinereyes
Newly Registered User
 
Join Date: Mar 2013
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
corinereyes is on a distinguished road
Variables

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.

corinereyes is offline   Reply With Quote
Old 07-15-2019, 01:08 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,554
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Variables

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-15-2019, 01:26 AM   #3
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,473
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Variables

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

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 07-15-2019 at 01:33 AM.
June7 is offline   Reply With Quote
Old 07-15-2019, 02:40 AM   #4
corinereyes
Newly Registered User
 
Join Date: Mar 2013
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
corinereyes is on a distinguished road
Re: Variables

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 is offline   Reply With Quote
Old 07-15-2019, 02:48 AM   #5
corinereyes
Newly Registered User
 
Join Date: Mar 2013
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
corinereyes is on a distinguished road
Re: Variables

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!
corinereyes is offline   Reply With Quote
Old 07-15-2019, 02:48 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,437
Thanks: 166
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Variables

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 07-15-2019, 03:56 AM   #7
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,473
Thanks: 0
Thanked 575 Times in 571 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Variables

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 07-15-2019 at 04:02 AM.
June7 is offline   Reply With Quote
Old 07-15-2019, 04:26 AM   #8
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,829
Thanks: 56
Thanked 1,032 Times in 998 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Variables

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
__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Old 07-15-2019, 04:37 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Variables

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-15-2019, 04:49 AM   #10
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,554
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Variables

arnelgp,

Is that meant to be

Code:
Case Nz(Me.Frame112, 0) = 1 And Nz(Me.Frame134, 0) = 0
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-15-2019, 04:53 AM   #11
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Variables

you got me, there.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-15-2019, 05:05 AM   #12
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,554
Thanks: 442
Thanked 847 Times in 818 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Variables

Took me a while, I was wondering what the significance of the 134 was.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 07-15-2019, 11:38 PM   #13
corinereyes
Newly Registered User
 
Join Date: Mar 2013
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
corinereyes is on a distinguished road
Re: Variables

Quote:
Originally Posted by Minty View Post
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 is offline   Reply With Quote
Old 07-15-2019, 11:42 PM   #14
corinereyes
Newly Registered User
 
Join Date: Mar 2013
Posts: 9
Thanks: 0
Thanked 0 Times in 0 Posts
corinereyes is on a distinguished road
[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 by corinereyes; 07-16-2019 at 03:56 AM. Reason: solved
corinereyes is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Variables in SQL cpc Queries 3 11-09-2012 09:18 AM
Variables within String variables possible? perlfan Modules & VBA 2 10-12-2011 04:18 AM
variables alarants Modules & VBA 4 09-14-2009 12:47 AM
Variables and SQL James.uk Modules & VBA 8 04-23-2004 10:47 PM
Variables in SQL? lewando_bria Queries 5 07-30-2002 07:11 AM




All times are GMT -8. The time now is 05:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World