Listbox with Multi-Select Output Question

[google]

Hello,
I am trying to create a listbox that users can select multiple
entries on. I want Access to put each on of those selections in a
different row on a particular table. This table will also include the
primary key of the parent form of the form that has the listbox. Here
is the code that I have. Right now it will put all of the selections
from the listbox into one row.

Private Sub Category_Click()

Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Me
Set ctl = frm!Category
'Category is the listbox, for some reason if I change the name of
the listbox to lstCategory and I change the code here the code does not
work, but this is a secondary issue.
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " "
Next varItem

Me.catText.Value = Me.Category.ItemsSelected.Count
Me.catText2.Value = strSQL
'These two statements above are just for testing purposes so I can see
what is going on.
End Sub

I am new to VB so this question may seem elementary. Thanks for the
help in advance.

- Michael

 

[John Nurick]

Try something like this, building and executing a single-record SQL
append query for each item. I don't know the names of your fields or
controls; PKField is of course the name of the primary key field, and
txtPKField the name of the control that displays its value on your form.

...
Dim dbD as DAO.Database
Set dbD = CurrentDB()

For Each varItem in Me.Controls("Category").ItemsSelected
strSQL = "INSERT INTO MyTable (PKField, MyField) " _
& "VALUES (" & Me.txtPKField & ", " & varItem & ");"
dbD.Execute strSQL, dbFailOnError
Next 'varItem

Set dbD = Nothing

However: where you have a one to many relationship like this, it's
usually easier to displsy it with a continuous subform in the main form;
the subform having one control, a combobox whose rowsource is the same
as the rowsource of your present listbox. That way, instead of selecting
items in a listbox and running code to create corresponding records in
the related table, these records are created automatically as the user
selects items from the combobox on the subform.

(More importantly, it's much easier to display the selected records in
the subform and let the user manage them, than by reading them back into
a listbox and responding to the user's manipulation of that).