Create a table called Tbl_Staff
Add three fields
StaffID (autonumber)
StaffFN (text)
StaffLN (text)
Enter three staff names
Create a table called Tbl_Events
Add two fields
EventID (autonumber)
EventName (text)
Enter 3 - 5 Event Names
Create a tabled called Tbl_StaffEvents
Add three fields
StaffEventID (autonumber)
StaffID (number)
EventID (number)
Create a form
Add a listbox with this SQL statement
Code:
SELECT Tbl_Staff.StaffID, [staffln] & ", " & [stafffn] AS StaffName, Tbl_Staff.StaffLN, Tbl_Staff.StaffFN FROM Tbl_Staff ORDER BY Tbl_Staff.StaffLN, Tbl_Staff.StaffFN;
Call the list box Lst_Staff
Go to the MULTI SELECT property of the list box and choose SIMPLE
Add a list box with this SQL statement
Code:
SELECT Tbl_Events.EventID, Tbl_Events.EventName FROM Tbl_Events;
Call the list box Lst_Events
Go to the MULTI SELECT property of the list box and choose SIMPLE
Put a button on your form
add this code to the ON CLICK property of your button:
Code:
Dim lstStaff As Variant
Dim iStaff As Integer
Dim lstEvent As Variant
Dim iEvent As Integer
Dim db As Database
Dim sSQL As String
Set db = CurrentDb
iCount = 0
If Me!Lst_Staff.ItemsSelected.Count <> 0 Then
For Each lstStaff In Me!Lst_Staff.ItemsSelected
iStaff = Me!Lst_Staff.ItemData(lstStaff)
If Me!Lst_Events.ItemsSelected.Count <> 0 Then
For Each lstEvent In Me!Lst_Events.ItemsSelected
iEvent = Me!Lst_Events.ItemData(lstEvent)
sSQL = "INSERT INTO Tbl_StaffEvents (StaffID, EventID) VALUES (" & iStaff & "," & iEvent & ")"
'MsgBox sSQL
db.Execute sSQL
Next lstEvent
Else
MsgBox "Nothing was selected from the EVENT list", vbInformation
Exit Sub 'Nothing was selected
End If
Next lstStaff
Else
MsgBox "Nothing was selected from the STAFF list", vbInformation
Exit Sub 'Nothing was selected
End If
If you set the multi select property SIMPLE each item you click will be selected, you have to click the same item again to unselect it
If you set the multi select property to EXTENDED each item you click will erase all other selections in that list box and only select the item you just clicked. If you want to add additional items or remove items one at a time you have to hold down the SHIFT key and click the item.
The code will add a record for each staff and each event chosen. So if you choose two staff and two events you will end up with four records in the table TBL_STAFFEVENTS.
I believe this is what you're after, you can modify the code to your situation.