Using a subform for multiselect

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Using a subform for multiselect

Post by NWGal »

Well, I have, with great assistance on here, built a process for assigning multiple roles to my contacts via checkboxes in a subform. This works fine but sadly when I created my db as a hybrid with the front end linked to sharepoint, this particular piece stopped working. I've looked for some help on getting it sorted, but solutions are too complicated for my novice brain and too time consuming, so I think the best and quickest solution for the time being is to simply go back to using a subform as a list, only I am unsure how to do this. The end result (as I have it now) is to add and update new records to a junction table for as many roles as a contact has.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using a subform for multiselect

Post by HansV »

For a hybrid database, you might check out Multi-Value fields - see Guide to multivalued fields. They have been created with SharePoint lists in mind.
Best wishes,
Hans

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Using a subform for multiselect

Post by NWGal »

HansV wrote:For a hybrid database, you might check out Multi-Value fields - see Guide to multivalued fields. They have been created with SharePoint lists in mind.
Now that is an interesting thought. I have avoided using mvf's because of all the advice to stick with junction tables instead. I can still see that point but if I can also see how switching to mvfs might simplify things. Suggestions on the best way to set this up? I'm guessing I would no longer need the contactrole junction table, but where would I put the mvf?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Using a subform for multiselect

Post by HansV »

The general recommendation is to avoid multi-valued fields in desktop databases, but they are well-suited for SharePoint backends.

You could create a multi-valued Roles field in the Contacts table. You wouldn't need a junction table.
Best wishes,
Hans

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Using a subform for multiselect

Post by NWGal »

Ok, I will give that whirl - thanks!

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Using a subform for multiselect

Post by Wendell »

A caution here - if you were using Sharepoint lists as your tables, that would work, but with the hybridized front-end you are now actually using SQL Server tables. While Sharepoint does actually store things in SQL Server, I believe it does some magic to make you think you have multi-valued fields. I'm not sure there is a true data type in SQL Server that behaves as a multi-value field. I'll do some additional research and post what I find out.

It didn't take long - the following is a quote from the article that Hans referred you to:
In addition to the preceding scenarios, you might also want to use a multivalued field when you are relatively sure your database will not be moved to Microsoft SQL Server at a later date. A Office Access 2007 multivalued field is upsized to SQL Server as a memo (ntext) field that contains the delimited set of values. Because SQL Server does not support a multivalued data type that models a many-to-many relationship, additional design and conversion work might be needed.
Wendell
You can't see the view if you don't climb the mountain!

NWGal
2StarLounger
Posts: 198
Joined: 21 Aug 2011, 02:32

Re: Using a subform for multiselect

Post by NWGal »

Wendell wrote:A caution here - if you were using Sharepoint lists as your tables, that would work, but with the hybridized front-end you are now actually using SQL Server tables. While Sharepoint does actually store things in SQL Server, I believe it does some magic to make you think you have multi-valued fields. I'm not sure there is a true data type in SQL Server that behaves as a multi-value field. I'll do some additional research and post what I find out.

It didn't take long - the following is a quote from the article that Hans referred you to:
In addition to the preceding scenarios, you might also want to use a multivalued field when you are relatively sure your database will not be moved to Microsoft SQL Server at a later date. A Office Access 2007 multivalued field is upsized to SQL Server as a memo (ntext) field that contains the delimited set of values. Because SQL Server does not support a multivalued data type that models a many-to-many relationship, additional design and conversion work might be needed.
Well phooey. Ok, guess I won't go in that direction. Too bad it looked like a pretty simple fix. So now back to how to create a subform that acts like a multi select list. :) Oh and now I remember one of the main reasons I stayed away from MVF's because I knew at some point I would be moving to using the web app option and I had read that best practice was to keep structure traditionally normalized.