MultiSelect ListBox in a subform (1 Viewer)

[CoddFish]

A PROVIDER can have one-to-many NEGATIVE LICENSING EVENTS. Each NEGATIVE LICENSING EVENT has
negative action(s) filed as:
- Denial
- Suspension
- Injunction
- Revocation
- Probation
and/or
- Fine.

These negative actions are not mutually exclusive in that each NEGATIVE LICENSING EVENT can have more than one negative action filed.

The PROVIDER form has a tab that is called "Negative Licensing". I would like to have a subform on this tab that allows multiple NEGATIVE LICENSING EVENTs and that allows a multiselect for the "Negative Action Filed" field. How do I do this, and how are multiple selections stored for a single field in a given record?

 

[Fizzio]

You cannot store this in a standard way as you have a 1-many-many relationship.

You either can
1. Create a nested subform (or some other subform combination) mirroring the current relationships you have
Ie
Main Form - Providor
Subform1 - Negative Licencing Events
Subform2 - Event Actions

2. Use a multi-select list box but concatenate the selections into one field (using code), then split the data when you want to display it in the listbox again - but this can be fiddly

The problem with 2 is that it makes analysing the data much harder to do as you have to interrogate the concatenated string to extract the relevant data so finding out how many people have been given a fine is a logistical nightmare!

Mile-O-Phile posted a demo somewhere in here of how to achieve 2 - have a hunt in the search to see if you can find it.

 

[CoddFish]

Fizzio,

I've never worked with nested subforms, but this is exactly what I need to do in this case (i.e., I am interested in solution #1). How would I implement this. I've looked in Access' HELP and in an Access book that I have, but there doesn't appear to be any discussion on this particular topic.

Thanks.

CoddFish

 

[Fizzio]

Nested subforms are just the same as normal subforms.
Imagine you have a main form and a subform. Adding a subform within the subform is exactly the same - You just have to make sure that the Master / Child links are correct.

 

[Fizzio]

Ruf 'n' Ready demo. Each colour is a separate form. I've left on the navigation buttons but also added simple record selectors. You will get an idea by playing with the records and also looking at the forms in design mode.

Get back if you need any help.