Solved

multi-selection VBA code in a list box

Posted on 2011-02-21
22
791 Views
Last Modified: 2013-03-12
I would like to build a list box where I could choose 2 or 3 items from a single list,I mean 2 or 3 items from a list of 5 items - multi-select property of the list box.
 Can you give me the VBA code to do that ? In Access 2007 this issue has been solved but in access 2003 I need a VBA code.
Thank you
0
Comment
Question by:marian68
  • 7
  • 4
  • 3
  • +3
22 Comments
 
LVL 84
ID: 34944487
The same code should work in either. See this link:

http://msdn.microsoft.com/en-us/library/aa196172(v=office.11).aspx

Basically, you loop through the ItemsSelected collection.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34944488
I'm not clear why you expect the code for A2003 to be any different?
I'm not aware of any differences in the list box between the two versions of Access.
0
 

Author Comment

by:marian68
ID: 34944589
0
 

Author Comment

by:marian68
ID: 34944661
LSM Consulting: The examples in the site are for printing. I am not a specialist in VBA code. All I want to strore in my table 2 or 3 concatenated items from my list box. I found the below code but it doesn't work. Maybe you can tell me why:
"Private Sub cmdStoreAllSelections_Click()
Dim SelectedValues, item

For Each item In lstItems.ItemsSelected
    If SelectedValues > "" Then
        SelectedValues = SelectedValues & "," & lstItems.ItemData(item)
    Else
        SelectedValues = lstItems.ItemData(item)
    End If
Next item
Me!CompoundValue = SelectedValues
End Sub"
Thank you


0
 
LVL 77

Expert Comment

by:peter57r
ID: 34944724
The link you posted refers to a new feature in A2007 called mult-valued fields.
These are nothing to do with standard listboxes or comboboxes.  The code you posted is used with listboxes NOT muti-valued fields.

There is NO equivalent to multi-valued fields in any previous version of Access.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34944727
mult-valued  s/b multi-valued
0
 

Author Comment

by:marian68
ID: 34944876
Ok I will rephrase my problem.
I have the following list box
aaaaaaaaaaaaaa
bbbbbbbbbbbbbb
cccccccccccccccccc
dddddddddddddd
eeeeeeeeeeeeee
I would like to be able to select more than 1 item lets say first 3 items and to have in my table:aaaaaaaaaaaa,bbbbbbbbbbbbbbbb,cccccccccccccc
Can anyone give the code for this?
Thank you
0
 

Author Comment

by:marian68
ID: 34945345
Capricorn where are you?
0
 
LVL 10

Expert Comment

by:VTKegan
ID: 34945608
You can set the multi-select value to simple, and then you can select multiple items in a list box.

Then on a button click you would do something like

Private Sub Button1_Click()
Dim varItem as variant
Dim strSelect as string
Dim strSQL as string

For Each varItem In Me.lstBox.ItemsSelected
  strSelect = strSelect & lstBox.ItemData(varItem) & ";"
Next

strSQL = "Update [YourTable] Set [YourField]='" & strSelect & "' Where [YourTableID]=" & Me.ID
CurrentDb.execute strSQL, dbFailOnError

End Sub
0
 
LVL 84
ID: 34945688
<Capricorn where are you?>

That's quite rude. If you want cap to help you exclusively, maybe you should contact him directly and hire him. I'm sure he'd appreciate the work.

First: Storing data in this manner is NOT the proper way to manage this. Instead, you should build a table to hold the values selected, and use that to store the user's selections. You can then build a Subform that looks like a listbox, and use that instead of a listbox. This manages the storage for you.

That said, if you insist on storing denormalized data then the code you referenced here should work, with a few tweaks:

Dim SelectedValues As String
Dim oitem As Variant

For Each oitem In lstItems.ItemsSelected
    If SelectedValues > "" Then
        SelectedValues = SelectedValues & "," & lstItems.ItemData(oitem)
    Else
        SelectedValues = lstItems.ItemData(oitem)
    End If
Next item

Me!CompoundValue = SelectedValues

Assuming your Listbox is named "lstItems", and the field where you want to store this denormalized data is named "CompoundValue" (and is a string), then that should work.

However, this is only half the battle. What happens when the user then is simply viewing the data? You must "split" the data you've stored in CompoundValue, loop through the Items in the Listbox, and Select/Deselect them based on your stored, denormalized data. Also, reporting on this sort of data is a real bear as well, since you cannot easily determine how many users have selected the choice of "aaaaaaaaaaaaaaaa", for example.

That's why we stress storing data properly, in a normalized fashion.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 44

Expert Comment

by:GRayL
ID: 34946152
I gave you the answer in your previous question.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34946197
0
 

Author Comment

by:marian68
ID: 34946228
Sorry LSMConsulting if you find my call "rude" but I come here with this list box because I am trying to find out how to store a string using a check box but nobody managed to help me.
Shortly I have 5 check boxes and I would like to have the label of my check stored as a string in my table.
"Private Sub T7_Click()
If Me.T7 Then
  T7 = "aaaaaaaaaaaaaaaaaaa"
Else
  T7 = ""
End If
End Sub"
aaaaaaaaaaaaaaaaaa being also the label of the check box.
Not finding a solution to that I decided to build a list box where I have all 5 strings. More than 1 string can be required.
thank you
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34946294
marian68, is this question related to your question from this post;

http://www.experts-exchange.com/Programming/Languages/.NET/Q_26829402.htm

The experts are right, storing these values from your list box is only half the battle, you really need another table to store the data.

0
 
LVL 10

Accepted Solution

by:
VTKegan earned 167 total points
ID: 34946308
As a software designer, it is possible to do almost anything, but the biggest part of designing software or writing code for people is that the client/question asker creates a clear scope of work as to what is needed.

From what I can tell, your questions (not only this one, but others) are vague, and we asvolunteer experts do our best to understand what you are asking/trying to do. Sometimes it is not the best practice to say "I want to store a value based on a check box" but you should describe your action/application/what you want to accomplish, because what you have in your mind might not be the best method.  So we could give you an answer that works for what you want, but it may not be best practice or could create serious headaches for someone else later down the road.

As for the immediate question, you may want to be more specific describe some of the how/what/whys and we could come up with a better method for this applicaiton or suggest alternatives.


0
 
LVL 3

Assisted Solution

by:DockieBoy
DockieBoy earned 167 total points
ID: 34947490
Ok, from reading both questions maybe this will help, although, I can not stress enough that you should take the advice that the experts have offered, especially the recent post from VTKegan, as from what I can understand, what you hope to achieve and the method for which you are trying to achieve this, you will encounter many problems down the track.  That said, you could try the following;

For starters, you can't store the string values you are talking about in the check box field of your table if the data type is set to Yes/No.

Not so sure I should really be entertaining this idea, but anyway, here goes.

Make sure that the fields in your table that you are using for your check boxes have their data type set to Text.

On your form, add the appropriate text box controls that are now bound to the fields that were previously for your Check Boxes.

Right click each of the new text boxes and their labels one at a time and set the visible property to No.  i would suggest also doing something that easily lets you know that they are not visible, like set the back colour to black and the fore coulour to white (something i picked up way back when from an expert here I think.)

Now add the the corresponding number of "Unbound" check boxes to your form (one check box for each text box)

Change the label captions for each check box to reflect the data you would like to store.

Name the check boxes appropriately (if your fields are T1, T2, T3 etc, perhaps name the check boxes Chk1, Chk2, Chk3 etc.)

For the on click event of your check boxes, use the following;

Private Sub Chk1_Click()
 If Me.Chk1 then
 Me.T1 = Me.Chk1.Caption
 Else
 Me.T1 = ""
End If
End Sub

Substitute the check box and text box names in the code as necessary for each different set.

Again, i think this will cause more trouble than it's worth, but I think this is what you have been getting at.

Hope I didn't breach any rules / answer in wrong post etc.  :)
 
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 166 total points
ID: 34950903
<Sorry LSMConsulting if you find my call "rude" but I come here with this list box because I am trying to find out how to store a string using a check box but nobody managed to help me. >

It's rude to call out to a single Expert. There are quite a few Experts on this board who are more than capable of assisting you. As you're a new member, I should have taken a different tact, but the fact remains: You don't single out one Expert over the others. That's a violation of EE policy, and is considered by many to be rude.

Does the code I suggested not work for you? It's standard, typical code for working with multiple items in a listbox. You iterate through the ItemsSelected collection and do what you need with those items. In this case, you're gathering them and storing them in a single field (which, again, is not a good database design practice).

If it doesn't work, please tell us exactly what does not work. We can fix it, but we can't do so unless you provide us with feedback.


0
 

Author Comment

by:marian68
ID: 34951884
Sorry again for my call out.
For DockieBoy: I tried your solution and it is working but can you tell me please what did you mean with the :"Again, i think this will cause more trouble than it's worth"? Do you have an easier solution to this problem?
Thank you
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34955607
marian68, just to clarify, what I mean is, although that will work for you, I think it will cause you problems.  

As the experts have said (and they are far more experienced and qualified than I), this is not the way to go about it.

There are many "solutions", but I would not suggest that they are easier, however, they are far better.

The method that I have given you, while it may be working for you, will cause problems in that it will prevent normalization and make data recall and manipulation difficult.

For the alternative solutions please see the posts from the experts.  :)  
0
 

Author Comment

by:marian68
ID: 34955828
Thank you for your reply.
I noted a small problem. When I surfing between the records I can not see the small check in the box, in other words, the user if he browses through his records can not see what he checked and not.
Maybe  you can help me.
Thank you
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34955900
Use the on current event for the form and the value of your hidden text boxes to set the value of your check boxes, for example, if one of your text boxes is T1 and check box is Chk1;

Select Case Nz(Me.T1,"")
 Case ""
 Me.Chk1 = False
 Case Is <> ""
 Me.Chk1 = True
 End Select
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now