Option Group on Subform (1 Viewer)

[Sleekmac]

Hello all, I'm new to the site, hoping someone familiar with both continuous subforms and option groups could give me a hand please?

I have a "dialog window"-type form with a subform in it. The subforms are in continuous mode, and I have a scrolling window in my main form that contains one subform for each record that meets the criteria. On each subform, I have a 3-button option group, each option corresponding to a particular "type" that I want assigned to the value in a linked table. The choice is made by the user, then there is a control button that executes an update query which saves the choice to the table (value is 1, 2 or 3)
This works fine when there is one record returned. The problem I am running into is when there are multiple instances of the subform, each for a different record. When you click a choice in the subform for record #1, it updates the value in the option group for all of the open subforms. I need this to work such that a different choice can be made in the option group for each subreport. It seems like the application only knows one option group, rather than optiongroup.subform(record 1), optiongroup.subform(record)2, etc. Any ideas on what I am doing wrong?

The subform is "modal" and continuous, and the option group is unbound. I've attached copies of the forms and the save query. If anybody can offer some insight, I'd greatly appreciate it!

 

[boblarson]

If you have an unbound control on a continuous form you need to understand that the control, while visible in many places (for each record) on the continous form, is actually the same control. When you set it's value, it becomes the value for all of the visible, and what appears to be separate, controls of the same name.

However, when you use a bound control, even though the control is, in essence, really only one control, it acts differently as it displays the contents of the records above and below the one you are currently working on. So, in that case you actually see the control showing different values.

It's the way of Access and continuous forms.

 

[Sleekmac]

Thanks Bob. I actually had it bound originally, and had to change it, because when I bind it to the table that I want it to update, it doesn't allow me to make any changes. The value is by default 0 in the field, and the choices in the control group are 1, 2, and 3. So when the form runs, all three option buttons are grayed out, and when you click on them, nothing changes in the table. Is there some setting on this control group that I am missing?

 

[Sleekmac]

Quick update: I tried changing some of the checks around this so I could have it default to a value of 3. I was thinking that maybe the problem was because the default value of 0 was not one of the option choices. It's now stuck on three and won't change if you click on either of the other two option buttons. I made this option group using the wizard.

 

[boblarson]

What is your form's recordsource. I just built a test form with something similar (3 options - 1, 2, and 3 with no option selected as default and bound to a LONG INTEGER field type) and it worked fine for me in continuous forms (Access 2000). I'm guessing that it may have to do with the way the option is tied to the record. If you can strip down the database enough to post it here, we can try to look at it and see why it's not working. Although, if you spot it before you do that, that is obviously better.

 

[Sleekmac]

Do you think it's because I've linked the control to a query that is then linked to the table? The subform is linked to a query containing the target field, maybe I have to link it directly to the table?

 

[boblarson]

It's possible, but it shouldn't be that way. What's the field's datatype?

 

[Sleekmac]

the field type was Integer, I just updated it to Long Integer but the same thing happens. I scrapped the option control group and tried a drop-down combo box. The same thing is happening. Every time I bind it to that field, it won't allow me to change the value from what it already is in the table. So maybe I shouldn't be setting the contol source to the field, but instead something like a control target? I can't find anything like that though.

 

[boblarson]

No, the control source should be the field. It almost sounds like your query is not updateable. You are using the FORM's recordsource as the basis for this option group, correct? In other words, ALL of the controls on the form have their control source based on a single query for the whole form. Is that correct?

 

[Sleekmac]

You're right, the entire recordsource is non-updatable. I went into the underlying query and tried to uncheck a checkbox and it won't update. I just have to figure out how to make it updatable!

 

[Sleekmac]

Hey Bob, thanks alot for your help with this! I figured it out...the reason I couldn't update the table under that query is because there were two linked tables under that query. When I set it to copy the information I needed from the second table to the first table prior to the function where I was getting the error, so everything I needed was in one table first, it lets me update that table by making changes in the query. Did not know you couldn't do that.

Here's another question that I hope is an easy one: I have a main form which is a container for those continuous subforms, and all of the subforms I want are there, but there's always one blank one left at the bottom. How do I get rid of that extra record? It's the one queries create on the last line for you to input information. This is a read-only form (except for the updating option buttons in the subforms) so there would never be a point where I'd want the user to enter another record in the blank subform.
Thanks,
Don

 

[boblarson]

So, in design mode, set the subform's ALLOW ADDITIONS property to NO.

 

[Sleekmac]

Bob - You are the man! Thanks so much for your help.