Multi Select Listbox to Table field

[MSU Sptn2]

I created a multi select listbox (lstEmail) on a subform and linked the
selected items to a listbox on the main form, similar to the ListBoxSubform
in Rogers Access Library (thank you, Roger). The selected values are written
to a separate table which hold the values in a One-to-Many relationship with
the main table. The items in the list are emails that I would like to
reference in the "To:" portion of an email so they need to be in one field
separated by a semicolon. I have tried to string the selected items and set
the field (Mailing_List) in a table (TblCompliance) to the string by adding
the following code, which didn't work:

If lstEmail(i) = True Then
For Each var In Me.lstEmail.ItemsSelected
strEmails = strEmails & Me.lstEmail.Column(1, var) & ";"
Next var

CurrentDb.Execute "UPDATE TblCompliance SET Mailing_List='" & strEmails & "'
WHERE AN_AutoNumber = " & Me!txtAN_AutoNumber

ElseIf lstEmail.Selected(i) = False Then

CurrentDb.Execute "UPDATE TblCompliance SET Mailing_List='" & "NA" & "'
WHERE AN_AutoNumber = " & Me!txtAN_AutoNumber

End If


Should this be added as a sub instead? Any help is appreciated.

 

[Graham Mandeno]

I'm not sure what the line:
If lstEmail(i) Then
is supposed to be doing.

What you need is something like this (note the use of the With statement to
avoid repeated reference to the listbox):

Dim var as Variant, strEmails as String
With Me.lstEmail
If .ItemsSelected.Count = 0 then
strEmails = "NA"
Else
For Each var In .ItemsSelected
strEmails = strEmails & .Column(1, var) & ";"
Next var
' remove the last semicolon
strEmails = Left(strEmails, Len(strEmails)-1)
End If
End With
CurrentDb.Execute "UPDATE TblCompliance SET Mailing_List='" & strEmails _
& "' WHERE AN_AutoNumber = " & Me!txtAN_AutoNumber