Problem Using "Multi Select" Feature with a List Box on a Form

[google]

Hello everyone,
I am having an issue using the "Multi Select" option in a list box
in MS Access 2003. I am making a form that users can fill out to add
an issue to the database. Each issue can be associated with multiple
categories. I have an "Issue," "IssueCategory," and
"Category" in the database (among other tables). The form has a
subform in it which is tied to the "IssueCategory" table. The main
form is tied to the "Issue" table. Right now I am able to create
an issue and select multiple categories for one issue. I have to use
the buttons at the bottom of the form to add each individual category
for each issue. I would like to use the "Multi Select" feature so
that I can remove the buttons at the bottom of the subform and user's
will not have to add each category individually, they can just select
all the applicable ones directly from the single list box. When I set
the list box to simple multi select and try to use it in a way that I
just described I get the following error: "Index or primary key
cannot contain a Null value." I get this error regardless of the
number of categories I select. If I select any amount and then
deselect them all I will get this error again. When I don't select
any categories (at all) then I can create a new issue without getting
an error, but each issue must have at least one category. How can I
use the multi select feature and have it add the primary key of the
Issue table to the IssueCategory table for each selection in the list
box (when using the multi select)? Thank you very much for your help,
any tips are appreciated.

- Michael

 

[Douglas J Steele]

How are you referring to the listbox in your code? A multiselect listbox
doesn't have a value: you need to loop through the ItemsSelected collection
to determine what value(s) have been selected (even if only one has)

 

[google]

Doug,
I do not have a reference to the listbox in my code. Is the
ItemsSelected a table, property of a listbox, or something else? How
would I go about determining what items have been selected? Here is a
screen shot of the properties window of the listbox I am trying to get
the multiselect feature to work on:
http://www.uvm.edu/~mcaha/ListBoxSS.gif. The CID is the primary key of
the Category table. It is also a foreign key in the IssueCategory
table, where it is a primary key in conjunction with the IID (primary
key of the Issue table). Thank you very much for your help, it is
definitely appreciated.

- Michael

 

[Douglas J Steele]

ItemsSelected is a collection associated with the listbox.
http://www.mvps.org/access/forms/frm0007.htm at "The Access Web" shows one
way to use that collection.

What do you want to do with the values, though? If they've selected multiple
values, that implies you should be inserting multiple rows into the table.