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!