Share via

Tips and tricks for designing forms and reports - part 3

  • Article

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.

Convert macros to code.

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.

Seach scope for end users and developers.

Comments

  • Anonymous
    February 24, 2007
    The comment has been removed

  • Anonymous
    February 24, 2007
    Hello Clint I like your blog  .... very much!    ;-) But I don't like Access 2007: I've tried it, but it's unusable ... and not only for the Ribbons. I think Microsoft developers have tested this product only with Northwind.mdb on IBM Blue Gene mainframe. Access 2007 doesn't work properly: too much memory usage, an annoying new look and low, low, low, low interface performances. I've converted a big, well written Access XP/2003 application to Access 2007 (an evaluate version): PC:   Pentium III  1.5 Ghz, 512 MB of RAM, Win XP S.P2 Front End .MDE 2003 version: Tipical memory usage:    25 to 30 MB performances:                good performances (acceptable also on Pentium II architectures) ............................................................................................................... Front End .ACCDE 2007 version: Tipical memory usage:    50 MB and more performances:                low performances (disappointing also on Pentium IV) More in detail: Converted shortcut menus doesn't work in subforms datasheet. Scroll speed of continuous forms is slow. In forms with tab controls there are often annoying flickerings. Load performances of UNBOUND forms are relatively slow Load performances of BOUND forms are, obviously, slow. Close performances of BOUND forms are slow. Performances of BOUND forms with dozen of textboxes and comboboxes (that work fine with previous versions) are very, very, very, very slow: also the movement between form-fields is slowest ... all it's slow in this new release. Clint, sorry but this isn't Microsoft Access but an Alpha/Beta version of FileMaker! It have no sense that Microsoft continues to develop this product ... if this are the results. It have no sense planning version 14 when 12 work badly . The development of MS Office suite (and, why not, Access) must be a "mission critical" work and NOT a game. In Microsoft strategy Access isn't (only) a developer product? OK, but I don't think that needs of final users contemplate iper-slow interface performances. Access 12 it seems to be a software for year 2015, certanly not for the 2007. David McCook

  • Anonymous
    February 24, 2007
    The comment has been removed

  • Anonymous
    February 24, 2007
    I just started reading your blog Clint - seen your name a lot from the old days of MSDN - always had healthy respect for the volume and quantity of your writing. I like your response to David - professional, to-the-point and human. Keep up the good work. With regards Office 2007, Access 2007, I haven't had enough time to play - I'm running it in a virtual machine so that my production Access, Excel multi-version environments don't get contaminated. I use vms and Altiris SVS to keep things sane and tidy. My previous tests with Altirisi SVS is that it copes with Office 97, 2002, 2003 but fails with Office 2007. I see Access 2007 has woken up (and so has interest from within Microsoft) from the slumber post Access '97. Here's hoping for good success in the Year of the Pig.

  • Anonymous
    February 26, 2007
    thanks for the props Ananda. Your name seems familiar. Did you use to post in the Data Access Page newsgroup? You know, I always feel terrible when we ship bugs in the product. People spend hours and days trying to work around stuff--the team feels that responsibility. Unfortunately, it is part of shipping any software especially something as large as Access. If there is something we can do to fix a perf issue for David--that would be great. It is exciting to see renewed interest in Access at Microsoft.