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
Refactorings
No refactoring yet !
Ants
January 31, 2010, January 31, 2010 19:16, permalink
No rating.
Login to rate!
There is a lot of common code between the two click handlers. The only difference is the special ListBox class, and the SQL command that is built up. I suggest that you make a common subroutine that takes the following parameters: listbox, sqlPrefix, sqlMiddle, sqlSuffix.
BTW, what kind of listbox are you using? I couldn't find docs on a listbox that has Column or Requery methods.
VBA, not VB.NET.
This is to provide a simple many-to-many join table UI linking Ambassadors and Events. The list boxes are bound to queries, one of which gives the names of the ambassadors linked to the current event, and the other one gives all the ambassadors by looking for a null link with the first query. I tried to do this with as little extra code as possible!
Gone overkill with comments because code may be accessed by people who know very little VBA, if any.