I have a database with a tab control that has several pages in it (my
boss loves tabs for navigation).  Each page has a list box based on a
category of training events that members of my office attend and the
box is based on a query that selects the training events for the tab
page's respective category.  I select an item from the list, click a
button, and go to a form with a text box showing the name of the
training event I previously selected.  On that form, I want to select
mutiple names of office members from a list box (already created and
source is the Office Roster table) and then click on a button to have
it update the training event record with the list of attendees I
selected.  What is the best way to do this?  I don't know how to write
VBA code but I am familiar with the editor.

Relevant Tables:  Training Event, Office Roster, Training Category

Thanks in advance for your help.

Stewart

On 21 Mar 2007 20:47:41 -0700, "Stewart" <alaskabranns@gci.net> wrote:

>I have a database with a tab control that has several pages in it (my
>boss loves tabs for navigation).  Each page has a list box based on a
>category of training events that members of my office attend and the
>box is based on a query that selects the training events for the tab
>page's respective category.  I select an item from the list, click a
>button, and go to a form with a text box showing the name of the
>training event I previously selected.  On that form, I want to select
>mutiple names of office members from a list box (already created and
>source is the Office Roster table) and then click on a button to have
>it update the training event record with the list of attendees I
>selected.  What is the best way to do this?  I don't know how to write
>VBA code but I am familiar with the editor.

Private Sub cmdProcess_Click()
    ' Comments  : Update the AnimalCondition table based on the selections in
    '             the unbound multiselect listbox lstHealthIssues.
    '             Newly selected rows will be added to the table, newly
cleared
    '             rows will be deleted.
    ' Parameters: None
    ' Modified  : 01/29/02 by JWV
    '
    ' --------------------------------------------------
    ' Populate the AnimalCondition table with the selected issues
    On Error GoTo PROC_ERR
        
    Dim iItem As Integer
    Dim lngCondition As Long
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' save the current record if it's not saved
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
    Set db = CurrentDb
    ' Open a Recordset based on the table
    Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
    With Me!lstHealthIssues
        ' Loop through all rows in the Listbox
        For iItem = 0 To .ListCount - 1
            lngCondition = .Column(0, iItem)
            ' Determine whether this AnimalID-HealthID combination is
currently
            ' in the table
            rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
                         & "[HealthIssueID] = " & lngCondition
            If rs.NoMatch Then ' this item has not been added
                If .Selected(iItem) Then
                    ' add it
                    rs.AddNew
                    rs!AnimalID = Me.AnimalID
                    rs!HealthIssueID = lngCondition
                    rs.Update
                End If ' if it wasn't selected, ignore it
            Else
                If Not .Selected(iItem) Then
                    ' delete this record if it's been deselected
                    rs.Delete
                End If ' if it was selected, leave it alone
            End If
        Next iItem
    End With
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Me.subAnimalCondition.Requery
    
PROC_EXIT:
    Exit Sub
    
PROC_ERR:
    MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
           & vbCrLf & Err.Description
    Resume PROC_EXIT
    
End Sub



>Relevant Tables:  Training Event, Office Roster, Training Category

AnimalCondition is your Training Event; the primary key of Office Roster is
AnimalID (though I hope you don't have too many animals as coworkers); and the
primary key of Training Category is my HealthIssueID.


             John W. Vinson [MVP]