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. 2 followers. Follow

7 Replies
612 Views

Similar Articles

[PageSpeed] 39

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:

Exch 2003 store.exe terminates when ESM Replication Status selected
Exch 2003 SP1 on W2K SP4 - had been 5.5 until moving to Exch 2K swing server. 5.5 removed and Exch 2003 SP1 installed, seemingly OK. Weird problem - if I click on ESM->Administrative groups-> group ->Servers-> server->First Storage Group->Public Folder Store(server) and then click on Replication Status, after a delay I recieve a popup saying, "An error occurred while retrieving the requested information. The Microsoft Exchange Information Store service is not running or the Microsoft Exchange Server computer is unavailable. ID no: c103047d Exchange System Manager...

List command buttons in a form
Hi, I have a lot of command buttons within a form and in the future there could even be more. The name of this command button needs to be created dynamically. I could to this with relative easy by manually programming something in VBA for every command button. However it would be a lot more efficient if i could dynamically create a list of the the command buttons of the form. And then for each element get the name. Does anyone know how you can get a list of all command buttons from a form within VBA? You can use the following to loop through all the control of your form Dim ctl as Control...

multiple journal mailboxes ex2003
Hi, I was wondering if anyone could provide any input on why you would consider having multiple journal mailboxes, in a scenario that requires journaling. The reasons I can think of are: 1. Geographic dispertion of exchange servers, seperated by slow WAN links 2. A single journal mailbox on a dedicated server not having the processing power to handle all journaled messages. Thanks, James Your WAN links would have to be saturated or you would have to have a large volume for this to be a real issue. Of course you would probably want to make sure the journaling mailbox was close to t...

Address List #8
Have a couple of questions about Address list in W2k3 AD. We have OU setup by department, with our current Address list setup same way. Is the Address list dynamic? meaning if I add a OU with it show up in the Address list automaticlly? If not and I setup an Address list for the department, then I would need to filter what users I want in that list or will it pickup all users under that OU/ Thank in advance Rick Address Lists are dynamic - they are nothing but a view of recipients in AD. As and when new recipients are added, Address Lists will show those recipients if they're returne...

Access can't find form "frmWarning" in macro or code
The error message is actually a little longer than the subject. The form name is referred to in code as Forms("frmWarning"). The name is NOT misspelled. and it is a valid form name. "Kruppy" <Kruppy@discussions.microsoft.com> wrote in message news:331E7802-56F0-40AE-8FEE-4194486F0CB7@microsoft.com... > The error message is actually a little longer than the subject. The form > name > is referred to in code as Forms("frmWarning"). The name is NOT misspelled. > and it is a valid form name. Is the form open? Only open forms are mem...

Stored Procedure error message
Hi, I have written scipt in Dex to create tables. After I have added chunk file, tables are creating properly. But the issue is after login into Great Plains I am getting the following error "The Stored Procedure GrantAccess() of form Sql maintenance : 69: pass through Sql returned the following results :DBMS :15151, Microsoft Dynamics GP:0" Any Idea Please... Jack Hi Friends, I got the solution my slef and issue has resolved.. Thanks, Jack "Jack" wrote: > Hi, > > I have written scipt in Dex to create tables. After I have added chunk file, > table...

Sequential Numbering Set-up for Multiple Sheets in a Workbook
How do I set up the excel file to sequentially number multiple sheets within a workbook I have a saved file with LTD,STD and Life details. When I create a "custom footer" and select "& pages" it does not sequentially number each sheet as a different page number It sounds like what you want is to print all the sheets together, with the footer saying Page 1, Page 2, etc. To do that, select all the sheets and print. It will then do the page numbering for you. You do need to have the footer on all of the sheets to be able to see it. >-----Original Message----- &g...

Multi Store Vouchers
I do not see a way for this to work. I want to be able to give out store credit to our customers but there does not seem a way to make this work for multiple stores. In a single store setup everything is stored in the Voucher table. These vouchers are uploaded during the HQ synchronization but each store does not have access to other stores voucher's. Does anyone have any insight on how I could get this resolved? Thanks, Dan I take it you are trying to issue Store Credit for returns, not actually trying to implement a voucher system for gift cards/certificates. If this is corre...

Multicurrency costing, list price
I have a new client using multicurrency. Their functional currency is Canadian dollar Below is his question... How do I record standard costs is USD when base currency is CAD? I need to report on the purchase variance and our standards are in USD as well as our purchases are based in USD. I'm not sure even where to start with multicurrency and inventory pricing, costing, etc. I know on the item you can assign a currecny ID to the List price but not to cost. So, will he use the PO to enter US currency ID when purchasing items? Thanks for any help. TracyM I have never tried this ...

excel list
want to put a guest list in alphabetical order by last name in column A. i.e. Mrs. John Doe.. Try this formula in B1 and fill down. Then sort on column B: =MID(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN (SUBSTITUTE(A1," ",""))))+1,255) HTH Jason Atlanta, GA >-----Original Message----- >want to put a guest list in alphabetical order by last >name in column A. i.e. Mrs. John Doe.. >. > ...

cell selection problem
Yesterday my excel 2003 started to have this problem. If I select a cell, my mouse cursor goes into selection mode and I can't get out of this mode. I have hit keys, tried to put the mouse on the title bar of the window to minimize the app (and I end up selecting all the rows above the current row). The only thing I can do is alt-tab to the next application, and close excel from the taskbar window. I must have done something to start this behavior, I just can't figure out what. Hi try hitting F8 ´to disable the extended mode -- Regards Frank Kabel Frankfurt, Germany "...

Using DAO when have two decode in select not returning data
I am on Office XP with Excel 2002 using DAO. I have a sql statement which pulls 12 fields (the last two use decode). When I copy the recordset to the excel spreadsheet it is not pulling in the last field. However, when I qualify the first decode with a name then it pulls in the last field. This does not make sense that I need to do this. I am trying to find out if anyone else has had this problem. ...

Adding Multiple Worksheets
Hi Guys, I hope someone can solve this one for me. I have 6 worksheets (which will grow to about 20) which I want to add together to form a total in my totals worksheet. So I have:- Wk1 Wk2 Wk3 Wk4 Wk5 Total The formula I have used in my totals worksheet is:- =SUM('Wk1:Wk6'!C5:F200) This works but the total is double, so if I put 1 in Wk1 as a test, I get 2 in the total page. I think it's something to do with the fact that's it's adding itself again but I'm not sure why. Can someone help me and show me the light!? Thanks a lot. Is the total worksheet outside ...

How can I host multiple domain with single EX2003?
Dear all, As titled... Should I configure the server to bind another IP address to host another domain??? If yes, it's undoubtable to waste IP address.... Please help, thanks.... You mean multiple SMTP domains? If so, check out this article, which applies to E2k3 as well: http://www.msexchange.org/tutorials/MF010.html -- Neil Hobson Exchange MVP For Exchange news, links and tips, check: http://www.msexchange.co.uk "Keith Ko" <Unavailable@127.in-addr.arpa> wrote in message news:uB3Yl9w5DHA.360@TK2MSFTNGP12.phx.gbl... > Dear all, As titled... > > Should ...

Query to check a field and select fields from a table
I have 2 tables (tServers and tTimezone) with the following structure: tServers: servername city tTimezone: city time1 time2 My query need to check if [tServers].servername is like "*DOWD*" and return a field "time" populating with [tTimezone].time2. I joined both tables using city. "Rodrigo Borges" <rsborges@hotmail.com> wrote in message news:D955F76E-0CCD-4B16-8E01-B7768189A119@microsoft.com... >I have 2 tables (tServers and tTimezone) with the following structure: > > tServers: > servername > city > > tTimezone: > city >...

How do I stop the Commands from disappearing from Quick Access too
..Added commands to the quick access toolbar at work - next day they were gone Using Excel 2007. So, how do I stop the How do I stop the Commands, I added, from disappearing from Quick Access toolbar -- SherylAnne ...

Round up or down to a multiple of 5
I have a question. Im trying to come up with a formula that should say: the total of a whole number times 1.15 and then round it off to either a 0 or a 5. Can someone please help me! =ROUND((A1*1.15)/5,0)*5 -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "latincutiepie" <u41871@uwe> wrote in message news:80b89e14f2680@uwe... >I have a question. Im trying to come up with a formula that should say: > > the total of a whole number times 1.15 and then round it off to either a 0 > or > a 5. > > Can some...

Multiple email to list
Good day I need to send monthly newsletters to about 8000 students. How could I go about it if : - I were to use Outlook 2007 - need to ensure that the names on the list are not accesibel by others on the list (email addreesses not broadcast to all - low spam count so thaty I could have a high delivery count. - my DNS wil not be marked as a source of spam Thank you in advance -- !ngeniuos (99% Perspiration) You can use a mail merge for that; http://www.howto-outlook.com/howto/mailmerge.htm Whether or not you'll get marked as a spammer highly depends on your ISP ...

Picklist Value displayed is not an option in the list.....weird.
Hi, I have a picklist value for a new opportunity that shows the Status Reason is "341" for the value. I have no idea how it got there, but it is there. I cannot seem to get rid of it. NOTE: This only occurs on converted lead --> opportunity, not opportunity created from scratch. Opportunity from Converted Lead Status Code = "341", but Opportunity created on its own, no issues, Status Code = "New", What do I do?????? Here are some background / additional notes: I made cutomizations to a DEV version of CRM for a client. When I attempted to import ...

Looking for Access expert in Phoenix for paid work
I need assistance with enhancing my small work database used for contract tracking. Would like to find someone locally who can assist. Please contact me at 602-381-9655. Thank you. mela, This newsgroup is to assist those wanting to do the work themselves. Your request would do better posted http://www.utteraccess.com/forums/postlist.php?Cat=&Board=34 When you do post it might also be helpful to give a brief synopsis of what you want done. You might also check your local Universities and Colleges, there might be a *board* you can post to. -- Gina Whipp ...

HQ/Store date mismatch
We just deployed our 3rd store and eveyrthing was working fine, transactions were being uploaded and everything. Then, the store ops DB computer got moved, and I guess at some point someone changed the date on the computer to be a month in advance. Now, all of our transactions from 4/25/2006 to 4/27/2006 are actually in the DB as 5/25/2006 to 5/27/2006. I just discovered the problem and changed the date on the Store Ops machine, but I don't know what the best way to fix these transactions is, or if it is even possible. What I'd like to do is go change all of these transac...

Outlook XP autofill To field
Outlook XP ... When typing in names in the To field of new messages Outlook will start to show names that have either already been typed, sent to or appear in the Contact list. Where is this "autofill" list kept? Is it user accessible? I'd like to get rid of some of the names that are not already in my Contacts. You can just use the down arrow key to highlight a name that is incorrect or whose address has changed and press delete. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding ...

Outlook 2000, multiple inboxes
When I load up my Outlook 2000, it starts up to the inbox which is fine. If I click send/receive, it opens up another inbox screen with the same emails as in the first inbox. Same thing when clicking send/receive even more times. This has been happening for about 5 days. Any ideas on what is causing this? On Tue, 24 May 2005 02:55:06 GMT, maryjersen55@home.net (Mary J.) wrote: >When I load up my Outlook 2000, it starts up to the inbox which is >fine. If I click send/receive, it opens up another inbox screen with >the same emails as in the first inbox. Same thing when cl...

Average Sales per Store
Need help modifying the RMS "Average Sales per Store" report that is found in CustomerSource. Would like to add filters for Department and Category if possible. I have tried to modify it myself, but I'm a beginner at this and I keep coming up short. One other thing that may be useful is if it can summarize by date also - would be nice to see how the values change over the course of a week or so. "Jason Kelton" wrote: > Need help modifying the RMS "Average Sales per Store" report that is found in > CustomerSource. Would like to add filters for D...

Mailing List SW for Exchange?
Hello All, Does anyone know of any third-party software for an Exchange 2003 server that would let me run mailing lists? I'm looking for something that is fairly easy to add users, as well as something that gives users ability to auto subscribe, remove, etc, etc.. (Basically the "standard" mailing list features.) Is this something that I should look for on Exchange, or should I just another application altogether? My intent is that the users / subscribers would be external, not part of my AD. Thanks, Brian Check slipstick.com -- David Sengupta M.T.S., B.Sc., MVP, MCSE, M...