Multiselect listbox question

[John Passaro]

In Access 2003, I have a data entry form with a Multi Select list box. I'd
like to attach a button which runs a macro or a query or a code that will
create a new record (as part of a many-to-many relationship) for each
selected entry. I've been figuring it would look something like this:

INSERT INTO table
VALUES (ID number drawn from the form), (list of items selected in the list
box)

There's two problems.
A) I don't know how to get a hold of that list of items.
B) Even if I could, I suspect this wouldn't work.

Any thoughts?

 

[Bob Quintal]

=?Utf-8?B?Sm9obiBQYXNzYXJv?= <John
(e-mail address removed)> wrote in

In Access 2003, I have a data entry form with a Multi Select
list box. I'd like to attach a button which runs a macro or a
query or a code that will create a new record (as part of a
many-to-many relationship) for each selected entry. I've been
figuring it would look something like this:

INSERT INTO table
VALUES (ID number drawn from the form), (list of items
selected in the list box)

There's two problems.
A) I don't know how to get a hold of that list of items.
B) Even if I could, I suspect this wouldn't work.

Any thoughts?

Insert into with VALUES only inserts one row.

In the button's OnClick procedure,

You would need to loop through the ItemsSelected collection and
issue an Insert for each row individually.

Private Sub Command2_Click()
Dim x As Variant
Dim strSQL As String

For Each x In Me.List0.ItemsSelected
strSQL = "Insert into xx (a,b) _
& " VALUES (" _
& """" & Me.Text0 & """," _
& """" & Me.List0.Column(1,x) & """" _
& ")"
Debug.Print strSQL
' docmd.runSQL strSQL
Next

End Sub

 

[John Passaro]

Thanks so much! That actually makes a lot of sense.

I'm not very strong in Visual Basic syntax, so could you possibly advise how
to figure out what to substitute for the prefix Me, as in "Me.Text0"? Also,
given that Text0 is the name of a text box on the form, is there anything I
need to attach to the end of that, such as "Me.Text0.Content" or something?

 

[John Passaro]

I tried this and VBA doesn't recognize .ItemsSelected as a valid "method or
data member". Help!!!

 

[Douglas J. Steele]

Bob's syntax is definitely correct.

Are you sure you've got the proper control name in your code: that it's a
list box, and not some other type of control?