Multiple select on forms

[Mark]

Hi,
I am creating a contacts DB. Each contact falls within a
set list of categories (eg, Press, Supplier, Employee
etc.). A contact may fall within one or more categories.
I have created a table for Contacts with ContactID(pk),
name, address etc. and a table for Category with
CategoryID(pk), categoryName, description etc.
I have linked these with a join table to create a many to
many relationship.
How do I create a form where I can select one or more
categories for each contact (by category name)?
Also how do I create a query that will pull out all of
the contacts within one of the categories?
Cheers,
Mark (newbie)

 

[Sandra Daigle]

Hi Mark,

The easiest way to do this is with a subform on either (or both) of the
forms Contact and Category. For example, the main form for Contact would
have a subform for ContactCategories - with a combo box allowing you to
select Categories for the contact. Then on The main form for Category you
could have a subform for Contacts which would allow you to select contacts
that fit this category.

On my website (http://www.daiglenet.com/msaccess.htm) I have an example of
this and another method which uses multi-select listboxes. Look for the
'SelectRecords' example.

 

[Mark]

Many thanks Sandra - that helps enormously. I think for
my purposes the multi-select list boxes are ideal.
It makes the user input much easier. I'm a bit of a
newbie so the code bit on the click event is a bit
daunting - any advice?
Also, how would I go about creating a report that listed
all of the contacts in a certain category?
Many thanks,
Mark.

-----Original Message-----
Hi Mark,

The easiest way to do this is with a subform on either (or both) of the
forms Contact and Category. For example, the main form for Contact would
have a subform for ContactCategories - with a combo box allowing you to
select Categories for the contact. Then on The main form for Category you
could have a subform for Contacts which would allow you to select contacts
that fit this category.

On my website (http://www.daiglenet.com/msaccess.htm) I have an example of
this and another method which uses multi-select listboxes. Look for the
'SelectRecords' example.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,
I am creating a contacts DB. Each contact falls within a
set list of categories (eg, Press, Supplier, Employee
etc.). A contact may fall within one or more categories.
I have created a table for Contacts with ContactID(pk),
name, address etc. and a table for Category with
CategoryID(pk), categoryName, description etc.
I have linked these with a join table to create a many to
many relationship.
How do I create a form where I can select one or more
categories for each contact (by category name)?
Also how do I create a query that will pull out all of
the contacts within one of the categories?
Cheers,
Mark (newbie)

.

 

[Sandra Daigle]

Hi Mark,

This code is a bit more advanced . . . but hopefully not impossible to
grasp -

Here it is again -

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from tblPersonClasses where PersonNbr="
& Me.PersonNbr, , dbAppendOnly)
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("PersonNbr") = Me.PersonNbr
.Fields("Classid") = Me.lstAvailable.ItemData(varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery

End Sub

Basically all it is doing is looping through the collection of selected
items in the first listbox and using that data to add records to the
"PersonClasses" table (which is the junction table in this example). The
This code uses a recordset which is opened by a simple query. The recordset
allows us to add new record and assign values to the fields in the new
record. When the record is complete, the recordset is updated (which commits
the value to the table) and then we move on to the next member of the
collection. When all selected items have been processed, we close the
recordset and destroy the object variables (rst and db) which were used in
the process.

Last but not least, we requery the two listboxes so that the changes are
reflected on the form.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Many thanks Sandra - that helps enormously. I think for
my purposes the multi-select list boxes are ideal.
It makes the user input much easier. I'm a bit of a
newbie so the code bit on the click event is a bit
daunting - any advice?
Also, how would I go about creating a report that listed
all of the contacts in a certain category?
Many thanks,
Mark.

-----Original Message-----
Hi Mark,

The easiest way to do this is with a subform on either (or both) of the
forms Contact and Category. For example, the main form for Contact would
have a subform for ContactCategories - with a combo box allowing you to
select Categories for the contact. Then on The main form for Category you
could have a subform for Contacts which would allow you to select
contacts that fit this category.

On my website (http://www.daiglenet.com/msaccess.htm) I have an example
of this and another method which uses multi-select listboxes. Look for
the 'SelectRecords' example.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Hi,
I am creating a contacts DB. Each contact falls within a
set list of categories (eg, Press, Supplier, Employee
etc.). A contact may fall within one or more categories.
I have created a table for Contacts with ContactID(pk),
name, address etc. and a table for Category with
CategoryID(pk), categoryName, description etc.
I have linked these with a join table to create a many to
many relationship.
How do I create a form where I can select one or more
categories for each contact (by category name)?
Also how do I create a query that will pull out all of
the contacts within one of the categories?
Cheers,
Mark (newbie)

.

 

[Guest]

Many thanks Sandra.
Lots to do...

-----Original Message-----
Hi Mark,

This code is a bit more advanced . . . but hopefully not impossible to
grasp -

Here it is again -

Private Sub cmdAddOne_Click()
Dim varItem As Variant
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()
Set rst = db.OpenRecordset("Select * from

tblPersonClasses where PersonNbr="

& Me.PersonNbr, , dbAppendOnly)
For Each varItem In Me.lstAvailable.ItemsSelected
With rst
.AddNew
.Fields("PersonNbr") = Me.PersonNbr
.Fields("Classid") = Me.lstAvailable.ItemData (varItem)
.Update
End With
Next varItem
rst.Close
Set rst = Nothing
Set db = Nothing
Me.lstAvailable.Requery
Me.lstSelected.Requery

End Sub

Basically all it is doing is looping through the collection of selected
items in the first listbox and using that data to add records to the
"PersonClasses" table (which is the junction table in this example). The
This code uses a recordset which is opened by a simple query. The recordset
allows us to add new record and assign values to the fields in the new
record. When the record is complete, the recordset is updated (which commits
the value to the table) and then we move on to the next member of the
collection. When all selected items have been processed, we close the
recordset and destroy the object variables (rst and db) which were used in
the process.

Last but not least, we requery the two listboxes so that the changes are
reflected on the form.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Many thanks Sandra - that helps enormously. I think for
my purposes the multi-select list boxes are ideal.
It makes the user input much easier. I'm a bit of a
newbie so the code bit on the click event is a bit
daunting - any advice?
Also, how would I go about creating a report that listed
all of the contacts in a certain category?
Many thanks,
Mark.

-----Original Message-----
Hi Mark,

The easiest way to do this is with a subform on either (or both) of the
forms Contact and Category. For example, the main form for Contact would
have a subform for ContactCategories - with a combo box allowing you to
select Categories for the contact. Then on The main form for Category you
could have a subform for Contacts which would allow you to select
contacts that fit this category.

On my website (http://www.daiglenet.com/msaccess.htm) I have an example
of this and another method which uses multi-select listboxes. Look for
the 'SelectRecords' example.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Mark wrote:
Hi,
I am creating a contacts DB. Each contact falls within a
set list of categories (eg, Press, Supplier, Employee
etc.). A contact may fall within one or more categories.
I have created a table for Contacts with ContactID (pk),
name, address etc. and a table for Category with
CategoryID(pk), categoryName, description etc.
I have linked these with a join table to create a many to
many relationship.
How do I create a form where I can select one or more
categories for each contact (by category name)?
Also how do I create a query that will pull out all of
the contacts within one of the categories?
Cheers,
Mark (newbie)


.

.