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
|
|||
|
|||
Trouble w/ Value List
Hello,
I'm attempting to create a database after seven years of not using Access and I'm already stuck on what is probably a very simple issue: I am using Win Vista w/ MS Office 2000. I am creating a resource DB, similar to the contact template DB, except for three unique fields. Each entry must have a) one of three set choices b)any combination of eight set choices and c)any combination of four set choices. I have created the basic db and form without problem. It is trying to specify the list fields that is causing me trouble. In following the "Help" directions, I keep ending up with a column of all my choices and it only choosing the first one. What I am wanting is three drop down lists, two of which allow for multiple selections. This is a private db, not for use on the Web or a server. I am the only one inputting data. If I need to offer more information in order to receive advice please let me know. Thanks, Jacki |
#2
|
|||
|
|||
Trouble w/ Value List
ikcaj wrote:
Hello, I'm attempting to create a database after seven years of not using Access and I'm already stuck on what is probably a very simple issue: I am using Win Vista w/ MS Office 2000. I am creating a resource DB, similar to the contact template DB, except for three unique fields. Each entry must have a) one of three set choices b)any combination of eight set choices and c)any combination of four set choices. I have created the basic db and form without problem. It is trying to specify the list fields that is causing me trouble. In following the "Help" directions, I keep ending up with a column of all my choices and it only choosing the first one. What I am wanting is three drop down lists, two of which allow for multiple selections. This is a private db, not for use on the Web or a server. I am the only one inputting data. If I need to offer more information in order to receive advice please let me know. Thanks, Jacki Right-click one of the combo boxes and look at the properties. The RowSource says where it gets the items from, and the RowSourceType indicates if that's a simple list or a reference to a table or query. If you set "Limit to List" to True, then only those items can be picked. Under some circumstances you may want to use a validation rule instead - you couldn't have a list of all the email addresses in the world (limit to list) but you could devise a validation rule expression which returned True if the text entered contained an "@" character. The row sources for your three combo boxes should be different. If the items are in the same table, then you'll need three different queries on that table to pick out the right items. It's often worth experimenting to figure this sort of thing out. Make sure the control Wizard is turned on, then add a few extra combo boxes to your form (you can delete them later). Fool around with the options that the Wizard offers you, and then study the properties of the resulting controls. One thing rings alarm bells. You mention multiple selections. The List Box control has a "Multi Select" property - I don't think the Combo Box has (can't find it in Access 2007 anyway). However, there are dangers - it's considered bad design to have multiple values in one table field (violates the "normalisation" rule of "1st Normal Form" to be specific). Think of it this way: if multiple selections would mean you'd want to put several values - maybe separated with commas - into a single field, then think again. The "Normal Form" rules exist to prevent your data structure being too complex to manage. On the other hand, if multiple selects meant that each item was tied to a separate true/false field, then it's ok. Generally, though, multiple values means multiple records, or you'll be up very late puzzling out later stages of your design. HTH Phil, London |
#3
|
|||
|
|||
Trouble w/ Value List
On Thu, 17 Sep 2009 08:34:03 -0700, ikcaj
wrote: I am using Win Vista w/ MS Office 2000. I am creating a resource DB, similar to the contact template DB, except for three unique fields. Each entry must have a) one of three set choices b)any combination of eight set choices and c)any combination of four set choices. If you're trying to store multiple values in a field... DON'T. A2007 has a "multivalue field" but it's a mistake on Microsoft's part, in my opinion. And it's not necessary. Rather than a *field*, you should consider using two new *tables*. You can use a simple table field for your "one of three set choices", but the other two should use a Many to Many Relationship; e.g. your (b) option would involve three tables in all: 1. Your current table, with a primary key ResourceID (or whatever it is) 2. A new, 8-row table with the eight set choices, and a primary key SetBID (or something more meaningful) 3. A new table with fields ResouceID and SetBID as a joint two-field primary key You would use a Subform based on this third table, with a combo box to allow you to enter zero, one, two or eight SetB choices into separate records. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Trouble w/ Value List
I appreciate the helpful advice. I was thinking that multiple values in one
field was not the right way to go, but wasn't sure how to get around it. Thanks again for helping an out-of-date (and not that great to begin with) user get back up to speed. Jacki "John W. Vinson" wrote: On Thu, 17 Sep 2009 08:34:03 -0700, ikcaj wrote: I am using Win Vista w/ MS Office 2000. I am creating a resource DB, similar to the contact template DB, except for three unique fields. Each entry must have a) one of three set choices b)any combination of eight set choices and c)any combination of four set choices. If you're trying to store multiple values in a field... DON'T. A2007 has a "multivalue field" but it's a mistake on Microsoft's part, in my opinion. And it's not necessary. Rather than a *field*, you should consider using two new *tables*. You can use a simple table field for your "one of three set choices", but the other two should use a Many to Many Relationship; e.g. your (b) option would involve three tables in all: 1. Your current table, with a primary key ResourceID (or whatever it is) 2. A new, 8-row table with the eight set choices, and a primary key SetBID (or something more meaningful) 3. A new table with fields ResouceID and SetBID as a joint two-field primary key You would use a Subform based on this third table, with a combo box to allow you to enter zero, one, two or eight SetB choices into separate records. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|