storing multiple list selections in access

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 ^^
0
Galliard
1/19/2008 12:08:14 AM
access 16762 articles. 3 followers. Follow

7 Replies
1013 Views

Similar Articles

[PageSpeed] 1

On Fri, 18 Jan 2008 16:08:14 -0800 (PST), Galliard <Galliard5@gmail.com>
wrote:

>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]
0
John
1/19/2008 12:49:07 AM
On Jan 18, 4:49=A0pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Fri, 18 Jan 2008 16:08:14 -0800 (PST), Galliard <Gallia...@gmail.com>
> wrote:
>
> >i have 2 tables, table a and table b. =A0is 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. =A0i'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 man=
y
> spells; each spell can be mastered by many characters. The proper way to d=
o
> this is to have THREE tables:
>
> Characters
> =A0 CharacterID <Primary Key>
> =A0 CharacterName
> =A0 <other info about the character as an entity, e.g. species (Human, Orc=
,
> Elf), EthicalNature (Lawful Good, Chaotic Evil), etc.>
>
> Spells
> =A0 SpellID <Primary Key>
> =A0 Description <text>
>
> Masteries
> =A0 CharacterID <who has mastered this spell>
> =A0 SpellID <what did they master>
> =A0 <other info about this character's mastery of this spell, such as leve=
l of
> skill>
>
> You would use a Subform to insert as many spells as needed for each charac=
ter.
>
> See the Northwind sample database Orders form for an example - the paralle=
l
> would be Orders =3D Characters, Products =3D Spells, OrderDetails =3D Mast=
eries.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0John 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?
0
Galliard
1/20/2008 6:01:52 PM
On Sun, 20 Jan 2008 10:01:52 -0800 (PST), Galliard <Galliard5@gmail.com>
wrote:

>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]
0
John
1/20/2008 9:19:07 PM
John W. Vinson wrote:
> On Sun, 20 Jan 2008 10:01:52 -0800 (PST), Galliard <Galliard5@gmail.com>
> wrote:
> 
> 
>>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]

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
MPAPoster@FortuneJames.com
0
James
1/21/2008 4:46:29 AM
On Sun, 20 Jan 2008 23:46:29 -0500, "James A. Fortune"
<MPAPoster@FortuneJames.com> wrote:

>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]
0
John
1/21/2008 5:49:04 AM
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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:4ge7p31ndob2b21762l2jt5nfp2d93qgs2@4ax.com...
> On Sun, 20 Jan 2008 10:01:52 -0800 (PST), Galliard <Galliard5@gmail.com>
> wrote:
>
>>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] 


0
Rob
1/21/2008 6:47:01 AM
On Mon, 21 Jan 2008 17:47:01 +1100, "Rob Parker"
<NOSPAMrobpparker@optusnet.com.au.REMOVETHIS> wrote:

>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]
0
John
1/21/2008 5:15:42 PM
Reply:

Similar Artilces:

How to select series in chart?
I know I asked this question before, but (sigh) I cannot find the answer now, when I need it of course. How can I select a series in an Excel chart (XY Scatter) using the keyboard, not the mouse? The issue is: I have overlapping series, so it is difficult for me to select a series by moving the mouse cursor to a point in one series and right-clicking it, as I normally do. Someone once mentioned a ctrl and/or shift key combination (I think) that would allow me to select each series explicit in round-robin fashion. That is what I am looking for again. More generally, how could I have found...

multiple flyers per page?
Hello. I need to make a custom template with publisher. It will be one 8 1/2 by 11 paper divided horizontally into three parts so that there are three fliers that can be printed. each flier needs to be double printed so I need to make it a double sided project like a postcard or brochure layout. I can figure out text and image stuff. But I don't know how to make a basic custom project that is divided into three panels horizontally (so 8 inches by 3.83 inches roughly) Also an additional customization would divide each 3.83 by 8 inch flyer into two parts so one 8 by 11 page would ...

Business Portal Error-SQL server does not exist or access denied
Hi, We are running business portal 4.0 for one of our customer. It was running correctly, however, they have changed the SQL server port (previously it was set as default 1433). After that the business portal becomes very slow and while creating a new request (purchase requisition) if we open the item pop up; it is showing exception "SQL Server does not exist or access denied...." Can any body tell me how can I provide the new port number to business portal connection to the database. Thanks and Regards, Waliullah, Thanks for using the newsgroups. I have a...

What's happened to the findfirst function in Access 2010?
I have a working Access 2007 Application which is now failing miserably in 2010. It hinges on location the first available working document in a table runing the following VBA code: With Me.Recordset .FindFirst "([fld1] + [fld2]) = 0" If .NoMatch Then .FindLast "([fdl1] + [fld2]) <> 0" Exit Sub End If .... FURTHER PROCESSING ... What must I do to correct this? Go through a record by record search? End With In 2007, this works correctly, stopping at the appropriate record (approc. rec 1385 in the recordset...

Removing multiple users from Outlook
We did some computer switching, which involved uninstalling a copy of Office XP and replacing it with a copy of Office 2003. Now when I open Outlook, it makes me choose a user. This wasn't in the .pst file which was transferred for the other computer, but apparenly it was on this computer someplace. How can I get Outlook to simply open for the one account without that dialog box popping up? Aloha Annie, Control Panel | Mail | Show Profiles...remove any profiles you don't want. Select the one you do want and set it as the default. (if there are more than one left) -Ben-...

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Multiple stacked bar charts
I have a project where i have to chart the compliance of three groups over a number of months. So for each data set there is a Compliance#1 stacked upon NonCompliance#1, then Compliance#2 stacked upon NonCompliance#2 and finally, you guessed it, Compliance#3 stacked upon NonCompliance#3. When i design a chart in excel it wants to place everything on top of each other producing one column of six not the required three columns of two for each week in the display. Can anyone point me i the right direction for this one?? Hi you can achieve this effect by having two layers in your x axis the at...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Access 2007 and Vista code problem
I am developing a database on a XP SP2 machine. On a form I have created some buttons with event procedures that run 2 queries and open a form. I sent the db to someone running Access 2007 on a Vista machine and nothing happens when she clicks the buttons and there are no messages. I know nothing about Vista so thanks in advance for any help. Tim Might be a reference problem, have them open the open a code window and click tools references. See what says missing and tell them what should be there, Add it and it should work. Duff "Tim Reid" <TimReid@discussions.micr...

Can't insert Char(1) into SQL Server from Stored Procedure
Hello I am trying to insert a char(1) field into a table from an ASP.Net/C# application. At first I was inserting rows into the table but none of the columns that I was inserting the rows into were char(1) columns, they were mostly int and varchar columns. Then I found out that there was one column in the list that was not supposed to be an int column but instead it was supposed to be one of the char(1) columns into the table. So I replaced the int column in the list with the char(1) column and since the value of the char(1) column resulted from a CheckBox ('Y...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Multiple Simultaneous Outlook Sessions
Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous Outlook Sessions open at one time? If so, how is it setup to send and receive from the same profile I would choose and save the replies back into the same profile? thx "Art" <Art@discussions.microsoft.com> wrote in message news:433A0AAA-9DB0-4411-954F-5165374B049D@microsoft.com... > Is there a way for Outlook 2007 or 2003 to have multiple Simultaneous > Outlook > Sessions open at one time? If so, how is it setup to send and receive from > the same profile I would choose and sa...

Single click selects multiple cells
When clicking on a single cell multiple cells are selected. The one time solution for this is to zoom in or out. This is problematic as 60% seems to be the zoom that works most of the time but at this zoom level the cell contents do not display. The time lost and the frustration that builds is killing my productivity and office attitude. Please give all of us a permanet fix. -- Thanks Mike ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" butt...

Multiple copies of email in "Forward to"contact
HIe, I am running Exchnage 2003. My CEO has a conatct in our organisation that refer to his blackberry contact. I have set the forwarding to also keep a copy in his mailbox. What happens is that three copies of his forwarded email hit his blackberry account! Only one hits his internal mailbox. What I ahve determined is that the front end Exchange server seems to be creating the additional copies. Is this how its meant to be (don think so cos its such a nuisance and my CEO keeps complaining about it). How do I tell the front end server that this guy's mail server is the one that...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Comments to print on invoices and packing lists
We have a client with a specific need for printing customer comments and item comments: 1) They want to store comments on Customer Master. When a sales order is entered these comments should default on the sales order with an option to change. They want to mark these comments to be printed on sales order, invoice, packing list and pick ticket. 2) Items are stored with item specific comments. These comments need to come to the sales order when the order is entered with an option to change. They want to setup comments with similar setup where they print on order, invoice, pick ticket and packi...

Associating Multiple Existing Contacts to an Account, Until CRM 2.x
I have racked my pea-sized brain for endless days for a way to associate multiple existing contacts (or accounts) to an account. My client has accounts that have many contacts (or accounts) involved at different stages of acquiring a new account. The logical method would be to add a custom attribute with lookup functionality into the existing CRM contact or account objects but this is not possible with CRM 1.x. The resulting UI would allow an end user to associate different contacts (or accounts) to an account. Example: Account: ABC - AccountPhysician: (existing account or con...

printing multiple copies of Sigel GmbH
I have made a tent card in a small size of 5.588cm x 4.191cm for a swing tag, using Sigel GmbH in the "other labels" section. It prints one copy perfectly, leaving room on the page for at least about another 5 copies (which is what I want), but when in the print page section it doesn't give me the option to print multiple copies on the page. This will be an enormous waste of paper if I have to print the number of copies individually on each page. HELP! -- candere What is the product number for this label? I cannot find this size label on my 2007 version. -- Mary Sau...

OLK 2k7
Outlook is behaving strangly with the "through the selected account" option. Each time I restart Outlook the rule fails. When I go in to check on the rules I get told that the rule is "invalid". and the "SELECTED" account is no longer selected. Each time the criteria the account needs to be selected by changes. For example with the following data Account Name Email Account mailserver.domain1.com user@domain1.com mailserver.domain2.com user@domain2.com One time I go in and it's asking me to select the account ...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

Getting rid of selection
How can I get rid of the selection rectangle? It seems that it's always there, with a heavy black rectangle, or there's a light black rectangle marking where it was. I'm trying to get rid of it altogether, so I can capture an image of the sheet for use in a webpage. I can achieve the effect that I want by selecting a cell which is outside the area that I'm trying to capture, but now that I've found that I cannot get rid of it entirely, it is driving me nuts trying to do so. -- Steve Swift http://www.swiftys.org.uk/swifty.html http://www.ringers.org.uk You could al...

LDAP Write access?
My ldap server allows Write access to entries - and a few clients now support this. Any plugins available for Outlook to allow this too? Thanks None that I'm aware of. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Outlook Tips by email: dailytips-subscribe-request@lists.outlooktips.net Subscribe to Exchange Messaging Outlook newsletter: EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM Outlook Tips: http://www.outlook...

MS Access 2007 code not running
I have created a brand new database in Access 2007 and linked some VB code to run on Form_Load. This code worked perfect with no problems the day I created it. Now today I opened the same form and it is not running the Form_Load code anymore (and yes it is still there, I did not delete it). I got frustrated so I copied and pasted the code to Form_Current and several On_Change events and set breakpoints at the beginning of all the events just to see if it was the Form_Load that was not triggering, but NO events are triggering my VB code. Any ideas? See this page: http://accessjunkie.com/fa...

unable to grant existing user access to TEST company
Set up test company using live company data; ran the script referenced in CustomerSource article ID#871973; can grant and remove user access in all other companies; ran grantsql 9.2; the dexsql log shows the user already has access probably because of using the other company data for the test; I can set up a new user and grant them access, existing users who already have access can view the company...but, I cannot figure out how to grant access to the TEST company to an existing user. The error popup reads "The user could not be added to one or more databases." Dynamics 9.0,...

Selecting the Right Text Alignment for a edit box doesn't work
When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong Thanks Dan Dan, "Dan" <anonymous@discussions.microsoft.com> a �crit dans le message de news:DECFE605-A130-416B-9924-60BA0C79D684@microsoft.com... > When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong? > I've no idea :-))) You can open your RC-file as text, and make sure it has the ES_RIGHT style set, thus: EDITTEXT IDC...