If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Option group bound to a 2 column lookup field
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 |
#2
|
|||
|
|||
Option group bound to a 2 column lookup field
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. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "Access denied" Access wrote in message ... 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 |
#3
|
|||
|
|||
Option group bound to a 2 column lookup field
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 |
#4
|
|||
|
|||
Option group bound to a 2 column lookup field
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. -- hth Al Camp Candia Computer Consulting - Candia NH http://home.comcast.net/~cccsolutions "Access denied" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|