Option group bound to a 2 column lookup field

[Guest]

I am working with MS Access 2002 SP3
I have a lookup field in a table. It is a value list with 2 columns. The
value list is 1;regional;2;Sub-regional;3;local.

I have an option group on a form which stores 1,2 or 3 in this field.

When I look at a datasheet view of the table, the field is populated with
the 1,2 or 3's entered through the option group on the form. But the values
are not associated with the second column eg when I click the drop down
arrow on the field the Regional, Sub-regional or local is not highlighted.
If I simply re-enter the 1,2 or 3 in datasheet view then click the drop down
arrow the second column value is highlighted.

This is causing me a problem because I have added a button to the form
containing the option group to display a bar chart of the option from a
linked form. The chart shows the correct information but the legend on the
bar chart is 1,2 and 3 instead of Regional, Sub-regional and local.
The legend is correct after I re-enter the values through the datasheet view
of the table.

Any ideas of how I can get around this.

Thanks

 

[Al Camp]

Denied,
Well, probably a bit of redesign will help.

I have a lookup field in a table. It is a value list with 2 columns. The
value list is 1;regional;2;Sub-regional;3;local.
I have an option group on a form which stores 1,2 or 3 in this field.

First, if you use a Combobox (aka lookup) to select a 1,2,3... then there's really no
need to have an OptionGroup capture that value. The Combo can do that, as long as it's
bound to your RegionCode field. A Combo can display 1,2,3 just as well as an OptionGroup.
Second, instead of a ValueList, create a table to store your values...
tblRegions
RegionCode RegionDesc
1 Regional
2 Sub-Regional
3 Local
and use that table as the source for your combo.
This will allow you to "re-associate" Code to Desc anytime you want in any subsequent
form, query, or report (related by RegionCode).
Datasheet/table view is a very limited view, with no control other than a dump of raw
data, so in that mode you will only see the RegionCode. Use a query instead to display
your data that will allow you to associate and see the Desc with your Code, and view it
what "appears" to be a table view.
BUT...Better yet... create a form based on that query to view your data. You certainly
DON'T want any users to access the tables directly.

A two column combo based on a value list, or ANY source, only displays the column 2
values to aid in selection. After the bound field value is captured, that value has no
idea what column 2 was at selection time. On any subsequent form, query, or report, the
RegionCode needs to be reassociated with it's corresponding Desc value... from the
table... linked/related by RegionCode.

 

[Guest]

Al thanks for the help, but I still need help with the bar chart.

I understand your comments on the combo box but whether or not it refers to
a value list or to a regions table the legend on the bar chart still refers
only to 1,2 or 3.
I know that using the combo box field instead of the options group on the
form will work. I'm designing the form to mirror the questionaire the data
is gathered on. The option group allows for easier entry and since the
screen mirrors the questionaire it is quicker to check data entry.

Any thoughts on how I can get the legend on the bar chart to read the second
column from the combo box.

Thanks

 

[Al Camp]

Well I'm no chart guru, but here's a trick...
Select the 1 legend, and make the text font white on white to make it disappear.
Place an Access Caption (Bring to Front) right on top of that with the words you want to
display (Region, Sub Region, etc)
Do the same for all three series.

But, I'm thinking that the values for your chart are delivered to it by a query, and
whatever you name those query fields is what the chart uses for the legend.
If you still have peoblems, please describe your query and query columns. We might be
able to do something there...

My work-around should get you out of a jam for now. Check out...
http://peltiertech.com/Excel/Charts/index.html
for excellent info on all aspects of charting.

Another minor point... please don't <snip> my previous posts from the thread. It's
better to see the whole conversation in the last post. Then responders don't have to pull
up old posts to see the flow of the problem.