Multi Value Combo Boxes
in [Form]
|
From: Rich Locus on 1 Jun 2010 13:32 Hello: One of the new features of Access 2007 (sometimes criticized) is the ability to create a table field as "Allow Multiple Values" using the Lookup Wizard when specifying the data type. This shows the values with a checkbox and allows the user to select multiple items (i.e. select CA, OR, WA as states in which a business resides). I was hoping to just define a table field as Text and then create the "Allow Multiple Values" when creating a combo-box on the form. The form wizard does not seem to create the same objects as when doing it in table design mode. So, unless someone knows how to create these "Many to Many Under the Cover" relationships in forms, I will probably have to do that functionality in the table design mode. I am familiar with the usual list box ability to select multiple items into a collection, but that is available in Access 2003. This new feature is only in 2007, and I'm not sure how to invoke it from a form. Ideas? -- Rich Locus Logicwurks, LLC
From: Albert D. Kallal on 1 Jun 2010 13:51 The issue here is that you can use a list box or a sub-form to create a classic relationship. You don't unfortunate have the same user interface control (I wish they put that new control in the design tool box). In the case of a multi-select list box, you can create a very similar looking effect by creating a sub-form with two columns, one a check box, the other text. Take a look at the screen shots here, the 1st form has what looks like two list boxes, but on the right is a sub-form with a check box. http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm For a multi-value combo box with a drop down of check boxes one could launch a small popup form with the above continues form (or data sheet) idea. So you can create a fairly close interface here, but I do wish that new control was exposed for our use. I quite like it.... -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal(a)msn.com
From: Rich Locus on 1 Jun 2010 14:23 Albert: Thanks for the great suggestions! Let's assume my parent table is a table of businesses. My child table is the states in which they reside. To use the sub-form approach, it seems I would define my "States" table as the source of the sub-form, and each line in the states table would point back to the main table using a foreign key. So, if the main table had two records, key 200 and 201, then the States table would look something like this: CA 200 OR 200 WA 200 NY 200 CA 201 MN 201 AZ 201 And so on. Does this seem reasonable? -- Rich Locus Logicwurks, LLC "Albert D. Kallal" wrote: > The issue here is that you can use a list box or a sub-form to create a > classic relationship. > > You don't unfortunate have the same user interface control (I wish they put > that new control in the design tool box). > > In the case of a multi-select list box, you can create a very similar > looking effect by creating a sub-form with two columns, one a check box, the > other text. > > Take a look at the screen shots here, the 1st form has what looks like two > list boxes, but on the right is a sub-form with a check box. > > http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm > > For a multi-value combo box with a drop down of check boxes one could launch > a small popup form with the above continues form (or data sheet) idea. So > you can create a fairly close interface here, but I do wish that new control > was exposed for our use. I quite like it.... > > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal(a)msn.com > >
From: Albert D. Kallal on 1 Jun 2010 14:44 "Rich Locus" <RichLocus(a)discussions.microsoft.com> wrote in message news:1BAF7908-98A9-4265-8AB2-1E5C5D10F13C(a)microsoft.com... > Albert: > So, if the main table had two records, key 200 and 201, then the States > table would look something like this: > > CA 200 > OR 200 > WA 200 > NY 200 > > CA 201 > MN 201 > AZ 201 > > And so on. > > Does this seem reasonable? You are right on the money. The issue is that the proper relation would say to use a sub-form. that sub-form would likely have the FK (foreign key) as standard long value column. (the above column to hold the 200 and 201 in your example). The combo box would have to be feed by a either a value list, or better yet a table that holds all of the state abbriviations. The user could type in the state, but clearing a combo box is much easier. Because a sub-form always shows an extra line, then the user would click on combo box, select state, then click on the next new line combo box below and again select sate. it goes quite fast, but it still not quite as nice as that built in check box. If your list is not long, say only about 10 choices, then a un-bound listbox with code behind to "toggle" or highlight or un-high the selections works well. You can see a screen shot of that here: the 4th screen shot here shows this: http://www.members.shaw.ca/AlbertKallal/Articles/UseAbility/UserFriendly.htm However, for 4-5, then that new cool built in combo works well. However, for 50 states, it actually quite HARD to scroll and select. I think the combo box idea above in a sub-form will actually be faster, and the user would have a choice of 100% keyboard. if you setup the record cycle settings correct, then the user can hit tab, and type in the state in addition to using the mouse clicks. So, in your case because this is a long list of states, a standard combo box likely is better then the multi select combo box (it too hard to scroll through the long list to just select one state). Keep in mind that the table of states that drives the combo box is just a table to save the user some time typing So, you have your main table, then tableListOfStates the above table and example you talking about. And, then the optional 3 table to drive the combo box if you choose to have a combo box in place of the user typing in the state name. (but, user could use combo drop down, or just type right into that combo box). Remember, those combo boxes work well in continues sub-forms as the "grid" link in the previous post shows. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal(a)msn.com
From: Rich Locus on 1 Jun 2010 21:01 Albert: Thanks again for taking the time to provide such a thorough solution. Regards, -- Rich Locus Logicwurks, LLC "Albert D. Kallal" wrote: > > "Rich Locus" <RichLocus(a)discussions.microsoft.com> wrote in message > news:1BAF7908-98A9-4265-8AB2-1E5C5D10F13C(a)microsoft.com... > > Albert: > > So, if the main table had two records, key 200 and 201, then the States > > table would look something like this: > > > > CA 200 > > OR 200 > > WA 200 > > NY 200 > > > > CA 201 > > MN 201 > > AZ 201 > > > > And so on. > > > > Does this seem reasonable? > > You are right on the money. The issue is that the proper relation would say > to use a sub-form. that sub-form would likely have the FK (foreign key) as > standard long value column. (the above column to hold the 200 and 201 in > your example). The combo box would have to be feed by a either a value list, > or better yet a table that holds all of the state abbriviations. The user > could type in the state, but clearing a combo box is much easier. Because a > sub-form always shows an extra line, then the user would click on combo box, > select state, then click on the next new line combo box below and again > select sate. it goes quite fast, but it still not quite as nice as that > built in check box. If your list is not long, say only about 10 choices, > then a un-bound listbox with code behind to "toggle" or highlight or un-high > the selections works well. You can see a screen shot of that here: > > the 4th screen shot here shows this: > http://www.members.shaw.ca/AlbertKallal/Articles/UseAbility/UserFriendly.htm > > However, for 4-5, then that new cool built in combo works well. However, for > 50 states, it actually quite HARD to scroll and select. I think the combo > box idea above in a sub-form will actually be faster, and the user would > have a choice of 100% keyboard. if you setup the record cycle settings > correct, then the user can hit tab, and type in the state in addition to > using the mouse clicks. So, in your case because this is a long list of > states, a standard combo box likely is better then the multi select combo > box (it too hard to scroll through the long list to just select one state). > > Keep in mind that the table of states that drives the combo box is just a > table to save the user some time typing > > So, you have your main table, then > > tableListOfStates the above table and example you talking about. > > And, then the optional 3 table to drive the combo box if you choose to have > a combo box in place of the user typing in the state name. (but, user could > use combo drop down, or just type right into that combo box). > > Remember, those combo boxes work well in continues sub-forms as the "grid" > link in the previous post shows. > > > -- > Albert D. Kallal (Access MVP) > Edmonton, Alberta Canada > pleaseNOOSpamKallal(a)msn.com > >
|
Pages: 1 Prev: Sorting Combo box items Next: Albert D Kallal's Ribbon Code |