473,241 Members | 1,579 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,241 software developers and data experts.

Listbox with Multi-Select Output Question

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
3 4608
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Bernie Yaeger | last post by:
I now know how to gather the file type icons and I'm able to use them in a listview. But a listbox does not have a .smallimagelist or .largeimagelist member, so I don't know how to translate that...
5
by: RBohannon | last post by:
I have a multi-selectable listbox named lstTitles. In the OnClick event I had the following: If Not IsNull(Me.lstTitles) Then ' do something with list selection Else ' do something else End...
3
by: Thunder$truck | last post by:
I'm sure this is easy but I can't figure it out. I use a ListBox for my application log and each time the app performs a task an entry is added into the ListBox. When the list box becomes...
0
by: Bernie Yaeger | last post by:
OK, I have drag-drop working between one listbox and another (and back) with the code below. But I can't get it to work when I change the selection mode to multi or multi extended. Any help...
7
by: Dave | last post by:
Hi all, After unsuccessfully trying to make my own dual listbox control out of arraylists, I decided to look for a 3rd party control. I've looked for over a week now and can't find anything but...
2
by: Martin | last post by:
Hi all, I'm trying to correct the (imho) wrong behaviour of a listbox when the user right-clicks on an item. I would like the selection to change when this happens, just like when the user uses...
1
by: eric | last post by:
Hi, Can anyone please explain to me how I can cop selected items from a multi listbox(simple) to another multi listbox? the code I found on msdn only seems to support single column copies, i...
1
by: gayano | last post by:
HI all, I have 2 forms 1. MainForm 2. SubForm Then I have a listbox in the subform and textbox in the mainform. (when i click on the listbox item) (i dont want multi selections) I want...
1
by: =?Utf-8?B?Um9iZXJ0IFNtaXRo?= | last post by:
Hi, I would like to use a multi-column listbox in asp.net (i.e. a web control). Do you know of any such controls out there. The standard listbox doesn't support multi-columns and datagrids and...
1
by: Sunny | last post by:
Hi, Does anyone knows, How to create a Multi Column Listbox & then Add Items to it Dynamically to each column. This only creates one column: <SELECT id="Select1" size="8" name="Select1"...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.