storing multiple list selections in access

[Galliard]

i have 2 tables, table a and table b. is it possibile for a control
bound to table a (a list box, with multiple selections eneabled) to be
able to select multiple records from table b, and store them?
i.e. table A record 1 has table B records 1,2,3,4,8,12, and so on
selected
while Table A record 2 only has table B records 3, 8 and 15 selected,
and so on..
is there some way to store those selections within table A?

for those curious, i'm making a character sheet for an RPG.. table A
is the actual sheet, while B is the list of spells.. i'm looking for a
way to store which spells each character has on the sheet(A), using
the spell table (B) as the reference. i'm looking at upwards of 50 or
so selections as well, so using multiple fields IE spell1, spell2,
spell3... is possibile, its not practical to do...

also, some way to put it to a report would also be handy.. i have it
set up so that i can print A and B fine, but i'm looking for a way to
print A, followed by B, but only the selections from B that were
picked in A

assistance and ideas much appreciated ^^

 

[John W. Vinson]

i have 2 tables, table a and table b. is it possibile for a control
bound to table a (a list box, with multiple selections eneabled) to be
able to select multiple records from table b, and store them?
i.e. table A record 1 has table B records 1,2,3,4,8,12, and so on
selected
while Table A record 2 only has table B records 3, 8 and 15 selected,
and so on..
is there some way to store those selections within table A?

No, and a multiselect listbox cannot be bound.

for those curious, i'm making a character sheet for an RPG.. table A
is the actual sheet, while B is the list of spells.. i'm looking for a
way to store which spells each character has on the sheet(A), using
the spell table (B) as the reference. i'm looking at upwards of 50 or
so selections as well, so using multiple fields IE spell1, spell2,
spell3... is possibile, its not practical to do...

also, some way to put it to a report would also be handy.. i have it
set up so that i can print A and B fine, but i'm looking for a way to
print A, followed by B, but only the selections from B that were
picked in A

assistance and ideas much appreciated ^^

This is a classic many to many relationship. Each character can master many
spells; each spell can be mastered by many characters. The proper way to do
this is to have THREE tables:

Characters
CharacterID <Primary Key>
CharacterName
<other info about the character as an entity, e.g. species (Human, Orc,
Elf), EthicalNature (Lawful Good, Chaotic Evil), etc.>

Spells
SpellID <Primary Key>
Description <text>

Masteries
CharacterID <who has mastered this spell>
SpellID <what did they master>
<other info about this character's mastery of this spell, such as level of
skill>

You would use a Subform to insert as many spells as needed for each character.

See the Northwind sample database Orders form for an example - the parallel
would be Orders = Characters, Products = Spells, OrderDetails = Masteries.

John W. Vinson [MVP]

 

[Galliard]

No, and a multiselect listbox cannot be bound.




This is a classic many to many relationship. Each character can master many
spells; each spell can be mastered by many characters. The proper way to do
this is to have THREE tables:

Characters
  CharacterID <Primary Key>
  CharacterName
  <other info about the character as an entity, e.g. species (Human, Orc,
Elf), EthicalNature (Lawful Good, Chaotic Evil), etc.>

Spells
  SpellID <Primary Key>
  Description <text>

Masteries
  CharacterID <who has mastered this spell>
  SpellID <what did they master>
  <other info about this character's mastery of this spell, such as level of
skill>

You would use a Subform to insert as many spells as needed for each character.

See the Northwind sample database Orders form for an example - the parallel
would be Orders = Characters, Products = Spells, OrderDetails = Masteries.

             John W. Vinson [MVP]

that worked almost exactly how i wanted it ^^

but is there a controlout there, or a way to make one, that looks
similar to the field selection controls in the wizards? the one with
to list boxes and the set of arrows between them that moves the
selections back and forth?

 

[John W. Vinson]

but is there a controlout there, or a way to make one, that looks
similar to the field selection controls in the wizards? the one with
to list boxes and the set of arrows between them that moves the
selections back and forth?

You'll need to program that yourself in VBA code, using the arrow buttons to
change the two listbox's RowSource queries and requery them. I suspect the
code to do so is out there somewhere but I can't quickly find it.

John W. Vinson [MVP]

 

[James A. Fortune]

You'll need to program that yourself in VBA code, using the arrow buttons to
change the two listbox's RowSource queries and requery them. I suspect the
code to do so is out there somewhere but I can't quickly find it.

John W. Vinson [MVP]

In:

http://groups.google.com/group/comp.databases.ms-access/msg/26b8d590580190ca

I posted a link to TabOrder.zip. TabOrder.zip has a form called
frmTabOrder that contains a subform and a listbox that send items to
each other when an item is clicked. The Click code can be moved to
command buttons with bitmaps of arrows applied and can be modified to
use two listboxes. Each button should make sure that item(s?) have been
selected in the appropriate listbox before attempting the move.

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

In:

http://groups.google.com/group/comp.databases.ms-access/msg/26b8d590580190ca

I posted a link to TabOrder.zip. TabOrder.zip has a form called
frmTabOrder that contains a subform and a listbox that send items to
each other when an item is clicked. The Click code can be moved to
command buttons with bitmaps of arrows applied and can be modified to
use two listboxes. Each button should make sure that item(s?) have been
selected in the appropriate listbox before attempting the move.

Many thanks, James!

John W. Vinson [MVP]

 

[Rob Parker]

Helen Feddema wrote an article on this topic in a recent Access Watch
column. The article, and an example Access database with code to swap items
between two listboxes, is available from her website via the following link:

http://www.helenfeddema.com/Files/accarch172.zip

HTH,

Rob

 

[John W. Vinson]

Helen Feddema wrote an article on this topic in a recent Access Watch
column. The article, and an example Access database with code to swap items
between two listboxes, is available from her website via the following link:

http://www.helenfeddema.com/Files/accarch172.zip

HTH,

Thanks, Rob - bookmarked.

John W. Vinson [MVP]