By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Listbox with Multi-Select Output Question

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Is the multiselect listbox really the way to go here? How about a
subform with a checkbox appended to the records?

Otherwise, for every pass in ItemsSelected execute an INSERT query. Much
like
For Each varItem In ctl.ItemsSelected currentdb.execute "INSERT INTO yourtable and whatsoever VALUES('"&
ctl.itemdata(varitem) &"')" strSQL = strSQL & ctl.ItemData(varItem) & " "
Next varItem

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #2

P: n/a
Ok, I have the data outputting into the table into separate rows.
The problem is that it does not put one entry per selection. For
example if I select item 3 and item 5 in the list box I get 3, blank,
3, and 5 in separate rows (in that order). Is it possible for me to
get just a 3 in one row and 5 in the next.
A secondary issue is when I am selecting the data in the listbox and
I move onto a new entry set I have to deselect the items I do not want
(that were selected in the previous entry set). Is there some way
that I can clear the selections in the listbox for each entry set?
Thanks.

Here is my latest code:

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
For Each varItem In ctl.ItemsSelected

CurrentDb.Execute "INSERT INTO tblCategoryTest
(SelectedCategories) VALUES('" & ctl.ItemData(varItem) & "')"

Next varItem

Me.catText.Value = Me.Category.ItemsSelected.Count
Me.catText2.Value = strSQL
End Sub

- Michael

Nov 13 '05 #3

P: n/a
Whose click event is this? Every selection you make in the listbox
accounts for one click, which may cause the data you see.

I suggest you put a button next to the listbox, caption "Update" or so
(find something that matches what happens as exactly as possible).

Even the Exit event of the listbox is unreliable, unless you start the
procedure by removing all previous entries from the table.

As for clearing all selections (which I'd rather hesitate to judge
'secondary'):
inside the loop, after you've done the INSERT, do
ctl.selected(varitem)=false

Oh by the way: your frm and ctl references are completely superfluous.
"Category" would do. I suggest you name it ctlCategory, though, or even
lstCategory, that tells you from viewing the code what is going on.

go****@myxware.com wrote:
Ok, I have the data outputting into the table into separate rows.
The problem is that it does not put one entry per selection. For
example if I select item 3 and item 5 in the list box I get 3, blank,
3, and 5 in separate rows (in that order). Is it possible for me to
get just a 3 in one row and 5 in the next.
A secondary issue is when I am selecting the data in the listbox and
I move onto a new entry set I have to deselect the items I do not want
(that were selected in the previous entry set). Is there some way
that I can clear the selections in the listbox for each entry set?
Thanks.

Here is my latest code:

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
For Each varItem In ctl.ItemsSelected

CurrentDb.Execute "INSERT INTO tblCategoryTest
(SelectedCategories) VALUES('" & ctl.ItemData(varItem) & "')"

Next varItem

Me.catText.Value = Me.Category.ItemsSelected.Count
Me.catText2.Value = strSQL
End Sub

- Michael


--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.