Access 2007 - Break up a list box into columns

[epid]

I have a problem that I'm not sure there is a solution for... I have a lookup field in a table that allows multiple values and the display control is set to be a List Box. The list box looks fine on the form (ie. a bunch of check boxes), but there are about 15 entries that I would like to have on the screen at once (ie. no scroll bars). Is there any way to break up the list box into columns (like 4 columns of check boxes)?

I have tried changing the Column Count value but this doesn't seem to have the intended effect....

[ADezii]

I have a problem that I'm not sure there is a solution for... I have a lookup field in a table that allows multiple values and the display control is set to be a List Box. The list box looks fine on the form (ie. a bunch of check boxes), but there are about 15 entries that I would like to have on the screen at once (ie. no scroll bars). Is there any way to break up the list box into columns (like 4 columns of check boxes)?

I have tried changing the Column Count value but this doesn't seem to have the intended effect....

Is your request to convert a single column List Box to a 4 Column List Box? If so is there a Delimiter betwen values in the single column List Box? What exactly are you trying to accomplish by doing this? Do you realize that the new format of the List Box will be for display purposes only? If this is your goal, please post some typical single column List Box entries.

[epid]

Is your request to convert a single column List Box to a 4 Column List Box? If so is there a Delimiter betwen values in the single column List Box? What exactly are you trying to accomplish by doing this? Do you realize that the new format of the List Box will be for display purposes only? If this is your goal, please post some typical single column List Box entries.

First of all, thanks for your reply. To answer your question, yes I would like to convert a single columns List Box to a 4 Column list box for purely formatting purposes. The List Box has about 15 entries and I don't want to have all of the items in the list box in one column. Since the List Box allows multiple selections, it does contains Check Boxes. The database is going to be used to keep track of optical prescriptions so typical entries (for tint color) would be: Clear, Grey, Pink, Yellow, etc. If this still doesn't make sense, let me know and I will create a couple screenshots.

[ADezii]

First of all, thanks for your reply. To answer your question, yes I would like to convert a single columns List Box to a 4 Column list box for purely formatting purposes. The List Box has about 15 entries and I don't want to have all of the items in the list box in one column. Since the List Box allows multiple selections, it does contains Check Boxes. The database is going to be used to keep track of optical prescriptions so typical entries (for tint color) would be: Clear, Grey, Pink, Yellow, etc. If this still doesn't make sense, let me know and I will create a couple screenshots.

I would have preferred a little more information, but I think I have enough to give you at least a code Template to work off of. The following code will transfer the entries in a Single-Column List Box named lstOriginal to a 4-Column List Box named lstNew. Let me know how you make out:

Dim intNoOfEntries As Integer, intCounter As Integer, strNew As String
 
'Define the characteristics of the New List Box
With Me![lstNew]
  .ColumnCount = 4
  .BoundColumn = 0     'doesn't matter
  .RowSourceType = "Value List"
  .ColumnWidths = "1 in;1 in;1 in;1.25 in"
  .Width = (4.25 * 1440)   'in Twips 1 inch = 1,440 Twips
End With
 
intNoOfEntries = Me![lstOriginal].ListCount
 
With Me![lstOriginal]
  For intCounter = 0 To intNoOfEntries - 1
    strNew = strNew & .ItemData(intCounter) & ";"       'concatenate into a String
  Next
End With
 
strNew = Left$(strNew, Len(strNew) - 1)     'remove trailing ;
 
Me![lstNew].RowSource = strNew              'RowSource for New List Box

[epid]

I tried the code you posted (with the listbox named appropriately) but got some weird results - strNew only contained "1;2;3;4;5;6;7" etc...

Here is a screenshot that I hope helps to clear things up a bit:

Screenshot

[epid]

Okay - using the following code, I am able to select and deselect items in the List Box using regular Check Boxes (unbound). I suppose that I can make the List Box invisible and control it using the Check Boxes. The only thing is I'm not sure if it is bad practice to have things coded like this. Any thoughts?

Private Sub Check122_Click()
  If (Check122.Value = True) Then
    Tint.Selected(0) = True
  Else
    Tint.Selected(0) = False
  End If
End Sub
 
Private Sub Check123_Click()
  If (Check123.Value = True) Then
    Tint.Selected(1) = True
  Else
    Tint.Selected(1) = False
  End If
End Sub
 

[epid]

Does that seem like a bad way to go about this?

[ADezii]

I tried the code you posted (with the listbox named appropriately) but got some weird results - strNew only contained "1;2;3;4;5;6;7" etc...

Here is a screenshot that I hope helps to clear things up a bit:

Screenshot

That's because you are probably retrieving data from a Bound Column rather than the data in the Column which is visible in the List Box. This was part of the additional information which I would have liked to see. Try changing Line #16 to:

strNew = strNew & .Column(1, intCounter) & ";"

and let me know what happens.

[epid]

I made that change, but strNew still contains "1;2;3;4;5;6;7" etc...
Here is what it looks like:

Screenshot 02

Honestly, at this point I'm ready to just hard code those values directly into the table instead of having them linked from another table. It feels wrong from a database point of view but I don't know how else I can get the form to look right...

[ADezii]

I made that change, but strNew still contains "1;2;3;4;5;6;7" etc...
Here is what it looks like:

Screenshot 02

Honestly, at this point I'm ready to just hard code those values directly into the table instead of having them linked from another table. It feels wrong from a database point of view but I don't know how else I can get the form to look right...

If a Check Box is selected in your Multi-Select, Single Column List Box, you want only the selected Color Names, e.g. Grey, White, etc. to appear in a new 4 Column List Box. Is this correct?

[puppydogbuddy]

I made that change, but strNew still contains "1;2;3;4;5;6;7" etc...
Here is what it looks like:

Screenshot 02

Honestly, at this point I'm ready to just hard code those values directly into the table instead of having them linked from another table. It feels wrong from a database point of view but I don't know how else I can get the form to look right...

I think you are barking up the wrong tree ....I mean control (LOL). You need to use a ListView control to accomplish what you want. Depending on what display option is selected, the ListView expands/collapses from one to five columns. Also, the standard Listbox that comes with MS Access does not have the properties to be able to dynamically format or sort the list items, or allow for images.


Go into the design mode of a form.. click on Insert from the toolbar, then select "ActiveX Control..." from the sub menu. In the selection box that is presented, scroll down to the entry that reads "Microsoft ListView Control, Version 6.0" and click on this, then click on OK, and set the control properties.

See these links for more detailed info and sample code.
http://support.microsoft.com/kb/210006

http://www.thescripts.com/forum/thread206631.html

http://www.tek-tips.com/faqs.cfm?fid=5329

[epid]

If a Check Box is selected in your Multi-Select, Single Column List Box, you want only the selected Color Names, e.g. Grey, White, etc. to appear in a new 4 Column List Box. Is this correct?

I'm sorry - I don't think I clearly described what I wanted in the first place. At the moment, I have one Multi-Select, Single Column List Box.

All I want is to have the Single Column List Box be a Multi Column List Box so that I can see all of the items in the List Box without scrolling.

[ADezii]

I'm sorry - I don't think I clearly described what I wanted in the first place. At the moment, I have one Multi-Select, Single Column List Box.

All I want is to have the Single Column List Box be a Multi Column List Box so that I can see all of the items in the List Box without scrolling.

Assuming that it could be done, it would make no sense converting your Single Column. Multi-Select List Box to a Multi-Column List Box. You would loose you Multi-Select capability, and if your chose a Row in the Multi-Column version, which value in which Column would you want returned? Suppose you wanted 2 values selected in the same Row - couoldn't be done. For viewing purposes only, you could create a 2nd List Box (Multi-Column), based on the same RowSource as the Single Column. The code has already been shown providing this functionality. I'm sorry, but this is the best explanation that I can provide.

[epid]

Thanks again for all of your help. I will either hide the Listbox and use the code written in post #6 or just hard code the list into the table and use regular check boxes.