Results 1 to 8 of 8
  1. #1
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

    Select Case Shortcut

    I just stumbled on a shortcut for a 4 case Select block. Here's how most of us would probably do it:
    Code:
    Select Case SomeTest
       Case 1
         set this1 rowsource
         disable ctl1
    
       Case 2
         set this2 rowsource
         disable ctl1
    
       Case 3
         set this3 rowsource
         enable ctl1
         set ctl2 value = year
         set ctl3 value = year +1
    
       Case 4
         set this4 rowsource
         enable ctl1
         set ctl2 value = year
         set ctl3 value = year+1
    
    End Select
    I thought "how can I use Select Case 1,2, etc. as one Case and set the rowsource accordingly and write only one line for enabling or setting control value?".
    You can't "IF Case = 1 Then..."
    The answer is

    Code:
    Select Case SomeTest
      Case 1,2
        If 1 Then set this1 rowsource
        If 2 Then set this1 rowsource
        disable ctl1
    
      Case 3,4
        If 3 Then set this3 rowsource
        If 4 Then set this4 rowsource
        enable ctl1
        set ctl2 value = year
        set ctl3 value = year+1
    
    End Select
    I dropped from 18 to 12 lines. Not a big deal here, but for larger Case blocks (don't read that as Select blocks) I imagine there could be a lot of consolidation. For me, the bigger thing was how to know which Case was True in a multi case block. Then again, I suppose everyone else who helps out here already knew this.

    Then again, I did research for "ms access vba which case is true in a multi case block" but saw nothing relevant. HTH someone in the future.



    EDIT - Well, somehow I broke it. Instead of getting one of 4 values I'm now only getting one of two. In either Case, both IF's now run, so the end result is that regardless of the Case the second IF is also executing. I tried closing the form and compacting to no avail. If I get it back I'll update.
    Last edited by Micron; 12-15-2021 at 11:02 AM. Reason: update
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This looks like it could be a good fit for a project I recently worked on. Thanks for the tip!

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    @Micron,
    Does case 3 or 4 work without the End If?

    I ask as I can see some people copying this and then saying it does not work, if that is not the case?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Old news

    you can also do things like

    Code:
    Select Case someNumber
        case is >25
        case is <=25
    end select
    haven't figured out a between tho'

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Welshgasman View Post
    @Micron,
    Does case 3 or 4 work without the End If?

    I ask as I can see some people copying this and then saying it does not work, if that is not the case?
    When entire If is on one line, you don't need End If, right?
    In each Case, if the first line is false, the second one must be true else that Case itself would not execute at all. What anyone else's code might need is a Case Else but for my purpose and to present the idea it's not necessary. I might try "If Else " though, just to see what happens with a Case Else block.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by Ajax View Post
    Old news

    you can also do things like

    Code:
    Select Case someNumber
        case is >25
        case is <=25
    end select
    haven't figured out a between tho'
    I don't see that as being the same. I'm using IF to test the value of the Case without using "If Case" because it wasn't allowed. Your example is just using a Case statement. However, if you consider that old news then I'm not surprised because I figured somebody would have figured this out before me. I can certainly say that regarding Case with Between:
    Code:
    Case 0 To 3
    The operative word is To.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The operative word is To.
    Ahh! after all these years

    thought your point was about saving lines using

    case 1,2

    rather than

    case 1
    case 2

    I missed your point about if

    so you can use excluding the reference to Sometext

    ie
    If 1 Then set this1 rowsource

    instead of
    If sometext=1 Then set this1 rowsource

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm not sure you get it yet. What I forgot to say in my first post was that if you count each blue line you'll see I was able to cut out at least one iteration of the line (total 4?). That's because I was able to use the effect of grouping (Case 1, 2) for the same blue line. The IF allowed me to set one source or the other. This also eliminated 2 Case lines.
    If sometext=1 Then set this1 rowsource
    In my case it was an option frame value. Yes, you could test as
    "If Me.frame = " inside the Case block but since the value is passed to the Case block by the frame afterUpdate I thought it made more sense to use what was passed. The trick was how to refer to it. At first I tried "If Case = " which would not compile. I didn't think it was as easy as "If 1 Then " but that was the next attempt. It seems to me that the Case behaviour in this example mimics a With block.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Select Case without case
    By DMT Dave in forum Access
    Replies: 4
    Last Post: 11-13-2019, 08:21 PM
  2. Select Case using Tab Index
    By shylock in forum Access
    Replies: 4
    Last Post: 11-21-2018, 01:48 PM
  3. Pl sql case select
    By mrmmickle1 in forum Queries
    Replies: 1
    Last Post: 11-17-2015, 11:14 PM
  4. Should I /can I use a Select Case statement?
    By Gina Maylone in forum Access
    Replies: 1
    Last Post: 12-13-2014, 12:08 PM
  5. Select case help
    By killermonkey in forum Programming
    Replies: 7
    Last Post: 10-25-2013, 05:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums