Results 1 to 5 of 5
  1. #1
    TheChairman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    16

    Mass populating the compound primary key of a table with data entered from a form

    Hello, this is my first posting on this forum. My situation is this:

    I have two tables that track contacts and events, respectively. These are tblContacts and tblEvents. The primary key for each is a multi-character alphanumeric code txtContactID and txtEventID.

    My client wants to track which events each contact has attended and which contacts are at a particular event (i.e. a many-to-many relationship). I have constructed a junction table tblEventParticipants, with two fields consisting of a particular Contact ID and a particular Event ID. This constitute a composite primary key. So, for instance, John Smithson at the Beethoven Festival would be one record. John Smithson at the Mozart Festival would be another record. Jenny Stevens at the Mozart Festival would be a third.

    Because we have so many contacts and events, though, it would be impractical to have a form in which the data entry person would have to click through each name and associate with an event. What I am envisioning for a data entry form is the following:

    1.) A combo box populated with the records in tblEvents and linked to txtEventID. The user selects a particular event from this.

    2.) A list box populated with all the records in tblContacts. The user could select multiple contacts with Ctrl-Click

    3.) A second list box that will be populated with the contacts selected from the first list box.

    4.) A submit button which, when clicked, will populate the junction table tblEventParticipants. Each record will have the Event ID selected from the combo box and the Contact ID of one of the names selected from the second list box. Because we're dealing with composite primary keys, there needs to be some sort of function to skip duplicate records.

    Can someone please give me an idea as to whether I am on the right track? I've populated the combo box and first list box with data from tblEvents and tblContacts. My question is how to let the user select contacts and move them into the second list box. I also would like to know how to update tblEventParticipants. Do I need to set up a two-dimensional array or something and if so, how on earth would I do that?

    Thank you!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You have the first listbox set to allow multi-selection of items?

    Not sure the second listbox is needed.

    Item 4 can be done with VBA code that cycles through the listbox items and checks if item selected and if is selected, saves a record. Review this http://www.access-programmers.co.uk/...d.php?t=202727

    However, you might find it just as practical and easier to set up a form/subform arrangement. The main form would be the event record and the subform would be the junction table. Each record of subform would be a selected contact.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    TheChairman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    16
    Thanks for the help! So, if I do a form/subform arrangement, my guess is that the subform would be more like a datasheet of Contact IDs and I would just have each cell look up the Contact ID from tblContactIDs? Is that what you are suggesting?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, that is the idea. The ContactID 'cell' could be a multi-column combobox where the ControlSource is set to the junction table ID field. Then the ID column of RowSource would be set as the BoundColumn with 0 width as users don't need to see the ID but users would see the corresponding names listed so as to make selection easier.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    TheChairman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Dec 2011
    Posts
    16
    Thanks again for your help! I'll give it a try.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 11-13-2011, 06:57 PM
  2. Replies: 3
    Last Post: 07-29-2011, 09:30 AM
  3. Populating a form with table data!
    By Extracash in forum Forms
    Replies: 6
    Last Post: 09-13-2010, 05:47 AM
  4. Form data not populating in table
    By sabrown in forum Forms
    Replies: 0
    Last Post: 08-27-2009, 08:19 PM
  5. Unmatched data entered with data in table
    By boreda in forum Access
    Replies: 0
    Last Post: 07-28-2006, 09:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums