1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

Private Sub cmdAddAmbassador_Click()
' Run when the user clicks the "<" button to add an ambassador to an event

  ' Need to make sure varItem is the right kind of object
  Dim varItem As Variant
  
  ' Get rid of those pesky "You are about to append 1 row" warnings.
  ' This also supresses important error messages, so should be commented
  ' out for debugging.
  DoCmd.SetWarnings (False)

  ' Loop through all the items selected in the "Available ambassadors" list
  For Each varItem In Me.lstAvailableAmbassadors.ItemsSelected
    
    ' Add the selected person to the current event, by creating a new record
    ' in the join table
    DoCmd.RunSQL "INSERT INTO AmbassadorWork VALUES (" & Me.ID & "," _
    & Me.lstAvailableAmbassadors.Column(3, varItem) & ",NULL,NULL);"
  
  Next

  ' Reload the list boxes so that they contain up-to-date information
  Me.lstAvailableAmbassadors.Requery
  Me.lstWorkingAmbassadors.Requery
End Sub

Private Sub cmdRemoveAmbassador_Click()
' Run when the user clicks the ">" button to remove an ambassador from an event
  
  Dim varItem As Variant
  DoCmd.SetWarnings (False)

  ' Loop through selected items in the list
  For Each varItem In Me.lstWorkingAmbassadors.ItemsSelected
  
    ' Remove the selected person from the current event by deleting the record
    ' which links the person with this event from the join table
    DoCmd.RunSQL "DELETE FROM AmbassadorWork WHERE (" & "EventID = " & Me.ID _
    & " AND AmbassadorID = " & Me.lstWorkingAmbassadors.Column(3, varItem) & ");"
    
  Next

  ' Reload the list boxes so that they contain up-to-date information
  Me.lstAvailableAmbassadors.Requery
  Me.lstWorkingAmbassadors.Requery
End Sub