Multi Value Fields Advice (1 Viewer)

I'd like to add a multi-value combobox to my database. I understand I want to avoid table-lookups, so I'd like to add it to my form, but can't figure out how. I've been unable to locate a post, article or tutorial. I must be searching using the wrong terminology. Can anyone direct me to an article or post that might help me figure out the best way to execute this?

Thank you!

Todd

2 methods

1) type the values in that you want via the wizard
2) If you already have data in your table you can use a select query (using the distinct clause) as the record source

Hi, thanks for the reply. I was hoping to use the wizard, but it does not give me the option to set up for multi values. The only way I can get it to do that is if I make it a look-up field at the table level, which I've been told not to do. So in this case, is it OK to have a look up field at the table?

Multi-value fields are a problem to use. So much so that the developers had to come up with completely new SQL syntax to make the queries come even close to working. Avoid them as you avoid table-level lookups. Do it the old-fashion way. Create your lookup table or if the list is fixed and unlikely to change, you can create a value list on the field in the table.

If you go with a value list rather than a table, it is best to define the list on the table rather than on each form. If the list is changed on the table, newer versions of Access will propagate the changes to forms that reference the field.

If you actually need to store multiple values, you will need a separate table to hold them and the best visual representation on the form will be a subform. You can remove the borders and other things that make a subform stand out so that the subform blends in better on the main form.

Thanks for the feedback.. I am going the "old fashioned way" Pat describes, and have successfully created an association table (pointer table? not sure of correct terminology) to allow me to store multiple values against a single ID.

My question is about getting the data from a form against the table. For example, say I want a form with a list box (seems drop down combo boxes are a no-no?) of states to put three states against one vendor, how to I go about this? I can't figure where and how I save this information. Can you further direct me on this?

Thank you again for your help!

No. Comboboxes on forms are what it's all about The problem is with defining lookups on tables. There is no problem with defining lookups on forms. To implement multi-values, you would create a subform The subform would be bound to the junction table. The field used to link to the main form whould be hidden and defined as the master/child link. The other field would be displyed as a combo. You could use a multi-select list-box if you prefer the visual (that is the ONLY good thing about multi-value fields) but you'll need to write code in the background to populate it. I wouldn't unless my client absolutely insisted. I would showhim the difference in cost between the "free" way with a subform (no-code) and the "not free" way with the multi-value listbox.

todd

your problem is more one of data analysis. it sounds like you are saying that a given supplier say, can supply several US states from the whole population of states.

in normal terms this would require 2 tables

- supplier table
- supplierstates table, which stores any number of states

now a multi-value field seeks to replace the supplierstates table with a single field in the supplier table, which can contain a number of values.

this is a non-relational structure, and may or may not be applicable. it does introduce problems of sorting/searching and maintenance that may or may not be resolved adequately. I do not know, as on principle i have avoided using the feature. for one thing, by being a non-standard feature, it removes any possiblity of migrating the access database to a different platform. in practice the feature adds very little. with experience the support structure for a proper lookup table can be in place very quickly anyway.

Thanks for your guidance. I was able to use your advice (and terminology) to find a great tutorial (http://www.youtube.com/watch?v=nszRT3nRUMU) to help along with some other posts on this site to help me figure it out.

Thank you all for providing such excellent support to a beginner like me!