473,505 Members | 16,544 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Select Case Help

15 New Member
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub opgHowLate_AfterUpdate()
  2. Select Case Me.opgVisitType.Value And Me.opgHowLate.Value
  3.  
  4.     Case (Me.opgVisitType = "1" And Me.opgHowLate = "1")
  5.         Me.ckbFastTrackPaper.Visible = True
  6.         Me.lblFastTrackPaper.Visible = True
  7.         Me.lblNotes.Visible = True
  8.         Me.txtNotes.Visible = True
  9.  
  10.     Case (Me.opgVisitType = "1" And Me.opgHowLate = "2")
  11.         Me.ckbEvalShortTreat.Visible = True
  12.         Me.lblEvalShortTreat.Visible = True
  13.         Me.lblEvalOnly.Visible = True
  14.         Me.ckbEvalOnly.Visible = True
  15.         Me.lblReschedule.Visible = True
  16.         Me.ckbReschedule.Visible = True
  17.         Me.lblRescheduleWhen.Visible = True
  18.         Me.opgRescheduleWhen.Visible = True
  19.         Me.lblRescheduleProvider.Visible = True
  20.         Me.opgRescheduleProvider.Visible = True
  21.  
  22.      Case (Me.opgVisitType = "1" And Me.opgHowLate = "3")
  23.         Me.lblEvalOnly.Visible = True
  24.         Me.ckbEvalOnly.Visible = True
  25.         Me.lblReschedule.Visible = True
  26.         Me.ckbReschedule.Visible = True
  27.         Me.lblRescheduleWhen.Visible = True
  28.         Me.opgRescheduleWhen.Visible = True
  29.         Me.lblRescheduleProvider.Visible = True
  30.         Me.opgRescheduleWhen.Visible = True
  31.         Me.lblNotes.Visible = True
  32.         Me.txtNotes.Visible = True
  33.  
  34.     Case (Me.opgVisitType = "2" And Me.opgHowLate = "1")
  35.         Me.lblShorterVisit.Visible = True
  36.         Me.ckbShorterVisit.Visible = True
  37.         Me.lblNotes.Visible = True
  38.         Me.txtNotes.Visible = True
  39.  
  40.     Case (Me.opgVisitType = "2" And Me.opgHowLate = "2")
  41.         Me.lblShorterVisit.Visible = True
  42.         Me.ckbShorterVisit.Visible = True
  43.         Me.lblReschedule.Visible = True
  44.         Me.ckbReschedule.Visible = True
  45.         Me.lblRescheduleWhen.Visible = True
  46.         Me.opgRescheduleWhen.Visible = True
  47.         Me.lblRescheduleProvider.Visible = True
  48.         Me.opgRescheduleProvider.Visible = True
  49.         Me.lblNotes.Visible = True
  50.         Me.txtNotes.Visible = True
  51.  
  52.      Case (Me.opgVisitType = "2" And Me.opgHowLate = "3")
  53.         Me.lblShorterVisit.Visible = True
  54.         Me.ckbShorterVisit.Visible = True
  55.         Me.lblReschedule.Visible = True
  56.         Me.ckbReschedule.Visible = True
  57.         Me.lblRescheduleWhen.Visible = True
  58.         Me.opgRescheduleWhen.Visible = True
  59.         Me.lblRescheduleProvider.Visible = True
  60.         Me.opgRescheduleProvider.Visible = True
  61.         Me.lblNotes.Visible = True
  62.         Me.txtNotes.Visible = True
  63.  
  64. End Select
  65.  
  66.  
  67. End Sub
Sep 7 '16 #1
9 1127
zmbd
5,501 Recognized Expert Moderator Expert
So let's start with the proper syntax
MS VBA Reference >Select Case Statement (read more?)
Expand|Select|Wrap|Line Numbers
  1. Select Case testexpression 
  2.    [Case expressionlist-n
  3.      [statements-n]] 
  4.    [Case Else
  5.      [elsestatements]]
  6. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub opgHowLate_AfterUpdate()
  2.  Select Case True
  3.       Case (Me.opgVisitType = "1" And Me.opgHowLate = "1")
  4.          Me.ckbFastTrackPaper.Visible = True
  5.          Me.lblFastTrackPaper.Visible = True
  6.          Me.lblNotes.Visible = True
  7.          Me.txtNotes.Visible = True
  8.       Case (Me.opgVisitType = "1" And Me.opgHowLate = "2")
  9.          Me.ckbEvalShortTreat.Visible = True
  10.          Me.lblEvalShortTreat.Visible = True
  11.          Me.lblEvalOnly.Visible = True
  12.          Me.ckbEvalOnly.Visible = True
  13.          Me.lblReschedule.Visible = True
  14.          Me.ckbReschedule.Visible = True
  15.          Me.lblRescheduleWhen.Visible = True
  16.          Me.opgRescheduleWhen.Visible = True
  17.          Me.lblRescheduleProvider.Visible = True
  18.          Me.opgRescheduleProvider.Visible = True
  19.        Case (Me.opgVisitType = "1" And Me.opgHowLate = "3")
  20.          Me.lblEvalOnly.Visible = True
  21.          Me.ckbEvalOnly.Visible = True
  22.          Me.lblReschedule.Visible = True
  23.          Me.ckbReschedule.Visible = True
  24.          Me.lblRescheduleWhen.Visible = True
  25.          Me.opgRescheduleWhen.Visible = True
  26.          Me.lblRescheduleProvider.Visible = True
  27.          Me.opgRescheduleWhen.Visible = True
  28.          Me.lblNotes.Visible = True
  29.          Me.txtNotes.Visible = True
  30.       Case (Me.opgVisitType = "2" And Me.opgHowLate = "1")
  31.          Me.lblShorterVisit.Visible = True
  32.          Me.ckbShorterVisit.Visible = True
  33.          Me.lblNotes.Visible = True
  34.          Me.txtNotes.Visible = True
  35.       Case (Me.opgVisitType = "2" And Me.opgHowLate = "2")
  36.          Me.lblShorterVisit.Visible = True
  37.          Me.ckbShorterVisit.Visible = True
  38.          Me.lblReschedule.Visible = True
  39.          Me.ckbReschedule.Visible = True
  40.          Me.lblRescheduleWhen.Visible = True
  41.          Me.opgRescheduleWhen.Visible = True
  42.          Me.lblRescheduleProvider.Visible = True
  43.          Me.opgRescheduleProvider.Visible = True
  44.          Me.lblNotes.Visible = True
  45.          Me.txtNotes.Visible = True
  46.        Case (Me.opgVisitType = "2" And Me.opgHowLate = "3")
  47.          Me.lblShorterVisit.Visible = True
  48.          Me.ckbShorterVisit.Visible = True
  49.          Me.lblReschedule.Visible = True
  50.          Me.ckbReschedule.Visible = True
  51.          Me.lblRescheduleWhen.Visible = True
  52.          Me.opgRescheduleWhen.Visible = True
  53.          Me.lblRescheduleProvider.Visible = True
  54.          Me.opgRescheduleProvider.Visible = True
  55.          Me.lblNotes.Visible = True
  56.          Me.txtNotes.Visible = True
  57.   End Select
  58.   Me.Repaint
  59. End Sub
My other thought is a nested select
Expand|Select|Wrap|Line Numbers
  1. Select Case Me.opgVisitType.Value
  2.     Case 1
  3.         Select Case Me.opgHowLate
  4.         Case 1
  5.             'Action 1
  6.         Case 2
  7.             'Action 2
  8.         Case 3
  9.             'Action 3
  10.         Case Else
  11.             'Action 4
  12.         End Select
  13.     Case 2
  14.         Select Case Me.opgHowLate
  15.         Case 1
  16.             'Action 5
  17.         Case 2
  18.             'Action 6
  19.         Case 3
  20.             'Action 7
  21.         Case Else
  22.             'Action 8
  23.         End Select
  24.     Case Else
  25.             'Action 9
  26. 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:
Sep 8 '16 #2
KatcDolly
15 New Member
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?


Expand|Select|Wrap|Line Numbers
  1. 'Selects for Evaluation and 10 min late
  2.    If Me.opgVisitType = "1" And Me.opgHowLate = "1" Then
  3.         Me.lblFastTrackPaper.Visible = True
  4.         Me.ckbFastTrackPaper.Visible = True
  5.         Me.lblNotes.Visible = True
  6.         Me.txtNotes.Visible = True
  7.  
  8.         Else
  9.             Me.lblFastTrackPaper.Visible = False
  10.             Me.ckbFastTrackPaper.Visible = False
  11.             Me.ckbFastTrackPaper.Value = Null
  12.             Me.lblNotes.Visible = False
  13.             Me.txtNotes.Visible = False
  14.             Me.txtNotes.Value = Null
  15.  
  16.     End If
  17.  
  18. 'Selects for Evaluation and 11 to 20 min late.
  19.     If Me.opgVisitType = "1" And Me.opgHowLate = "2" Then
  20.         Me.ckbEvalShortTreat.Visible = True
  21.         Me.lblEvalShortTreat.Visible = True
  22.         Me.lblEvalOnly.Visible = True
  23.         Me.ckbEvalOnly.Visible = True
  24.         Me.lblReschedule.Visible = True
  25.         Me.ckbReschedule.Visible = True
  26.         Me.lblRescheduleWhen.Visible = True
  27.         Me.opgRescheduleWhen.Visible = True
  28.         Me.lblRescheduleProvider.Visible = True
  29.         Me.opgRescheduleProvider.Visible = True
  30.  
  31.         Else
  32.             Me.lblEvalShortTreat.Visible = False
  33.             Me.ckbEvalShortTreat.Visible = False
  34.             Me.ckbEvalShortTreat.Value = Null
  35.             Me.lblEvalOnly.Visible = False
  36.             Me.ckbEvalOnly.Visible = False
  37.             Me.ckbEvalOnly.Value = Null
  38.             Me.lblReschedule.Visible = False
  39.             Me.ckbReschedule.Visible = False
  40.             Me.ckbReschedule.Value = Null
  41.             Me.lblRescheduleWhen.Visible = False
  42.             Me.opgRescheduleWhen.Visible = False
  43.             Me.opgRescheduleWhen.Value = Null
  44.             Me.lblRescheduleProvider.Visible = False
  45.             Me.opgRescheduleProvider.Visible = False
  46.             Me.opgRescheduleProvider.Value = Null
  47.  
  48.     End If
  49.  
  50. 'Selects for Evaluation and 21 or more min late
  51.     If Me.opgVisitType = "1" And Me.opgHowLate = "3" Then
  52.         Me.lblEvalOnly.Visible = True
  53.         Me.ckbEvalOnly.Visible = True
  54.         Me.lblReschedule.Visible = True
  55.         Me.ckbReschedule.Visible = True
  56.         Me.lblRescheduleWhen.Visible = True
  57.         Me.opgRescheduleWhen.Visible = True
  58.         Me.lblRescheduleProvider.Visible = True
  59.         Me.opgRescheduleProvider.Visible = True
  60.         Me.lblNotes.Visible = True
  61.         Me.txtNotes.Visible = True
  62.  
  63.         Else
  64.             Me.lblEvalOnly.Visible = False
  65.             Me.ckbEvalOnly.Visible = False
  66.             Me.ckbEvalOnly.Value = Null
  67.             Me.lblReschedule.Visible = False
  68.             Me.ckbReschedule.Visible = False
  69.             Me.ckbReschedule.Value = Null
  70.             Me.lblRescheduleWhen.Visible = False
  71.             Me.opgRescheduleWhen.Visible = False
  72.             Me.opgRescheduleWhen.Value = Null
  73.             Me.lblRescheduleProvider.Visible = False
  74.             Me.opgRescheduleProvider.Visible = False
  75.             Me.opgRescheduleProvider.Value = Null
  76.             Me.lblNotes.Visible = False
  77.             Me.txtNotes.Visible = False
  78.             Me.txtNotes.Value = Null
  79.  
  80.     End If
  81.  
  82.  
  83. 'Selects for Follow-up and 10 min late
  84.     If Me.opgVisitType = "2" And Me.opgHowLate = "1" Then
  85.         Me.lblShorterVisit.Visible = True
  86.         Me.ckbShorterVisit.Visible = True
  87.         Me.lblNotes.Visible = True
  88.         Me.txtNotes.Visible = True
  89.  
  90.         Else
  91.             Me.lblShorterVisit.Visible = False
  92.             Me.ckbShorterVisit.Visible = False
  93.             Me.ckbShorterVisit.Value = Null
  94.             Me.lblNotes.Visible = False
  95.             Me.txtNotes.Visible = False
  96.             Me.txtNotes.Value = Null
  97.     End If
  98.  
  99.  
  100. 'Selects for Follow-up and 11 to 20 min late
  101.     If Me.opgVisitType = "2" And Me.opgHowLate = "2" Then
  102.         Me.lblShorterVisit.Visible = True
  103.         Me.ckbShorterVisit.Visible = True
  104.         Me.lblReschedule.Visible = True
  105.         Me.ckbReschedule.Visible = True
  106.         Me.lblRescheduleWhen.Visible = True
  107.         Me.opgRescheduleWhen.Visible = True
  108.         Me.lblRescheduleProvider.Visible = True
  109.         Me.opgRescheduleProvider.Visible = True
  110.         Me.lblNotes.Visible = True
  111.         Me.txtNotes.Visible = True
  112.  
  113.         Else
  114.             Me.lblShorterVisit.Visible = False
  115.             Me.ckbShorterVisit.Visible = False
  116.             Me.ckbShorterVisit.Value = Null
  117.             Me.lblReschedule.Visible = False
  118.             Me.ckbReschedule.Visible = False
  119.             Me.ckbReschedule.Value = Null
  120.             Me.lblRescheduleWhen.Visible = False
  121.             Me.opgRescheduleWhen.Visible = False
  122.             Me.opgRescheduleWhen.Value = Null
  123.             Me.lblRescheduleProvider.Visible = False
  124.             Me.opgRescheduleProvider.Visible = False
  125.             Me.opgRescheduleProvider.Value = Null
  126.             Me.lblNotes.Visible = False
  127.             Me.txtNotes.Visible = False
  128.             Me.txtNotes.Value = Null
  129.  
  130.     End If

Thank you!
Sep 8 '16 #3
zmbd
5,501 Recognized Expert Moderator Expert
+ Did you try the revised code I posted?
It is a very simple change.
Select Case Me.opgVisitType.Value And Me.opgHowLate.Value

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.
Sep 8 '16 #4
KatcDolly
15 New Member
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?
Sep 9 '16 #5
zmbd
5,501 Recognized Expert Moderator Expert
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.
Sep 9 '16 #6
KatcDolly
15 New Member
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.
Sep 9 '16 #7
PhilOfWalton
1,430 Recognized Expert Top Contributor
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
Sep 9 '16 #8
KatcDolly
15 New Member
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?
Sep 9 '16 #9
zmbd
5,501 Recognized Expert Moderator Expert
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
Sep 9 '16 #10

Sign in to post your reply or Sign up for a free account.

Similar topics
20
1669
CASE help
by: Joey Martin | last post by:
I am scanning an HTML file. I need to gather certain data from areas that start with <SMALL> text. Let me show the code, then explain more. ---- Set fso =...
ASP / Active Server Pages
17
14596
SELECT CASE in ASP???
by: Newbie | last post by:
Dear friends, I am having a hard time understanding how to use a SELECT CASE in ASP. I have used it in VB but never in ASP scripting. Scenerio: I have 2 textboxes on a form that I have to...
ASP / Active Server Pages
9
3612
Select Case Syntax Error
by: Kevin | last post by:
Hi, I am getting a syntax error Microsoft VBScript compilation error '800a03ea' Syntax error On the code below. The error references the "End Select" line Can anyone help me with what I am...
ASP / Active Server Pages
4
2817
VB code help for Select Case
by: Terencetrent | last post by:
I having been using Access '97/2002 for about 4 years now and have never really had the need or the time to learn visual basic. Well, I think the time has finally come. I need help with Visual...
Microsoft Access / VBA
3
3377
select case not working
by: mark.irwin | last post by:
Hello all, Have an issue where a redirect pushes data to a page with a select case which then redirects to another page. Problem is the redirect isnt working in 1 case. Code below: strURL =...
ASP.NET
3
3762
[ASP.NET 2.0] Select Case
by: Rob Meade | last post by:
Ok - I *think* this is only different in .net 2.0 - as I've not had any problems in the past, but then maybe I've not tried it... I have a value being read from an xml file where the value maybe...
ASP.NET
2
5160
Select Case problem...
by: scole954387 | last post by:
Hi, I have a problem. I have written a SQL statement that has a nested select case statement on the 'where' clause to condition the results. ...
MySQL Database
2
1417
Select Case Confusion
by: JP2R | last post by:
The following is my code - I'm new to VB - not to Excel - just VB and need some support/guidance/direction (please) I have a spreadsheet and in column "X" titled COS I need to put an integer that...
Visual Basic 4 / 5 / 6
18
2219
Need help with Select Case
by: nfenlon | last post by:
I'm new to VB express 2005 and am having some trouble with coding. I'm using a CASE structure in my program and i'm wondering if anyone can help me. Instead of Saying "Case 1,2,3,4" is there a way...
Visual Basic 4 / 5 / 6
21
17392
beacon
Select Case/If Statement to total a group of option buttons
by: beacon | last post by:
Hello to everybody, I have a section on a form that has 10 questions, numbered 1-10, with 3 option buttons per question. Each of the option buttons have the same response (Yes, No, Don't know),...
Visual Basic 4 / 5 / 6
0
7218
marktang
What is ONU?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
General
0
7103
Changing the language in Windows 10
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
Windows Server
0
7370
jinu1996
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
Online Marketing
1
7021
The easy way to turn off automatic updates for Windows 10/11
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
Windows Server
1
5035
isladogs
Access Europe - Using VBA to create a class based on a table - Wed 1 May
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
Microsoft Access / VBA
0
3188
Trying to create a lan-to-lan vpn between two differents networks
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
Networking - Hardware / Configuration
0
3177
Windows Forms - .Net 8.0
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
Visual Basic .NET
1
755
muto222
How to add payments to a PHP MySQL app.
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
PHP
0
409
bsmnconsultancy
Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
General

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes
How to Post and Respond on Bytes
How to Promote and Link on Bytes
How to increase your Ranking on Bytes
Become a Recognized Expert on Bytes
Feedback Welcomed! Contact Us