?
Solved

Fields too far apart in Access when stacked

Posted on 2011-10-18
5
Medium Priority
?
535 Views
Last Modified: 2012-05-12
I am using Microsoft Access 2010. I have a database where I have a form and a report that lists columns of fields. To line up the fields, I use the Layout, Stacked command. However, in the past year or so (I think even when I was using Access 2007), when I use that command, the fields are spread too far apart. There is too much vertical space between the fields and I have to manually move each field to make them closer together (which defeats the purpose of the Stacked command). Access used to nicely stack them so there was not a lot of vertical space between the fields. I can't figure out why this is happening or how to fix it so it stacks them close together like it used to do. The database is in Access 2003-2003 file format. So, I thought maybe the problem was the format and I converted the database to 2007/2010 format but I still have the same problem.
0
Comment
Question by:mauldlouie
  • 2
  • 2
5 Comments
 
LVL 85
ID: 36991676
I'm not sure how Access determines the layout and position of the controls with these new layout paradigms, but I'd be willing to bet it's based in the screen resolution to some degree, and your machine may simply have an odd resolution/layout. I personally don't use those design paradigms - they're more trouble than they're worth, at least to me - so can't really say I've done any great amount of research on this.

Have you made sure that your Access/Office app is fully up to date, and that you've installed any needed service packs and hotfixes? Might consider doing the same for Windows as well, since these tend to roll up quite a few minor issues in one package ...
0
 

Author Comment

by:mauldlouie
ID: 36993750
Yes, all my Office software and Windows is fully updated. My screen is a 23" widescreen, running at the optimal resolution for the monitor. Not that odd since widescreens are pretty much all you can buy these days. The Access layout options save me a lot of time when they work right, since there are dozens of fields that I need to stack up on the form and report and I need them evenly spaced and the same size. And it saves me a ton of time when I have to add a field or 2 to the database. When the stacking command does't work right, it can take me 5x longer to add a new field since I have to manually move all the existing fields to make room for the new one and then manually stack them all up again and get them evenly spaced..
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 36994463
LOL

Yeah, I noticed this too...

I don't know if there is a real "Fix" here though...
...Perhaps just the way it is...

Trying to create a reporting engine functionality that will create "good" looking Reports with any number of fields and datatypes is probably a real challenge.

Worth noting here is that the Field size property of a text field will dictate (to a large extent) how "Wide" the textbox will be.
The default in Access is 255 by default, resulting in the widest textboxes.
So you should set this to about 25 in the access options...

To be clear, the new "Layout" feature in Access can be both a Godsend and a source of aggravation if you don't know all the consequences.
(for example you can easily adjust the spacing by using the "Size/Space" option under the "Arrange" tab)

For me, a lot of the time it is sometimes just simpler and faster to just use the wizard to recreate the report with the new field(s)

Finally, just bear in mind that while this may be time consuming, it should not happen all that often.
Try to hold off on creating your reports until you are reasonably sure of all the needed fields.
Meaning it should not be everyday that you are adding fields to the report.
So if this takes 10 minutes to fix, it should be a while before (or even if) this will be needed again.

Just my feelings on the matter


JeffCoachman
0
 

Author Comment

by:mauldlouie
ID: 37003570
Actually, most of the fields are yes/no fields, not text. The database is tracking permitted security rights for users--not just Windows rights but all sorts of rights, including physical access to the server room and access to various databases on Linux servers. We have to document this for every user for the IT auditors (SAS 70) and have the manager sign the report for each user. When I do the stacking, it makes them nicely aligned with checkboxes on the right. I didn't know that you could adjust the vertical spacing under Arrange. I just tried that and it fixed the spacing on the stacking :). So my problem is solved. I did have to remove the Layout before I decreased the vertical spacing, but that's fine.

I can't use the wizard to re-create the report because it would not place the fields where I need them and I have headings within the single page of the report for the different types of security rights. So the report needs to stay the same except for new fields being added and old ones removed, as our infrastructure and software changes. Depending on what section of the report the fields are added/removed from, that affects how many other fields have to be shuffled around. So it can take a while to reformat the report and form. If the stacking feature works right (and now I know how to decrease the vertical space), that greatly speeds up the rearranging of the fields.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37004342
<most of the fields are yes/no fields, not text. The database is tracking permitted security rights for users>
Then as an FYI, it may be easier to use listboxes for this kind of data.

Then you can display all the Yes values for the record in one control and all the No values in another.
This will require less form/report real estate, and require *NO* maintenance when fields are added, edited or deleted...

JeffCoachman
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question