Populating a table with multi selection List Box

[Guest]

Is it possible to enter data in a table through a multi selection List Box??
I am creating a list of partners in a project and I am linking each partner
to a list of keywords (e.g: partner A is involved with "culture" and
"politics").
I solved this problem creating a second table called keywords and a third
called partnerprofiles linking both the partners table and the keywords
table... however, I am not very satisfied with this solution because when I
run a query I have many repetitions of names.
e.g instead of having a result like:
Partner A = Culture, Politics, X, Y, ...
I have:
A = Culture
A = Politics
A = X
A = Y
A = ...
So it gets really messy especially when I want to use the result in a clean
report.

 

[Danny J. Lesandrini]

How about something like this. Assuming you have a Partner-Keyword table
with a Partner and Keyword columns, and that the form with your listbox of
key words has a txtPartner field, where you can grab that foreign key value.

Public Function WriteSelectionsToTable() As Integer
On Error Resume Next

Dim iItem As Integer
Dim sPartner As String
Dim sKeyword As String
Dim sSQL As String
Dim iCount As Integer

sList = "('"
For iItem = 0 To lstBox.ListCount - 1
If lstYourListBox.Selected(iItem) = True Then
sPartner = Nz(Me.txtPartner, "")
sKeyword = lstYourListBox.Column(0, iItem)
sSQL = "INSERT INTO tblYourTable ([Partner], [Keyword]) " & _
"VALUES('" & sPartner & "','" & sKeyword & "')"
CurrentDb.Execute sSQL
iCount = iCount + 1
End If
Next

WriteSelectionsToTable = iCount

End Function