Storing Multiple selections in list boxes

I am trying to set up a very basic catelogue system of reference material and
i'm having problems with the filing system.

I have a main form "Item" and in it have a Listbox for the type of item (ie.
magazine, book, cd, electronic, etc.) and want to be able to select multiple
types as some of the references are both electronic and hard copies. At
present the list box looks up the value from a related table "ItemType" but
will not store the selections in the original record.

I have basically no understanding of code writing so i've struggled with
some of the other responses to similar questions on this board. I gather it
cannot simply store from the way i have attempted above but need a real
lamens explination of how i can get around this.

Any help greatly appreciated.

Matt

Sorry that was a bad example but merely used to explain what i was trying to
do with more than one multi selction list box. In fact the categories the
records fall under (ie. topics covered within. There are 18 to select from in
this case.) might be a better example of what i'm trying to achieve where one
report can cover any number of topics.

For this i am unaware of any better way of organising it other than a lot of
check boxes.

Matt

"Klatuu" wrote:

A multi select list box behave a little differently from other controls in
that it does not, on its own, return a value, regardless of whether anything
is selected or not. You have to use the list box's ItemsSelected collection.
It will contain a list of all the selected items. VBA Help has a good
example of how to retrieve data from a multi select list box.

With that being said; however, there is a basic flaw in your system. One
item cannot be two types. It is like saying "That animal over there is a cat
and a dog." Sorry, it is a cat or a dog. You can have animals that are cats
and animals that are dogs. So to say an item can be multiple types means
that you can have a magazine that is an item. Once copy of the magazine may
be hard copy and another copy may be electronic. Therefore, each copy is a
different type.

That means you need two records in your table, one for the hard copy and one
for the electronic copy. Each will have a field in the record identifying
its type. Now we get down to the fact that perhaps a multi select list box
is not appropriate because you should only select one type for a record.


"fordrules01" wrote:

I am trying to set up a very basic catelogue system of reference material and
i'm having problems with the filing system.

I have a main form "Item" and in it have a Listbox for the type of item (ie.
magazine, book, cd, electronic, etc.) and want to be able to select multiple
types as some of the references are both electronic and hard copies. At
present the list box looks up the value from a related table "ItemType" but
will not store the selections in the original record.

I have basically no understanding of code writing so i've struggled with
some of the other responses to similar questions on this board. I gather it
cannot simply store from the way i have attempted above but need a real
lamens explination of how i can get around this.

Any help greatly appreciated.

Matt

I've run into a snag. Tried to follow your instructions as closely as
possible. Have set up tble ItemItemID with 2 primary keys as per below and
set up a relationship between the 3 tables. No problems there.

I then input a continuous subform into the main form but my problem is that
the combo box is still only a drop down list (with the 6 items from ItemType)
and doesn't allow multiple selections.

Is there something i've missed?

Matt



"Damian S" wrote:

Hi Matt,

You say that you want the data to store in the Item record... this means
that an Item can only ever have ONE item type... To handle multiple Item
Types, you will need to set up another table like this:

tblItemItemType
ItemID - Links to ItemID in tblItem
ItemTypeID - Links to ItemTypeID in tblItemType

Have the primary key a composite of the two id's above.

Then, create a subform on your Item form that shows a continuous form with a
Combo Box on it for Item Types. The data source for your subform will be the
tblItemItemType from above. The data source for the combo is tblItemType.
Link the Item form to the Subform via the ItemID field and Bob's your uncle!!

Damian

"fordrules01" wrote:

I am trying to set up a very basic catelogue system of reference material and
i'm having problems with the filing system.

I have a main form "Item" and in it have a Listbox for the type of item (ie.
magazine, book, cd, electronic, etc.) and want to be able to select multiple
types as some of the references are both electronic and hard copies. At
present the list box looks up the value from a related table "ItemType" but
will not store the selections in the original record.

I have basically no understanding of code writing so i've struggled with
some of the other responses to similar questions on this board. I gather it
cannot simply store from the way i have attempted above but need a real
lamens explination of how i can get around this.

Any help greatly appreciated.

Matt

If what you are trying to do is set filtering for a report, here is a
procedure I use for exactly that purpose. In my case, I have 8 different
list boxes from which the user may make multiple selections. This procedure
builds a string that can be used for filtering the report:

Private Function BuildWhereCondition(strControl As String) As String
'Set up the WhereCondition Argument for the reports
Dim varItem As Variant
Dim strWhere As String
Dim ctl As Control

Set ctl = Me.Controls(strControl)

Select Case ctl.ItemsSelected.Count
Case 0 'Include All
strWhere = ""
Case 1 'Only One Selected
strWhere = "= '" & _
ctl.ItemData(ctl.ItemsSelected(0)) & "'"
Case Else 'Multiple Selection
strWhere = " IN ("

With ctl
For Each varItem In .ItemsSelected
strWhere = strWhere & "'" & .ItemData(varItem) & "', "
Next varItem
End With
strWhere = Left(strWhere, Len(strWhere) - 2) & ")"
End Select

BuildWhereCondition = strWhere

End Function


"fordrules01" wrote:

Sorry that was a bad example but merely used to explain what i was trying to
do with more than one multi selction list box. In fact the categories the
records fall under (ie. topics covered within. There are 18 to select from in
this case.) might be a better example of what i'm trying to achieve where one
report can cover any number of topics.

For this i am unaware of any better way of organising it other than a lot of
check boxes.

Matt

"Klatuu" wrote:

A multi select list box behave a little differently from other controls in
that it does not, on its own, return a value, regardless of whether anything
is selected or not. You have to use the list box's ItemsSelected collection.
It will contain a list of all the selected items. VBA Help has a good
example of how to retrieve data from a multi select list box.

With that being said; however, there is a basic flaw in your system. One
item cannot be two types. It is like saying "That animal over there is a cat
and a dog." Sorry, it is a cat or a dog. You can have animals that are cats
and animals that are dogs. So to say an item can be multiple types means
that you can have a magazine that is an item. Once copy of the magazine may
be hard copy and another copy may be electronic. Therefore, each copy is a
different type.

That means you need two records in your table, one for the hard copy and one
for the electronic copy. Each will have a field in the record identifying
its type. Now we get down to the fact that perhaps a multi select list box
is not appropriate because you should only select one type for a record.


"fordrules01" wrote:

I am trying to set up a very basic catelogue system of reference material and
i'm having problems with the filing system.

I have a main form "Item" and in it have a Listbox for the type of item (ie.
magazine, book, cd, electronic, etc.) and want to be able to select multiple
types as some of the references are both electronic and hard copies. At
present the list box looks up the value from a related table "ItemType" but
will not store the selections in the original record.

I have basically no understanding of code writing so i've struggled with
some of the other responses to similar questions on this board. I gather it
cannot simply store from the way i have attempted above but need a real
lamens explination of how i can get around this.

Any help greatly appreciated.

Matt