Simulating Multivalue field in form display

[tom.hampton]

I would like to simulate the display capabilities of multivalue fields in Access 2010. Specifically, I have one table (table1) which has a link to another table (table2) through a junction table (tableJ) due to the one-to-many nature of the tables.

What I would like to display in a subform are all values of table2 with check boxes that show which of these values are linked to table1 through the junction table (tableJ). In this manner additional links can be selected/edited within the junction table without having to choose from a dropdown list.



Thinks like a movie database that can have many types of genres connected to a single movie. As new genres are added to the database, they can easily be added to the particular movie's link.

Thanks for any guidance.

[June7]

The junction table is associating movie with genres?

The only way I know to do anything like what you describe is with radio buttons or checkboxes in an Option Group control.

[tom.hampton]

That would require modification of the form every time that a new table2 item was added to the records. I need something a little more dynamic. The subform for table2 would likely be a continuous form listing all items in table2 with check boxes showing the link between table1 (the main table) and tableJ (the junction table).

[June7]

That's true, but how often is there a new movie genre?

Combobox or listbox really the only way to go if you want dynamic without a lot of VBA.

Use a form/subform arrangement to display the junction table records related to the movie on the main form.

[tom.hampton]

Actually I was just using that as an example. The real application is a contacts database and the table2 is a categories/groups table that the user can assign a contact to. In this case, new categories can be added (or edited) quite frequently. I am not adverse to using VBA and am somewhat familiar with it. Just need some guidance in how to get there.

[June7]

What you describe is highly non-conventional and I expect will require a lot of VBA.

Why is form/subform arrangement as I described not suitable? This is the conventional approach along with combobox to select category in a new record.

[tom.hampton]

Take a look at how Outlook display categories when you want to categorize a contact. All available categories are shown with a checkbox next to those that are enabled for the particular contact. This is the type of action that I would like. If this requires a lot of VBA programming, then I will have to come up with another solution.

[ItsMe]

Perhaps creating an alias for each column in the "Category" table. You could include an Instant If statement within each alias to indicate -1 or 0 dependent upon the value of the sister column. A checkbox control would then be created for and bound to each alias.