Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Option group change all values upon selection

Posted on 2011-10-28
19
Medium Priority
?
457 Views
Last Modified: 2012-08-14
In the attached access2k3 mdb I'm trying to figure out option group radio buttons. Please help with the VBA needed to do the following.

1.) Upon clicking an ON radio button choice for any row it should change all the other values to OFF

2.) When the user clicks ON or OFF the value in field status should change to 0 for off and 1 for on.

 OptionGroupArray.mdb
0
Comment
Question by:Dalexan
  • 7
  • 7
  • 4
  • +1
19 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37048385
I'm not understanding.

I see the radio buttons that change the row status.

I don't understand what you mean in #1 and #2.

#1: If you click 'any'  'ON' turn them ALL off?
#2: click individual 'OFF' turns it to what?  since 'ON' will turn ALL off per #1.
0
 
LVL 75
ID: 37048395
For Number two, all you need to do is change the Option Value for Option 11 from 2 to 0 - see image.


Capture1.gif
0
 

Author Comment

by:Dalexan
ID: 37048476
Slightwv exactly as you say for #1 and #2
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Dalexan
ID: 37048487
Sorry. For #1 click any ON turn the rest off
0
 

Author Comment

by:Dalexan
ID: 37048489
I'n other words you can turn any 1 row on which should set that rows status to 1 and sets the status of all other rows to 0 an off
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37048542
So, there can be only one 'on'?

The single 'on' can be turned 'off' so there can be ALL off?
0
 

Author Comment

by:Dalexan
ID: 37048557
Exactly
0
 
LVL 75
ID: 37048563
"Exactly"
I'm not sure that makes sense ...

mx
0
 
LVL 75
ID: 37048568
Which brings up the point ... How does more than one ever get on anyway ?

mx
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37048577
>>How does more than one ever get on anyway ?

It doesn't.

Any 'on' turns 'off' everything else.  An 'off', sort of by default, turns 'off' everything (likely more simple to code?)

MX, Can you write it faster?  I'll step off...  I'm about done for the night/weekend anyway.
0
 
LVL 75
ID: 37048585
"It doesn't."
Then there is no point in turning them all off.  I guess I really don't get it.

mx
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37048594
>>I guess I really don't get it.

Maybe it's the Friday beer but I think I do.


There can be only one 'on'.  Any other 'on' will turn 'off' any others and activate itself.

Now, you can turn the 'only' 'on', well, off.

All can be 'off' but only one can be 'on'.


0
 
LVL 75
ID: 37048600
Yeah ... I guess.

Dalexan: Can we add a AutoNumber to that table ?

mx
0
 

Author Comment

by:Dalexan
ID: 37048643
I think I need to explain myself a little more if not for my own benefit. slightwv has the logic exactly correct but maybe there's a better way to do what I'm trying to accomplish. I created this sample database to understand how to code option control's. I looked around for examples online but I couldnt find one that does anything close to what I'm looking to do.

There's a table that controls a process and only 1 process can be running at one time so the user can turn any one of the processes on at a time. I cant modify this underlying table to add an autonumber field but the table does have a unique number field that could potentially be used for what you need to do to make this work.
0
 
LVL 75
ID: 37048680
"but the table does have a unique number field"
ok ... I don't see that field ... can you upload a db with that table and field ?

mx
0
 

Author Comment

by:Dalexan
ID: 37048690
If you could just add a field called ID and make it numeric. The actual table this represents is quite large with many many fields but the ID field is unique and an integer.
0
 
LVL 75
ID: 37048704
OK ...
I can't work on this right now ... but later tonight or tomorrow.

mx
0
 

Author Comment

by:Dalexan
ID: 37048716
NP, thanks for your time.
0
 
LVL 49

Accepted Solution

by:
Dale Fye earned 500 total points
ID: 37050438
OK, I've modified your database slightly.  I added an ID (autonumber) column to your table.  You could probably have used the text field (Name) to do what I'm doing in the code, but I prefer to use Autonumber fields as the Primary Keys in my tables.

BTW, Name is a reserved word in Access and should be avoided as the name of a table or field.  See Allen Browne's site for more on reserved words.

I then added this ID field to your form and named it txt_ID.  But hid it because you really never want your users to see an ID value.  This way, you can use that value in the code that fires in the AfterUpdate event of the option group.

That code first saves the current record change to acknowledge the change in the Status value caused by the change in the option group selection.  It then checks to see if you pressed the "ON" button.  If you pressed "Off", you don't need to do anything, but if you pressed ON, then you need to change the Status value of all the other records in the table to "Off" (or 2).  To do this, you must run an update query that sets the STATUS value for all the recods, I've provide three examples of a SQL statement that would do this.

Once you update the recordset, you must requery or refresh (either will work in this case) the forms recordset to see the changes.  The difference with requery and refresh (in this instance) is that when you use requery, the focus is set to the first record in the recordset, but with refresh, the record focus stays on the record that you just turned ON.  Requery requeries the entire recordset which will remove any records which have been deleted or add those that have been added since the form was originally loaded (or last requeried).  Refresh just refreshes the values of the current recordset.
 OptionGroupArray.mdb
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question