Displaying 'allow multiple values' fields 1

[Nelviticus]

I'm using Access 2007. I have a couple of fields in my main table where 'allow multiple values' is set to 'yes'. These fields look up values in other tables and bind to the 'ID' column while displaying the value column.

For example, I have a field in my main table called 'Areas' which allows the user to pick any number of values from my Areas table.

If I create a form based on the main table these multi-value fields are displayed as list boxes. They list all the values in the Areas table with check-boxes next to each one, and the check-box is either ticked or un-ticked depending on whether the user picked that value.

Is there a way to just display the 'picked' values rather than the entire list? If I change the control to a text box it does this but it displays the IDs rather than the values, as it's bound to the ID column.

At the moment it looks like this:

Areas
-----
x North
  South
  East
x West

If I change it to a text box it looks like this:

Areas
-----
0,3

But I want it to look something like this:

Areas
-----
North
West

Is this possible?

Thanks

Nelviticus

 

[kjv1611]

Maybe use a Combo box instead of text box, and set control source to a query that only selects the "ticked" items. For instance, after you select the table under data, you can click the build button, and then use the query builder to filter down the records to your liking..

--

"If to err is human, then I must be some kind of human!" -Me

 

[MajP]

Using look ups in your table is a bad design and leads to a lot of problems. There are several FAQs on this subject and you can also Google this subject.

Your form or report can display just the value by creating your recordsource from a query. You link AreaID from your Areas table to AreaID in your look up table. You want to use a left outer join so that you show all records in your main table and related records in your Area table. Now you can add "Area Name" to your form or report from the look up table.

 

[MajP]

This should read:

You want to use a left outer join so that you show all records in your Areas table and related records in your "lookup" table.

 

[MajP]

Let me be more clear. You can and should use lookups, but do not put them in the table, only on the form. Here is the article:

http://www.mvps.org/access/lookupfields.htm

 

[Nelviticus]

I think I understand ... so, in my main table instead of having the lookup I just have a value field with a type of 'long integer', then define the link between it and the Areas table using the relationships, is that right?

I'm trying that out now but it doesn't seem to let me choose multiple values unless I change the Display Control to a List Box or Combo Box, but that makes it into a lookup again.

I'm used to Access '97 so 'allow multiple values' is new to me. I assumed it would be a nice easy way of avoiding having an extra table to map multiple Areas to each main record but it's looking like it causes as many problems as it solves

Nelviticus

 

[MajP]

If you want to display your value in a text box.
1)Build the forms recordsource from a query. If you click on the three dots ... in the recordsource property if will bring up the query builder. In the query builder you add your Areas table and the AreaLookUp table. Create a link. Then right click on the join line and you should see "Edit join". You can choose "select all records from Areas and only related records from AreasLookUp"
2)Now you can select fields from both tables. Add "AreaName" to your query and form.

What you are describing is a standard lookup that is common in earlier versions of Access.

I have not worked with Access 2007 and this "Allow Multiple Values" is so you can put two or more values in the same field. Like "West,North". So a record is related to both West and North.

 

[Lilliabeth]

This feature is my biggest complaint about Access 2007. It took what was already "the creation of the Evil One" and combined it with an even worse idea - storing multiple values in a single bucket. Comma-seperated values in a single field. Ack!



--Lilliabeth

 

[Nelviticus]

Well you'll all be pleased to know that the multi-value fields are now gone, replaced with an extra linking table. Thanks for the help!

Nelviticus

 

[aperez54]

ah crapola. I just created several tables using this new feature assuming it's all good. I really should've researched first.