Full Version: Using Option Buttons On Continuous Subforms
UtterAccess Forums > Microsoft® Access > Access Forms
Firesmith
In on of my forms, I have a continuous subform that lists all of the records within a (reasonably small table). I would like to use an option button to select one (and only one) of these records and then use that information to update a second subform on the same form. It seems like an option group is the standard way to ensure that only one of the option buttons is selected at a time, but all of the examples of option groups I can find only have a small and constant number of option buttons that can all be displayed simultaneously. That won't work in this case because the number will vary with the size of the table and not all will be displayed simultaneously. I have therefore not used an option group and decided to handle the "only select one at a time" behavior myself in code attacked to the clicking of the button. This means that when I click the one button, not only should the corresponding field in the current record be set to True, but all of the others should be set to False. The first part is trivial, but I am not sure how to set the select field of all other records to False. Any suggestions on either how to do this or some other better way? Any help would be greatly appreciated.
GroverParkGeorge
You need two update queries. One to set the value of this field to "False" for all records where the Primary Key is not the one selected, and one to set the value of this field to "True" for that record.
You can run them both, in sequence, from your command button.
Firesmith
I was thinking along those lines. Setting the one value to True is easy as I am already at that record so that query is done and works fine. However, I am unsure of what the code for the other query would look like. It seems that I would select all records in the table that are not the current one, but how to set only one field of all of those records (i.e., the field associated with the control) is not clear to me. I already have a temporary table where I store all of the fields including the record IDs, the associated names, and the values of the command button. Can you suggest what the code might look like to first select the other records and then assign the value of the command button field to False? I have not been able to find a similar example anywhere, and the basic MS SQL descriptions and trivial examples has not been very helpful. (I basically know about how to do about 10-15 things in SQL and this isn't one of them.)
Thanks
theDBguy
Hi,
ardon me for jumping in... but just wondering what is the purpose of this exercise.
If you have a continuous subform, isn't it true that you can only have one "current" (selected) record at a time? So, I am not sure why there is a need for an Option button.
What is the purpose of "selecting" the subform record? What are you planning to do with the selection? Is the checkbox field going to be used elsewhere? If not, there is a way to add a checkbox on the form that will automatically clear all the other checkboxes, especially if you say your record source has an ID field.
Just my 2 cents... 2cents.gif
Firesmith
I am working on an app to support safety and security analyses for the purpose of creating safety and security-related requirements. It will be provided on a CD with my next book. I have a table and form for entering/editing abuses (i.e., accidents and attacks). Each abuse can harm multiple assets (people property, environment, and services), and each asset can be harmed in multiple ways (depending on both the abuse and the asset). On the abuse form, I have two subforms: one for selecting relevant assets and one for selecting relevant harm types based on the current asset (and current abuse). I need to show three things on the continuous assets subform: the current asset for which the harm types subform shows the associated harm types (option button - only one selected at a time), a check box to show all relevant assets for the abuse, and the names of the relevant assets (assets listed alphabetically by name). I also store the asset ID in a hidden text box on the continuous subform. I have considered and rejected using a single selection combo-box because it couldn't differentiate between the current asset and all assets that are relevant (I need to be able to reselect a current relevant asset without inadvertently marking it as not relevant). I also considered and rejected using a separate text field for showing the current asset because it got to confusing when it came to entering data (one could try entering a new asset in either the text box or by checking the relevant access box) . I show two things on the continuous harm types subform: a check box to show whether the harm type is relevant to the current asset and the harm type's name. Thus, it is a fair bit more complex than most of my other forms and beyond what I have ever done before. I have basically the same situation on my danger (safety hazard and security threat) form so if I get one, it should be easy to reuse (at least the design) from the abuse form on the danger form.
theDBguy
Hi,
Thanks for the additional information. However, I'm afraid that not knowing anything about your business process and not seeing your setup doesn't really help me understand what you're trying to accomplish.
Also, I forgot to mention that maybe you don't need a subform at all. Have you tried using just a Listbox to show these related data on your form? By default, a Listbox can only have one selected row at a time.
Just my 2 cents... 2cents.gif
Firesmith
I tried the listbox version but it didn't let me differentiate between the current asset (about which harm types are being entered using the second subform) and any of the other relevant assets:
For example, with my automated taxi application (simplified):
abuse/accident1 = taxi collision
asset1 = passenger
harm type1 = death
harm type2 = injury
asset2 = taxi
harm type3 = destroyed (i.e., totalled)
harm type4 = collision damage
abuse/accident2 = flat tire
asset1 = passenger
harm type5 = lost time
asset3 = taxi service
harm type = service degradation
Operational concept (entering the preceding information in top to bottom order):
1) First I use the abuse form to pick the abuse (say abuse 1).
(2) I use the option button on the select assets subform to select the first relevant asset as my current asset (asset1 = passenger). A a check in the asset check box of asset 1 appears meaning it is relevant to abuse/accident 1 (taxi collision). The select harm types subform gets populated with any harm types previously selected with (abuse 1 and asset 1)
(3) I use the check boxes in the select harm types to select harm types 1 and 2 (death and injury). The join table gets updated with two records, both with the same abuse and asset ID but the two different harm types.
(4) I check the option button on the select assets subform to select the second asset as my current asset (asset2 = taxi). The previous option button associated with asset 1 becomes deselected. The
select harm types subform gets reinitialized with any harm types previously selected for abuse 1 and asset2.
(5) I check the box for harm types 3 and 4 in the harm types selection subform. The join table gets updated with two more records for these two selections.
(6) I go to the second abuse on the parent form. The asset selection subform gets initialized with all relevant assets
(7) I select asset 1 again. The harm types selection subform gets initialized with any relevant harm types for abuse 2 and asset 1.
(8) I select harm type 5 using the harm types selection subform to enter lost time for the passenger when a flat tire occurs.
and so on...
Ocan also de-select harm types for given abuses and assets and have the corresponding records removed from the join table.
Similarly, I can de-select an asset (making it no longer relevant for the current abuse) and all join table records corresponding to that abuse/asset combination get deleted.
It is basically a way to store information concerning which accidents harm which assets in which way (harm types).
Thus, I need to know both which is the current accident/asset pair for which I am currently selecting relevant harm types and I also need to keep track of all of the assets that a given accident can harm. This why I am using both option buttons (to select the single current asset) and check boxes (to select and show all assets that can be harmed).
Bottom line is I need to know how to keep only one option button (only one asset) selected at a time. I can make that (new) option button selected by setting it to true. However, I still do not know how to deselect all of the other option buttons (actually only the previous one since I can assume that only one was previously selected). I would prefer to deselect all of the other option buttons just to make sure that they are all deselected and the option buttons never get out of sync (i.e., more than one selected at a time).
Therefore, can someone please tell me how to deselect all option buttons except for the current one (which I know how to select)?
theDBguy
Hi,
hope you get the answer you're looking for. I couldn't tell you how to do something I don't understand yet. You are concentrating on the "option group" solution because you think that is what you need. However, without knowing what you know about your database, I couldn't tell you if I agree to that solution or not. You might get the answer you're looking for but if it's just a workaround for an improper database design, then you'll be back again later to find another workaround after this one. What I am trying to teach you is to use the "best" design approach so that any other requirements you may need today and in the future wouldn't be so hard to implement. Couldn't you at least give us some screenshots to see what you're working with? It might help us get a bearing on where you're headed with your project.
Good luck!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.