Tips and tricks for designing forms and reports - part 3

This is the final post of a three part series on Access Tips and Tricks provided by members of the Access team. This article focuses on non-obvious things new to the forms and report designers. The other articles in this series covered useful keyboard commands and a general topic on new databases, the nav pane, import/export, and more. Another previous post that might be interesting to you was my post Ideas about great looking reports.

My next post will likely be a follow up by Ric on the technical details to his previous post on the books database that uses the Amazon web service. I hope you enjoyed this series.

Enjoy!

Clint

Custom AutoFormats

You can create a custom AutoFormat based on the current form. This was useful to me when I was customizing a template and didn’t want it to look exactly like the ones out of the box. With your form or report in Layout or Design views, under the Format contextual tab, drop down the list of AutoFormats and choose AutoFormat Wizard… Then click on Customize and choose “Create a new AutoFormat based on the form ‘Current’.” It requires some effort to get it exactly right, but what I did was make a form that had all the elements I might need (logo, title, labels, text boxes, etc.) and create the AutoFormat based on that, then apply it to all the other forms/reports. –Abigail (form and report developer)

Layouts

I’m a big fan of layouts and anchoring for creating forms that use the available real estate. However, it gets really annoying when a layout tries to suck in a control as it gets close to the layout. Why doesn’t Access read my mind and know that I didn’t intend for that control to go into the layout? Grrr!!! We tried to implement the “read my mind” feature but didn’t get very far!

As you drag controls near stacks, hold the CTRL key to keep fields from being sucked into stacks. This makes it possible to put red stars next to text boxes or move boxes behind controls to provide visual separation.

Another tip—you might notice all buttons in command bars are in stacks. We put them into stacks so that they would resize correctly in localized builds at instantiation time.  Feel free to get rid of the stack—it doesn’t provide any functional value once the template is opened.

One last command I found really helpful when designing reports--on the right click menu for a control in layout and design mode there is the Layout | Move Up a Section and Move Down a Section. I have found this more useful than I expected when you are experimenting with different groupings.

Truncated Numbers

Ever had someone make a bad decision because a number was truncated?  There is a new property called “Check for truncated number fields.”  When this option is enabled, numbers appear as "#####" when the column is too narrow to display the entire value. When this option is not enabled, you see only part of the values in a column.

Transparent Images and Smaller Databases

If you have an existing mdb database, you know that adding images dramatically increase the size of the database. You also know that the images don’t preserve transparency. You set the option in the Access Options | Current Database. Here is what the help file says about preserver image format:

• Preserve source image format (smaller file size)   When this option is selected, Access stores images in their original format. Select this option to reduce database size.
• Convert all picture data to bitmaps (compatible with Access 2003 and earlier)   When this option is selected, Access creates a copy of the original image file in either the Windows Bitmap or Device Independent Bitmap formats. Select this option to view images in databases created in Office Access 2003 and earlier.

Binding Images to External Files

It is a royal pain to bind the image control to external files. The code is messy and sometimes the JPG filter doesn’t work correctly. Fortunately, late in the development cycle Brian made it possible to set the control source on image controls to UNC paths. Give it a try—create a text field that contains UNC paths to pictures in your favorite image library. Drop the image control on a form give it a run.

Easy Database Lockdown

I know, the runtime hasn’t shipped yet. If you are looking for a simple way to share a database with co-workers and you don’t want them messing around with things… Try renaming the file to ACCDR. This is the equivalent of running the database with the /runtime switch. Basically the ribbon and nav pane get turned off.

Save Embedded Macros as VBA

Access 2007 introduces a new type of macros called embedded macros. Embedded macros are macros that are stored on an event instead of as a separate object. Embedded macros support name fix-up and are used extensively through-out our templates. They are largely targeted to information workers that don’t write code but useful for developers that are trying to perform some simple actions.

I admit some types of macros are easier to maintain as code for developers. Thanks to a late DCR it is possible to convert all the macros in a form or report to VBA. Open the object in design view and Go to the Database Tools tab. Click on the Convert Form’s Macros to Visual Basic.

Hyperlinks and Hand on Hover for Buttons

Hyperlinks have a new Display as Hyperlink property that doesn’t munge # signs. It also prints hyperlinks as black text instead of blue underlies. Also, buttons have some new cool properties including transparent and a hand on hover property. On February 24th you will see new templates get released that use the new images and text and this property. (Abigail – Form and report developer)

Developer Help

End user and developer help is separated out so that end users don’t get confused with developer topics and developers don’t get bored with end user’s topics. You can easily switch between the two with the search dropdown.