Conditional Expression For Radio Button

L

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,171
Office Version
  1. 365
Platform
  1. Windows
I have two controls in my form and each has a radio button. In control 1 the user selects "No", then I want control 2 to automatically select "No" for the radio button. If the user selects "Yes" in control 1 then I want control 2 to do nothing and have the user the option to pick "Yes" or "No"

Will I have to build IIF statement in control 2 in the Validation Rule property? Or something in the Event properties?
 
Last edited:
Sort by date Sort by votes
Joe4 said:
OK, let's back up again, because I see it appears you still aren't quite clear how this code all works. Since it all builds on itself, if you do not understand the base layer, building on top of that is only going to make things more confusing. It is important to understand how the code works before building on top of it.

So, there is a problem with the logic of this block of code that your wrote:
Code: 
Select Case Me![Frame103] 
    Case 1 
        Me![Text101] = "Y" 
        Me.Frame112.Locked = False 
        Me![Text111] = "Y" 
        Me![Text111] = "N" 
        Me![Text111] = "<acronym style="border-width: 0px 0px 1px; border-bottom-color: rgb(0, 0, 0); border-bottom-style: dotted; cursor: help;" title="To be discussed">TBD</acronym>"

What this says, is if that the first option is selected in Frame103, then set the following fields equal to the values that you specify. Hence, each item you have listed under there should only be listed once. If option 1 is selected for Frame103, what do you want to set Text111 to? It will only be one of "Y", "N", or "TBD", not all three. So you only should have one of those lines listed there, not all three.

It might make more sense if I use we look at a similar no-technical example of this structure. Maybe something like this:
Code: 
Select Case "We are having a baby"
    Case "The baby is a boy"
        Set BabyName = "Karl"
        Set BabyName = "Bob"
        Set BabyName = "Mark"
That doesn't make much sense. You are only going to give the baby one name, not all three. So you only want to list the BabyName that you want.
If you did use this structure it would do something -- the last setting wins. So the BabyName would be "Mark". Hence the other two lines are unnecessary and don't really do anything. Hence they should not be there.

So, back to your code, under each "Case" block, no field/property combination should be listed more than once.

Does that help clarify things?
So, how would you rewrite that block of code you have?
Click to expand...

I understood what you meant intially, I gave you the code as is and stated you could only put the field once. I tried different combinations, including:

Code: 
Private Sub Frame103_AfterUpdate()
    Select Case Me![Frame103]
        Case 1
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "Y"
        Case 2
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "N"
        Case 3
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "TBD"
        Case 4
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
            Me![Text111] = "N"
        Case 5
            Me![Text101] = "TBD"
            Me.Frame112 = 3
            Me.Frame112.Locked = True
            Me![Text111] = "TBD"
                                
End Select
End Sub

They above code doesn't work either because when I select "No" or "TBD" in Frame103 it doesn't lock it's corresponding option in Frame112 nor when I select "Yes" in Frame103 and select "No" or "TBD" in Frame112 it does not show in my table.

I even tried the following:

Code: 
Private Sub Frame103_AfterUpdate()
    Select Case Me![Frame103]
       [B] Case 1[/B]
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "Y"
        [B]Case 1[/B]
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "N"
        Ca[B]se 1[/B]
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "TBD"
        [B]Case 2[/B]
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
            Me![Text111] = "N"
        [B]Case 3[/B]
            Me![Text101] = "TBD"
            Me.Frame112 = 3
            Me.Frame112.Locked = True
            Me![Text111] = "TBD"
                                
End Select
End Sub

For this code, when I select "Yes" in Frame103 it allows me to pick all three options in Frame112 but again only "Y" shows in my Table, since it's the first choice for Case 1.
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
In your first block of code, you have 5 cases for Frame103. I thought you only had three options ("Yes", "No", "TBD"). What are the other two cases/options? It important to understand that each case number corresponds to exactly one option. If you go into the properties of that radio button, it shows you the number associated with that particular selection, and that is what the "Case" number refers to.

In your second block of code, you have "Case 1" listed three times. You cannot do that. Just like you wouldn't repeat a particular field/property combination more than once in a single block, you would not list a single case more than three times. When the first option is selected, you only want one set of rules to apply.

I feel like we are spinning our wheels here a bit. I don't think you quite grasp how these Case statements work, and despite my efforts, I don't think I am able to clearly explain it to you in this limited space here. It is not uncommon for this sort of thing to happen when a user tries to dive deep into VBA without having the proper fundamental instruction first (trying to run before they can walk). I would highly recommend picking up some introductory Access VBA books and working through them. Once you understand the different topics and concepts, it will make a lot more sense and b easier to program.

I learned some years ago using a book named "Beginning Access 2000" by Robert Smith and David Sussman. It was published by Wrox Press. I imagine that they have a much newer version now. I found it extremely helpful. There are also many good articles and tutorials on-line that can be found with Google searches.
 
Upvote 0
Joe4 said:
In your first block of code, you have 5 cases for Frame103. I thought you only had three options ("Yes", "No", "TBD"). What are the other two cases/options? It important to understand that each case number corresponds to exactly one option. If you go into the properties of that radio button, it shows you the number associated with that particular selection, and that is what the "Case" number refers to.

In your second block of code, you have "Case 1" listed three times. You cannot do that. Just like you wouldn't repeat a particular field/property combination more than once in a single block, you would not list a single case more than three times. When the first option is selected, you only want one set of rules to apply.

I feel like we are spinning our wheels here a bit. I don't think you quite grasp how these Case statements work, and despite my efforts, I don't think I am able to clearly explain it to you in this limited space here. It is not uncommon for this sort of thing to happen when a user tries to dive deep into VBA without having the proper fundamental instruction first (trying to run before they can walk). I would highly recommend picking up some introductory Access VBA books and working through them. Once you understand the different topics and concepts, it will make a lot more sense and b easier to program.

I learned some years ago using a book named "Beginning Access 2000" by Robert Smith and David Sussman. It was published by Wrox Press. I imagine that they have a much newer version now. I found it extremely helpful. There are also many good articles and tutorials on-line that can be found with Google searches.
Click to expand...

That is correct, I only have three option "Yes" "No" and "TBD".

Two options work out perfectly fine. They include:

Code: 
[B]Case 2[/B]
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
            Me![Text111] = "N"
[B]Case 3[/B]
            Me![Text101] = "<acronym title="To be discussed">TBD</acronym>"
            Me.Frame112 = 3
            Me.Frame112.Locked = True
            Me![Text111] = "<acronym title="To be discussed">TBD</acronym>"

The issue is with Case 1only:

Code: 
Select Case Me![Frame103]
       [B] Case 1[/B]
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "Y"

For instance, if I only have the above for Case 1, even though I can select all three options in Frame112, the results will only return a "Yes" in my Table no matter if I pick "No" or "TBD" (in Frame112). This shouldn't be happening. I understand this because I only have one field in the last line. How can I incorporate the other two options so that when I pick "Yes" in Frame103 I will be allowed to pick "Yes" "No" and "TBD" in Frame112 but ALSO have my Table show "Yes" "No" and "TBD" for the selection I make in Frame112? I hope this is clear. I gave you the other scenarios I tried but it did not work as intended.

Thanks for the tip. I will check out those authors.
 
Last edited:
Upvote 0
How can I incorporate the other two options so that when I pick "Yes" in Frame103 I will be allowed to pick "Yes" "No" and "<acronym title="To be discussed" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">TBD</acronym>" in Frame112 but ALSO have my Table show "Yes" "No" and "<acronym title="To be discussed" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help;">TBD</acronym>" for the selection I make in Frame112? I hope this is clear. I gave you the other scenarios I tried but it did not work as intended.
Click to expand...
So, are you saying that after you make a selection in Frame112, that you want one of your TextBoxes (101 or 111) updated to reflect that?

If so, then you will need to add similar code to the the AfterUpdate event of the Frame112 (and not put it in this code, for the AfterUpdate of the Frame103), as it the the event of updating your selection in Frame112 that is driving that update you want.
 
Upvote 0
Joe4 said:
So, are you saying that after you make a selection in Frame112, that you want one of your TextBoxes (101 or 111) updated to reflect that?

If so, then you will need to add similar code to the the AfterUpdate event of the Frame112 (and not put it in this code, for the AfterUpdate of the Frame103), as it the the event of updating your selection in Frame112 that is driving that update you want.
Click to expand...

No - Textbox101 and Textbox111 is used because it allows me to put the letter "Y" "N" and "TBD" in my Table instead of the normal Access values of "1" "2" "3". My main Table has a drop-down Lookup of another Table that stores the values "Y" "N" and "TBD". So in my code when I refer to the Textbox101 or Textbox111, it looks into the respective field of my main Table (using the control source to refer to the Table field) and simply records it as letters instead of numeric values.

Again, the issue is with Case 1. I select "Yes" in Frame103 which allows me to pick any of the three options (Yes, No, TBD) in Frame112 (this working as designed) BUT my Table is only recording "Y" for this field even if I select "No" or "TBD" in Frame112. Please let me know if you have any other questions, sometimes I am not clear so pardon me :)
 
Last edited:
Upvote 0
Upvote 0
Joe4 said:
It sounds to me like your Table fields are not bound to your Form controls, so that making the update on the Form updates the underlying Table field that should be connected to that Form control.

Here are some good links on bound vs. unbound controls
Introduction to controls - Access
http://www.igetit.net/newsletters/Y03_12/BoundUBound.aspx (this last one actually has a picture to the book I was telling you about!).
Click to expand...

Thanks - I am very familiar with bound/unbound. I don't think that is the issue.

When I changed the value for Case 1 to "TBD" it picks it up in my Table but if I pick "Yes" or "No" in Frame112 it'll still say "TBD". See below:

Code: 
Private Sub Frame103_AfterUpdate()
    Select Case Me![Frame103]
        Case 1
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            Me![Text111] = "[B]TBD"[/B]
        Case 2
            Me![Text101] = "N"
            Me.Frame112 = 2
            Me.Frame112.Locked = True
            Me![Text111] = "N"
        Case 3
            Me![Text101] = "TBD"
            Me.Frame112 = 3
            Me.Frame112.Locked = True
            Me![Text111] = "TBD"
                                
End Select
End Sub

I also changed the value for Case 1 to "No" and it does the same, it won't show "Yes" or "TBD". How can I make it show all three values in my Table if any one of them is selected in Frame112? I'm sure it has to do with the code.
 
Upvote 0
I think the Nomenclature might be confusing things again.

What exactly do you mean when you say "updated in the Table"?
What we are working in a Form here.
Are you opening the underlying Table to look at the results after you do this? Or is your use of Table really mean something else (i.e. something on your Form)?

Are you closing your Form first (updates probably won't be reflected until that record is closed so it gets updated)?
Also, if your table was open prior to this Form entry (and while the Form entry was happening), you will probably need to close and re-open the Table to see the changes.
 
Upvote 0
Joe4 said:
I think the Nomenclature might be confusing things again.

What exactly do you mean when you say "updated in the Table"?
What we are working in a Form here.
Are you opening the underlying Table to look at the results after you do this? Or is your use of Table really mean something else (i.e. something on your Form)?

Are you closing your Form first (updates probably won't be reflected until that record is closed so it gets updated)?
Also, if your table was open prior to this Form entry (and while the Form entry was happening), you will probably need to close and re-open the Table to see the changes.
Click to expand...

That is correct, we are working in a Form. The Form is bounded to a Table. When I enter data to the Form, it will show up in my Table that displays all records. The Table is under the table objects section of the Access objects. I enter data in my Form, save it, close the form, open the table, look for the record and see that it's not showing "No" or "Yes" when I had selected the radio button "No" and "Yes". It only shows "TBD". The reason is because of the way this code is written:


Code: 
Case 1
            Me![Text101] = "Y"
            Me.Frame112.Locked = False
            [COLOR=#ff0000]Me![Text111] = "[B]<acronym title="To be discussed">TBD</acronym>"[/B][/COLOR]

This last line states only to show "TBD", how can we make it say also show "Yes" and "No" when they are selected? That is my ultimate question.
 
Upvote 0
I think you have so much going on here, it is very hard to tell what is connected to what, and what should impact what without actually seeing the database and Form you are working on. I think the best way to help you would be the following:

1. Remove any sensitive data from your database, and upload a copy of your database to a file sharing site for me to download
2. Provide a real simple example (step-by-step) of what you are trying to do, and what should happen behind the scenes

Then, I can work in the exact same setup you have, see what is happening, and see what needs to be done to get it to work the way you like.

I won't be able to download the database until I am at home later tonight.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
VBA code for Next button to move to each record in Textbox1
Replies
2
Views
214
welshgasman
welshgasman
C
  • Question
Access application gives 'Not responding' at this code line. Having difficulty in resolving
Replies
6
Views
243
chazrab
C
K
  • Question
Fill in form text box based on selection from a combobox
Replies
1
Views
597
Micron
M
D
  • Solved
Combo Box changing customer name to random number
Replies
1
Views
302
DarkSmile
D
MCTampa
  • Solved
Mixing Number and Text Query Criteria from a Form
Replies
5
Views
432
MCTampa
MCTampa

Forum statistics

Threads
1,215,368
Messages
6,124,521
Members
449,169
Latest member
mm424

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top