Check Box List - Vertical?

[qwerty99]

I have a check box list on my form for all 50 states, so the user can choose any number and any combination of possible states, which I need the user to be able to do, but when it displays the data it does so horzontally and delineated by a comma. The problem with this is that if enough states are selected, it eventually gets cut off at the end and doesn't display the last states. Is there any way to make it instead show vertically, since at least with that I can format around it and not have a horizontal bar going all the way across the form? I've gone through every google search I can think of and come up with bupkis, so I'm not sure this is something Acces will even allow.

Thanks!

 

[Cronk]

I am not sure what you want and where you want it.

You have a form with 50 check boxes and the user can check any combination of these.

You have a command button then when clicked outputs to a text box on the screen, a string of the States represented by each of the checked boxes comma separated.

You now want to have the output arranged vertically so the selected States are listed one per row.

If so, I'd replace in the string generator, the ", " separator with vbCrLf and have a tall text box.

Or, change the string generator so that it generates SQL source for a list box.

 

[qwerty99]

I may not have explained what I have very well. What I have is a combo box that when you hit the drop down arrow a list of 50 checkboxes shows up. After selecting the appropriate states and hitting enter it then displays in the format: Alabama, Colorado, Nevada. What I would like is for it to display as a tall box that shows all the selected states, with one on each line.

Assuming what you said is still applicable, where do I change/find the string generator? Is that the Row Source?

 

[Cronk]

I'm still unsure about what you have. Check boxes on a combo? A multi select combo? Maybe post the database without the data.

The string is being generated in some VBA code which runs on the After Update event.

What events can you see on the properties form for the combo?

 

[qwerty99]

Attached is my database. Under the form "ICinfoF" the "State" field is the one I'm talking about.


There are no events on the properies sheet that I can find.


Thank you so much for your help with this!

 

[Cronk]

I see the issue now and I am not going to be able to help you much.

You have a 2010 database with a combo lookup in text field State1.

I do not use Access 2010. I personally don't like the concept of lookups placed in table design as the real data being held is usually not obvious.

What you can do is to add a list box to your form bound to the field. This will show the selected states in a column, rather than a single line.

 

[Cronk]

Pat,

I agree with your sentiments about multi-value fields. I'll never use them in anything I set up.

I'm curious though. Can you query on the multi values? For example, find all records that have a particular state selected.

 

[CJ_London]

I agree - an abomination - I've seen bad design examples where they have been used even when only one value is to be selected - e.g. choice of 50 counties in an home address scenario - as far as I know you can't be in two counties at the same time!

You can use them in a query as criteria but you can end up with multiple rows which is a real problem if you are trying to sum or count (although does depend on the requirement) leading to over summing or counting

 

[Cronk]

CJ

There can be situations where there would be a need to store multiple states/counties against an entity.

For example, a wholesaler supplying a number of distributors who have outlets in more than one, but not all states.

 

[CJ_London]

I quite agree regard multiple states in that situation (tho' I would still handle it in a separate table) - I was referring to a db I was asked to fix where the previous developer had used the allow multiple values property for the user to select the county for the customers home address - basically he wanted the user to tick a box rather than select a value for a list

 

[Cronk]

Pat

As I said, I was curious whether it could be done. I'll never have to worry about figuring the SQL because I'll never use multi-value fields, nor look ups on a field for that matter.

 

[CJ_London]

Going back to the original post, I suppose one way you could do it is to have a separate unbound listbox (perhaps set just below the combobox) with a rowsource type of value list.

Then in the after update event in the combobox you can assign the field to the listbox rowsource. Not sure if it would work but could give it a try - since the listbox needs to display text you may need to surround values with quotation marks (use the replace function and top and tail with quotation marks)

Still give you a problem if more states are selected than can be displayed in the listbox so the next thing you will need to do is use VBA to adjust the height of the listbox- although you can use the scroll bar to scroll.

 

[qwerty99]

I would prefer to stay away from having to put a subform on the form due to space restrictions, but I like the idea of having a combobox update a listbox to show all the states. How do you set it up so that the row source in the list box is what is selected in the combobox? I looked around the internet and found others doing similar things, but I'm having a hard time trying to transfer that knowledge over into my DB.

Thanks everyone for your comments and help!

 

[CJ_London]

Then in the after update event in the combobox you can assign the field to the listbox rowsource.

If your combobox has a control source called 'States' and your listbox is called 'SelStates' your code would be:

SelStates.Rowsource=States