populating a table from two listboxes

[demo]

Hi
I got two tables, people and events. Each have a .name and a .ID.
In a third table, participation, I have a many to many connection
between the two, conection people to events.

In a form, I got two listboxes populated with the .name and the .ID
from the two tables.

I want to select multiple (or single) elements in both listboxes, and
then push a button to create new records in a the participations table
for each combination of events and people, thus adding the
participation of all the selected people to all the selected events.

Any thoughts much appreciated.
K

[Pieter Linden]

create an unbound form with two listboxes, one for people, one for
events. both have 2 columns, the unique ID field for each
person/event, and the name of the person/event. column widths:0;1
column count: 2. Set both of the listboxes' multiselect property to
Extended. In the button to add records, add this code (or fix it up
nicer, even!)

Private Sub cmdAddParticipants_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varPerson As Variant, varEvent As Variant
Dim ctlPerson As Control, ctlEvent As Control

Set ctlPerson = Me.mslbxPeople
Set ctlEvent = Me.mslbxEvents

Set db = CurrentDb

Set rs = db.OpenRecordset("tblEventParticipants", dbOpenTable,
dbAppendOnly)

For Each varPerson In ctlPerson.ItemsSelected
For Each varEvent In ctlEvent.ItemsSelected
rs.AddNew
rs.Fields("EmployeeID") = ctlPerson.ItemData(varPerson)
rs.Fields("EventID") = ctlEvent.ItemData(varEvent)
rs.Update
Next varEvent
Next varPerson

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox "done"
End Sub

I'm assuming you have these tables:
tblEventParticipants (ParticipantID, EventID)
tblEmployees (EmployeeID, FirstName, LastName,...)
tblEvents (EventID, EventName,...)

[Pieter Linden]

create an unbound form with two listboxes, one for people, one for
events. both have 2 columns, the unique ID field for each
person/event, and the name of the person/event. column widths:0;1
column count: 2. Set both of the listboxes' multiselect property to
Extended. In the button to add records, add this code (or fix it up
nicer, even!)

Private Sub cmdAddParticipants_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varPerson As Variant, varEvent As Variant
Dim ctlPerson As Control, ctlEvent As Control

Set ctlPerson = Me.mslbxPeople
Set ctlEvent = Me.mslbxEvents

Set db = CurrentDb

Set rs = db.OpenRecordset("tblEventParticipants", dbOpenTable,
dbAppendOnly)

For Each varPerson In ctlPerson.ItemsSelected
For Each varEvent In ctlEvent.ItemsSelected
rs.AddNew
rs.Fields("EmployeeID") = ctlPerson.ItemData(varPerson)
rs.Fields("EventID") = ctlEvent.ItemData(varEvent)
rs.Update
Next varEvent
Next varPerson

rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox "done"
End Sub

I'm assuming you have these tables:
tblEventParticipants (ParticipantID, EventID)
tblEmployees (EmployeeID, FirstName, LastName,...)
tblEvents (EventID, EventName,...)