Better Looking Forms & Reports, Faster
Building modern looking data forms & reports in Access 2003 can be difficult. Doing the heavy lifting is easy - there are great tools for building the data-bound portions of the form - but making the interface look great can be a challenge and can require some creative hacks. UtterAccess even has a popular board devoted to helping people build better looking interfaces. In Access 12, we've built a number of features that will help people build great looking apps with far less code, time and effort.
Better Image Support
We did work to improve native support for image formats. In the past, you could add pictures but there were some serious limitations. First, when the picture was added it was converted from its native format to a device independent bitmap (dib). This resulted in significant bloat if the image stored data in a compressed file format such as gif and jpg. Even worse in many cases, when the image was converted transparency was lost.
Access 2007 now supports BMP, GIF, JPEG, Exif, PNG, TIFF and DiB without bloating the database size. Images with transparency work great. You can see how we're using transparency below - the buttons are all transparent, as is the image in the title bar.
To ensure that database are backwards compatible, we created a new database property called “Preserve image format.” This property controls if the image is converted to a dib or stored in its native format. ACCDB databases default to storing images in their native format while MDB and ADP database Access 2007 will continue to convert images to DiBs so that the image is available when the form is used by older versions of Access. In a nutshell—if you want your image to be displayed in previous versions of Access make sure preserve image format is set to false. I recommend turning off the property if your app is deployed as a runtime or in an Access 2007 only environment.
Another painful limitation of former versions was displaying images on a form or report that were stored in the file system. The Northwind employees form had 135 lines of code to make this work. Now the image control can be bound to a UNC path including jpg and png files.
AutoFormats
AutoFormats are a great way to create a consistent look across an application, but the existing themes were looking pretty dated. In Access 12, there are 25 new themes that you can choose from that will add that final polished look to your application. The Quick Format gallery gives a small preview of the font and color schemes:
Other New Look Improvements
In Access 11 the SizeMode property provides enums for Clip, Stretch, and Zoom. These properties do not allow us to create really small horizontal or vertical images that tile across the header. The 2003 behavior was to stretch the image across the entire form or report. In many cases the right effect is to stretch horizontally. There are now two new enums: Stretch Horizontal and Stretch Vertical. This makes it possible to stretch an image across the header of the form when it is the Picture property on the background of a form or report.
We also did work to make the grid control more modern and remove the Windows 95 3-D look. Internally, we draw the grid colors from the exact same color palette as Excel.
A few weeks Deano asked about tab controls:
“Also will tab controls blend correctly when you change the colours? I use Access 2000 and there's a section in the top-right of the tab control that does not match the changed form colour.”
I’m pleased to say that the color now matches the form color. Here is a screen shot from WinXP:
We made a one change to buttons that I think folks will find useful. There is a new enum for backstyle called Transparent and a new property called HandOnHover. Using those two properties and you can create buttons that look like hyperlinks. BTW – have you seen the Vista buttons? I think they look great in our shell.
Cyrus asked about pixel drawing problems on disabled images. This problem has been fixed in builds after beta 2 if preserve image format = false. We remove all the color from the image.
There is still a some button work we want to consider in the next version. Cyrus—we hear you on button background colors and images and text. Another thing we wanted to do this version but didn’t get to was color properties for different mouse over states.
New Attachment Data Type
If you want to store images directly in the database—the attachment data-type and control works great. In the past users would use the OLE Object data type. Because of how OLE worked there were bloat issues as it had to store a preview of the file along with the OLE data. Attachments are a new complex data-type that stores multiple attachments to a record in a binary field in a hidden table. We compress the file when it is added if it isn’t already a compressed file type. Here is the dialog to manage attachments:
When the image has focus there is a helpful floating toolbar that allows you to navigate through records and launch the dialog.
There are three display as enums: Paperclip, Image, and Icon. The icon will always show up as a paper clip in datasheet view.
Attachments are exposed in QBE similar to other complex data forms. Users can query for the collection or the expanded version returning a row for each attachment. Let me walk through an example with a simple issues database with one record that has 4 attachments. The query designer exposes three columns that are stored in the hidden complex data table: FileData, FileName, and FileType. Double clicking on the root Attachment node will add the attachment collection to the grid below.
This generates the following SQL:
SELECT Issues.Title, Issues.Attachments
FROM Issues;
The query returns the collection of Attachments.
If you add the Attachment.FileName to the query you get the expanded results set. Here is the SQL:
SELECT Issues.Title, Issues.Attachments.FileName
FROM Issues;
The datasheet now returns the expanded results:
The collection is exposed through DAO so that you can modify and update it. Here is a short code sample:
Private Sub cmdAddImage_Click()
Dim rsEmployees As DAO.Recordset2
Dim rsPictures As DAO.Recordset2
Set db = CurrentDb
' Get the parent recordset
Set rsEmployees = Me.Recordset
'Put the parent record into edit mode
rsEmployees.Edit
'Get the attachment recordset
Set rsPictures = rsEmployees.Fields("AttachmentCell").Value
'Set first attachment to loaded picture
rsPictures.Edit
rsPictures.Fields("FileData").LoadFromFile ("C:\FileName.jpg")
rsPictures.Update
MsgBox "Picture added"
' Update the parent record
rsEmployees.Update
End Sub
The attachment data-type is available in ACCDB files and link tables to WSS lists. It is not available for MDB and ADP databases. We didn’t expand the 2GB limit for Access databases. If you think you will have lots of data in your app break out the attachment column into its own database and use a link table to join it with the table.
Next Time
In the next big post, I'll look at Filtering and Grouping. In the meantime, I'll have a smaller post about searching in the navigation pane.
