Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MultiSelect ListBox puzzlement (Access 2k, Win 2K Pro)

    I thought it'd be nice to use a listbox using MultiSelect to handle a combination of choices by the user. After sending it on for testing, I noticed the following (a quote from Charlotte): multiselect lists will allow you to select multiple items in a record, but they won't display those items when you come back to that record.

    Is this TRUE? It certainly seems to be in my case. That is, you select stuff, go away, come back and there's no indication that anything was selected. Go into DataSheet view, nada. Also nothing written to the source table for the form. The listbox seems to be rather useless, and I'm sure there's some reason to suspect I don't know what I'm doing (I've never used the multiselect option or listbox) but I thought I'd check with the loungers next.

    SO, short questions are: Why does the listbox not display choices made? How do I get the selection saved to the table?

    TIA
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win 2K Pro)

    That comes about because a multiselect list box is inherently an unbound control. The reason that is so is that Access doesn't support the concept of a multi-value field (which isn't really valid in a relational database anyhow). So what you need to do is write some code to capture whatever choices have been made and either construct your own multivalue field, or write out relational records to a related table.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    huh! i suppose this makes sense, but it also makes ListBox my candidate for 'most retarded toolbox control'

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    I understand your sentiments, but the bottom line is it was designed more for use in unbound situations. There isn't a good way for it to figure out what kind of choices might have been checked when the data was originally saved, given the constraints of a relational database - at least a properly normalized one.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    <font color=blue>To heck with the multi-select list box! I just geared a subform to do the same thing (more or less). it worketh.</font color=blue>

    so what you're saying is, Access pretends to give you a control with multi-select functionality (hey! even 'extended select'!) but the control has to have user-written code to make it perform as advertised. Ah well.... i knew there was a reason I avoided using them in the past. but don't check boxes write a deliminated string to a field? at least they on a webform. I suppose the righteous thing to do is write out the selections to a separate table. do you have any 'canned' code for use in this case? I could re-design the form with checkboxes but I like the illusion of ease of use a multiselect listbox presents. <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    > I just geared a subform to do the same thing (more or less).

    That is the "standard" approach.

    A multi-select list box can be very useful as an unbound control, for example to let the user select a number of records or categories to display/print in a report. It is not ideal as a bound control, and I can't remember having seen claims from Microsoft that it is, so I don't know what your "perform as advertised" is based on.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    The key is in the word "multiselect". The control is designed for selection, not for data display, since if it is bound to a field, it can't translate multiple values in the field back into selected values. It is intended to allow the user to select several items that will then be acted on by the code. That IS as advertised. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    A checkbox doesn't write any kind of string to anything, it enters a true or false value, which in Access equates to a -1 or 0, into the field it is bound to.

    You haven't really said what you were trying to do with the multiselect, but if you wanted to enter multiple values in a single field, then what were you planning to do with those values, since you no longer have a normalized table?
    Charlotte

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    oh i was just feeling a little bugged by the expectation *I* brought to using the control on a form: expecting that, if you put the control on the form and set it up as a multi-select and assign a column as the source for the data, the multi-select list box would proceed to act as expected. there's nothing during the process that really alerted me to this being, in fact, considered 'bad practice'. Of course, I can understand why it might be considered as such and the alternate method (which I've always used in the past) is perfectly fine as well. Call me naive, but if the interface allows you to 1) Set the listbox to select multiple lines and 2) assign the value to a column, then I'd expect 1) the choice to 'stick' in the control and 2) actually be written to the table. the fact that it didn't do this made me think perhaps there's some secret setting I was missing, not 'You Can't Do This'. That's where the advertisement came in, in that the control seemed to be happy with doing all of the above, at least when setting up the properties.

    I mean, a multi-line list box that only allows one selection is almost never, IMHO, better than a simple drop-down. The list box seems most useful *with* the option to multi-select.

    Ah well live and learn!

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    I was planning on using the multi-select to display one or more selections in the box, for any record displayed in a form. the fact that my selections went away was puzzling; the fact that the values didn't get written was also puzzling. Judging from how the properties were set ("advertised"), I was unaware that the control is intended for another use IF multiselect is turned on. As far as this denormalizing things, if the selections are written to a single field with some kind of deliminator I don't see what would stop anyone from getting the values back out.

    I don't want to beat this control into the ground too much, but perhaps if the control is meant to react to some kind of user action (rather than store data selected to a table, as I assumed from the properties settings) then it would have been nice for the wizard to bring that up. of course, now that I know about the control (which I have never used before -- perhaps I did a long time ago and was so mystified and befuddled that I didn't attempt to use it till now <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> ), I don't have the same expectations. Also, I'm completely aware that it's silly to expect all aspects of a program such as Access to behave with perfect transparancy, but in this case the properies available for the list box seem much more unintuitive than normal.

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    <hr>As far as this denormalizing things, if the selections are written to a single field with some kind of deliminator I don't see what would stop anyone from getting the values back out.<hr>
    Normalized data contains one piece of information in each field, and all the fields in the record are theoretically dependent only on the primary/unique key.l You can get multiple values back out of a field, but the records are no longer normalized because a the field contains multiple values. So if you try to determine how many records exist with complex conditions, you'll quickly discover why multiple values in a field are frowned upon. One of the biggest nuisances in converting data from programs like FileMaker is that users have stored arrays of values in single fields because the design of the program actually encourages them to do so.
    Charlotte

  11. #11
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    It's certainly possible to make the listbox act the way you want it to, but you have to do it using a bit of code. I would store the user's selections as a binary string (000110010...) and include code to write the user's selections to the field (After Update) and code to retrieve them when each record is loaded into the form (On Current).

    The biggest drawback of this method is that it requires the listbox's list to be unchanging - always the same length and in the same order - unless of course you want to want to be really clever and change the length/order of all the binary strings every time the list changes! That might not exactly be the best use of processor time (not to mention your own time)... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> ...but it's not impossible to do.

    Although subforms might be the "proper" way to do it, listboxes look so much better IMHO. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <img src=/w3timages/redline.gif width=33% height=2>
    If it helps, here's the code for a multiselect listbox containing 7 items. The listbox is called List0 and the data is stored in a field called myField:

    Private Sub Form_Current()
    Dim i As Integer
    For i = 1 To 7
    If Mid(myField, i, 1) = "1" Then
    List0.Selected(i - 1) = True
    Else
    List0.Selected(i - 1) = False
    End If
    Next i
    End Sub

    Private Sub List0_AfterUpdate()
    Dim i As Integer, myString As String
    myString = ""
    For i = 1 To 7
    If List0.Selected(i - 1) Then
    myString = myString & "1"
    Else
    myString = myString & "0"
    End If
    Next i
    myField = myString
    End Sub
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    store the selection as a binary string?? heh heh... is this an example of sarcastic coding? how about hex or octal or, like, upper register ASCII?

  13. #13
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Ok, so the binary string could be converted to an integer to save a bit of space... the point I was trying to make is that it's possible (and in fact, very easy) to store data from a multiselect listbox without any need to worry about compromising the "normalized data structure" that the others were so concerned about.
    <img src=/S/argue.gif border=0 alt=argue width=50 height=25>

    The thing to bear in mind is how to report on the stored data, if that's required, but I can see no reason why that should be a problem.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    But by storing multiple values in one field, whatever the exact form, you ARE creating a non-normalized design...

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: MultiSelect ListBox puzzlement (Access 2k, Win

    Thanks for the support, I suppose, in giving some feedback on how to make a multiselect listbox function in the manner I was expecting. I'm not sure how useful a binary string would be for storing choices. Wouldn't that just tell you how many selections were made? I'd actually be happy with a deliminated list in a field, but I agree in principle that this sort of thing is dangerous in terms of design. however, if you're going to allow a multi-select list box to store data to a field, a deliminated list would definately meet my expectations and wouldn't cause me puzzlement. Not sure why you can't adjust on-the-fly for changes in the number of items in the listbox tho. Is there a method for enumerating the items in a listbox?

    I dunno... Now that I've got an alternate (the subform with a datasheet view etc.) working the listbox problem has been voided. I still think the control is badly implemented, AND that it does look nicer than a subform/datasheet. but this sort of thing could go on all day... <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> so i think i will stop....

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •