listbox column goes forever.....

[Kevin_S]

This one is an easy one but a stumper as I cannot find the answer in either the help files, the Access Cookbook, or this forums' past posts so....

What is the property used to keep the last column in a list box from being really, really long for no reason?

I have set the last columns width to 1 in but it is huge and as a result makes the horizontal scroll function way, way longer than it needs to be. I have also tried to set the listbox width and this has not solved the problem either.

Anyone know the way to get around this?

Thanks in advance,
Kev

 

[KevinM]

Post your Column Count, All Column Widths & List Width.

 

[Kevin_S]

Hi Kevin - thanks for the reply...

Here is what I have for one listbox:

Me.List0.ColumnCount = 6
Me.List0.ColumnWidths = ".75in;.75in;1.5in;1in;1in;1.5in"

Where is the listwidth property?

When I type in me.List0.**** I don't get an option to use the listwidth prop???

Additional Info - I am setting the record source for the listbox dynamically on the fly dependent on the users selection in an option group - however, I don't think that is something that will be a problem with this case as I am resenting the columncount and columnwidth for each selection....

Thanks for the continued help on this,
Kev

 

[KevinM]

I've never worked in ins (always cm), but don't think thi sshould matter in your case.

I have a similar setup where the rowsource is dynamic, but have found in some cases that I need to set the columnCount and columnWidth properties in code too.

Check that the columns returned in the rowsource code matches your values.

ListWidth is simply known as width in the ListBox properties. Try adjusting this first.

 

[Pat Hartman]

Check the ListWidth property. It defaults to auto but you might have set it to something specific and your setting is too wide for what is actually being displayed.

 

[Kevin_S]

First - thanks to everyone for the help...

Second - still no luck!

I manipulated the Listwidth property per suggestion and while that does change the size of the listbox (the physical display size) it does not get rid of the "run on" column and the far right of the list box.

I have made sure that for each listbox the columncount and columnwidth is set and everything but I still get that super long field to the right in the lisbox display....

Now - The listbox is smaller than the amount of fields I am displaying (on purpose since the form is small and the fields are numerous) so the scroll feature is used but I can't figure out why the last column is sooooooo long....

Any/All help is appreciated.

ps. If it would help I can post an example of this as well.
thanks -Kev

 

[Pat Hartman]

Maybe you should post a picture of the expanded listbox. If you don't already have software that will do this, you can download a trial version of Snagit from www.techsmith.com. This is a very good product with a lot of features and well worth its price.

 

[Kevin_S]

Hi Pat

First of All - GREAT PRODUCT! I am testing the free download now - but I can already tell this is going to be a keeper.

I have attached the screenshot to this post. As you can see in the image the last column just goes on and on AND if you notice where the scroll bar is in the pic its not even all the way to the far right!!!!

I have set all of the column widths to 1 in and in every listbox I have (dynamic rowsource or not) the last column in everyone is really, really long...

please help!

kevin

 

[Kevin_S]

Opps!

It didn't attach...

trying again (crosses fingers)

 

[KevinM]

Looks like column 2's width is set TOO high.

Are you setting these values in vb or in the list box properties?

Might help to post a sample db with just the required objects.

 

[Kevin_S]

Hi Kevin - thanks for keeping interest!

I tried changing everything to cm's and that didn't do it either. Basically, if all of the columns fall within the listbox's physical width (i.e. no horizontal scroll necessary) then everything is peachy BUT...

If the field width or column count is such that Access puts in a horizontal scroll then I get the giant field to the farest extint to the right?!?!?!

What is ever more strange is that I can control all the other column's widths with the columnwidths prop but the last one - no matter what I set it at *even 0* is super, super wide.....

does anyone else experience this same phenomenon?

help plzzz.....

Kev

 

[WayneRyan]

Kevin,

I've never seen that before.

Is it possible you can compact, ZIP and post a sample?

Wayne

 

[Kevin_S]

Hi Wanye -

I have attached the form and tables in question. To get the listbox to populate - select an option in the toggle - then - select from the option group a query to sort the listbox (View Vacanct Positions) was the one from the screenshot) and then select the first entry in the combo box (Algonac) to sort...

Scroll to the right of the list box to see the forever - running column

The queries are nested in vba in the on click event for the option group(s) (there are two setting on top of one another (optval and optval2) For each query I set the colmn # and widths prior to running each query so I can't figure out why this is happening.....

hopefully you can provide some help on this !!!!!

Thanks in advance -Kevin

the file is in A97 format

 

[KevinM]

I have come across this in my own dbs.

Usually it's either a typo on one of the column widths, i.e. it's set ridiculously high or the width of the list box is set to less than that of the column widths added together.

My guess is the latter.

Set the width manually in the property rather than just expanding it with the mouse.

 

[KevinM]

YEP, it was the latter.

Your column widths total 24.13cm
and your listbox width is set to about 14cm

You need to reduce the column widths or....

get a larger monitor

 

[Kevin_S]

OK - now we are getting somewhere

The width of the listbox is indeed less then the width of the column widths. This was done because some of the querys/filters for the listbox are up to 12 fields long and I want to have smaller forms (due to differing screen sizes and resolutions) so I want the user to just have to scroll to the right when they are viewing larger queries - BUT I can't figure out why it has to be that the last field has to be so long... that just seems dumb to me

Is this just the way it is? If you listbox width is less then the column width then the last field is super big???

It seems that it should be set up so that if the listbox requires a horizontal scroll then the scroll ability stops at the last field?

thoughts?

K

 

[Kevin_S]

So... to summerize:

You either go with -
(1) a huge listbox - even though some queries are small in fields or...

(2) A scroll bar that runs into oblivion for no good reason...

If those are the only two options then that has to be a design flaw on Microsoft's part....

Anyone have any ideas on how to correct this????

 

[KevinM]

Yes, i think that's the way Access just does it.

I think you can reduce some of the column widths.
Remove the Column Headings and use labels instead or rename them in the query.

e.g. EmployeeID could be Emp.ID and Work Group # could be Wrk Grp#

Also you could concatenate Last Name & First Name into ONE column.

All this will help in reducing the max width required for the columns.

 

[Kevin_S]

Thats probably what I will have to do but I was trying to make it as user-friendly with regard to column headings and what not. I think that is really stupid of Microsoft to set that up like that and I hope it is something that is addressed in the next version. I am going to e-mail customer support right now and voice my displeasure with that control.....

Oh Well (shrugs) What can you do?!!!

Thanks for the help Kevin - I appreciate it!

If anyone has a suggestion to get around this issue I'd love to hear about it!

thanks again -kev

 

[WayneRyan]

Great work guys,

I had just moved it into a blank form, changed its rowsource
to a value list and was just about to go crazy myself!

Glad I checked back. learn something every day.

take care ...l

Wayne