Main Form List Box Selections Add Record in Subform

[geotalk2bs]

Wondering if this is possible? I have a main form that displays Task
detail. One of the items on this form is a List Box to assign Team
Members to Tasks. It displays Team Members in a multi-select
fashion. I have a separate Table to capture the one-to-manys of Task
and assigned Team Member. I have created a Subform on my Main Form
displaying the one-to-many Table data.

Can I have each selection made in the Main Form's List Box create a
new record in the SubForm?

Example: Task A is displayed, Team Member List Box shows Employee A,
Employee B, and Employee C. I select Employee B and Employee C and
click an 'Add' button. I would Like the Subform to now show the two
new records:
Task A Employee B
Task A Employee C

Any guidance would be greatly appreciated.
Thanks,
Bill

 

[Dirk Goldgar]

In

Wondering if this is possible? I have a main form that displays Task
detail. One of the items on this form is a List Box to assign Team
Members to Tasks. It displays Team Members in a multi-select
fashion. I have a separate Table to capture the one-to-manys of Task
and assigned Team Member. I have created a Subform on my Main Form
displaying the one-to-many Table data.

Can I have each selection made in the Main Form's List Box create a
new record in the SubForm?

Example: Task A is displayed, Team Member List Box shows Employee A,
Employee B, and Employee C. I select Employee B and Employee C and
click an 'Add' button. I would Like the Subform to now show the two
new records:
Task A Employee B
Task A Employee C

Any guidance would be greatly appreciated.

Something along the lines of (air code):

'------ start of code ------
Private Sub cmdAddMemberToTask_Click()

Dim db As DAO.Database
Dim strSQL As String
Dim varItem As Variant

If Me.lstTeamMembers.ItemsSelected.Count = 0 Then
MsgBox "You haven't selected any team members!"
Exit Sub
End If

' Make sure the current Task record has been saved.
If Me.Dirty Then Me.Dirty = False

Set db = CurrentDb

' Add each team member selected in the list box to the
' TaskTeamMembers table.
With Me.lstTeamMembers
For Each varItem In .ItemsSelected
strSQL = _
"INSERT INTO TaskTeamMembers(TaskID, TeamMemberID) " & _
"VALUES (" & _
Me.TaskID & ", " & .ItemData(varItem) & ")"
db.Execute strSQL, dbFailOnError
.Selected(varItem) = False
Next varItem
End With

' Requery the Task Team Members subform.
Me.sfTaskTeamMembers.Requery

End Sub
'------ end of code ------