Setting the List column width/horizontal scroll issue

G

Guest

MS Access 2K, Windows XP
====================
Hi,

I have a list on a form and I'm displaying some data from a query. Some of
the columns in the list are 0" width as I don't want to display them to the
user. I've specified the width of all the columns under Column Widths. The
Column Heads is set to Yes.

When the form is opened, the last column is much wider than what I
specified. If I scroll horizontally, the width of the last column is
approximately the width of the list (6") instead of 1" as specified.

I've double-checked the Number of Columns, and the Column Widths to make
sure that I'm not missing specifying the width of a column, or have more/less
columns specified than in the query.

I'd like to have the column width be equal to 1" (what I specified) instead
of 6" (what it is).

An internet search did not turn up any similar issue, and I've played around
with the Properties somewhat (setting Column Head to No) w/o any luck.

I did not see a Property listing that would specify the horizontal
"scrollable" width of a list.

Is this a list "feature"? Have you experienced something like this, and/or
know how to fix this? I will appreciate any help with this problem.

Thanks.

-Amit
 
G

Guest

Hi, Amit.

Add up the values of the Column Widths Property of the List Box control.
Subtract this value from the Width Property, and you'll see how much extra
width the last column gets when the list box is too wide. Adjust the Width
so that it equals the values of the Column Widths, and you'll get rid of this
wide margin in the last column.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
G

Guest

Van T. Dinh said:
You need to set the appropriate value for the ListWidth also.

Van,

Thanks for your response.

I've already tried that (setting the "Width" List property it to 13" -
that's the total of all the column widths) but what it ends up doing is
extending the list width to 13" (and extends the Detail section of the form
to accommodate the expanded list) which is not what I want. I would like to
keep the list width (displayed area) to 6" (or so) and the horizontal
scrollable width to be 13". Right now, the scrollable width is 18"+ which
makes the last column width 6" and not 1" as specified in the properties.

Please let me know if you have other ideas.

Thanks.

-Amit
 
G

Guest

'69 Camaro said:
Hi, Amit.

Add up the values of the Column Widths Property of the List Box control.
Subtract this value from the Width Property, and you'll see how much extra
width the last column gets when the list box is too wide. Adjust the Width
so that it equals the values of the Column Widths, and you'll get rid of this
wide margin in the last column.

Ah, my favorite Access Expert :)

You must've read my response to Van by now which may explain things.

In my List Properties, I have:
Column Count: 21
Column Widths:
0.5";0";0";0";2";1.25";1.25";0";0";0";0.75";0.75";1";1.5";0";1.5";0";1.5";0";1";0"
Width: 6.6667"

The total of the column widths comes to 12".
If I change the Width to 12", it expands the viewable list area to 12" and
correspondingly, the form to 12"+. Which is not what I want.

I'd like to keep the width of the list "window"/"viewable area" to 6.6667".

Not sure if I'm missing something simple here......

-Amit
 
G

Guest

Hi, Amit.

Sounds like I've made some fans. :)

Now that I think on it, Access 2000 wasn't very cooperative with list boxes
that weren't the same width as the columns. To avoid any problems, I just
made sure that the form was wide enough to accommodate the list box -- so
that no horizontal scrolling in the list box was necessary. The good news is
that Access 2003 allows one to adjust the widths so that this annoyance when
scrolling the list box doesn't happen.

Until you get a chance to upgrade to a newer version (I'm not sure that 2002
is any better than 2000 on this), perhaps placing the horizontal scrollbar on
the form so that the list box can be as wide as it needs to be will work?
Okay. That's probably even more annoying!

How about using a subform in Datasheet View instead? That's 11 visible
columns you have, so it wouldn't be overkill to use a subform. You'll still
need to scroll to see everything, though.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
V

Van T. Dinh

The last column always seem to expand to the width of the list if you keep
scrolling right. I guess Access got to fill the blank space if the user
keeps scrolling right.

Unfortunately, this behaviour always occurs if the list is horizontally
scrollable, i.e. when total of column widths > list width.

OTOH, I don't recommend using that many Columns in the List since this
forces Access to fetch a fair bit of data. I am not sure how many rows of
data Access fetches for the ListBox but certainly enough to fill the height
of the ListBox + a fair bit more. If I need 10 "hidden" values to do
something else with these, I would use the AfterUpdate Event to fetch these
10 values.
 
G

Guest

Hi Gunny and Van,

I guess till I upgrade to Access 2003, I'll have to live with this list
feature.

Van, the reason I have some columns with 0" width is because I need that
data when exporting it to Excel but I don't want to display them on the
form/list (they are IDs). Hope that makes sense.

Gunny, I will try out your idea of using a subform to display the data
instead of a list.

Thanks again to both of you for taking time+efforts to respond to my post.
You guys rock!! :)

-Amit
 
V

Van T. Dinh

I guess you have a different set-up from what I would normally do: I would
use a Query for Export to Excel. Since the Query gets the data from saved
Tables, the number of Columns (hidden or not) shouldn't have anything to do
with the Excel export.
 
G

Guest

Van T. Dinh said:
I guess you have a different set-up from what I would normally do: I would
use a Query for Export to Excel. Since the Query gets the data from saved
Tables, the number of Columns (hidden or not) shouldn't have anything to do
with the Excel export.

Yes, you are right.
Since I need to display data on a form in a list AND export the *same* data
to Excel (the form has some search criteria that go into the WHERE clause of
the query which becomes the row source for the list), I thought that I could
simply export the data displayed in the list to Excel. But, when I tried to
do that, I found that I'll need a query (or a table) as TransferDataSheet
does not work with a SQL statement.

Thanks.

-Amit
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top