Chris Weber shows you how to let your users to zoom in and out of datasheet subforms.
One of my users recently asked if there was a way to zoom in and out of subform datasheets just like you can with the grid in Excel. I pointed out that the Format menu for a datasheet has a dialog that lets users choose which font Access should to use to display the datasheet, and the columns can be manually sized both vertically and horizontally. As you can guess, their response was less than enthusiastic since they would have to manipulate three different settings (font, cell width, and cell height) to get the result they wanted. So, I added a pair of buttons labeled Zoom In and Zoom Out that would not only change the font size but also adjust the cells’ height and width so that the data would continue to be displayed after the font size was increased.
Just in case you think that I promised too much, I should point out that I had already written the second half of my proposal (the part about making the cells large enough to display the data in the new font’s height and the width)–long-time subscribers will remember it from the September 2001 “Access Answers” column. I’ve improved on my original design and incorporated the technique into my zooming solution as a generic procedure. To round things out, in this article I’ll also throw in a few lines of code that allows a subform datasheet to occupy all of the space available on the main form whenever the main form is resized.
One step at a time
When resolving this problem, the first thing I tackled was making my subform datasheet occupy as much space as available. Figure 1 shows a main form with a subform (Northwind Employees and their Orders) in design view. As you can see, all of the controls reside in the form’s detail section and the Employee information occupies the first inch of the detail section’s height. You can also see that the subform is offset by about an eighth of an inch from the edge of the form. I’ve set both the Default View and Views Allowed to Datasheet for the subform (named fsubOrders) that’s inside the subform control.
Figure 1
When the user changes the size of the master form, the master form’s Resize event fires. Since I wanted to maximize the subform when the master form resizes, I added these lines of code to the resize event of the main form:
Private Sub Form_Resize() On Error Resume Next Const Inch = 1440 Me.Section(0).Height = Me.InsideHeight With subOrders .Height = Me.Section(0).Height - (1.125 * Inch) .Width = Me.InsideWidth - (0.125 * Inch) End With End Sub
In this routine I set the subform control’s Height and Width properties to the Height of the detail section (minus the inch occupied by the Employee controls) and the InsideWidth of the main form. In both cases I’ve also subtracted an eighth of an inch to accommodate the design offset I pointed out earlier. The On Error Resume Next statement at the top of the routine is a key part of this code. If my user shrinks the main form so that the main form is smaller than the design surface of the subform, an error occurs. However, I ignore the error because the user can’t see the subform once the main form is that small.
The zoom functionality is implemented through the pair of buttons shown in Figure 1 that have the plus and minus sign captions. Zooming is handled by my DatasheetZoom routine. The routine must be passed two parameters:
- A reference to the subform (called subDataSheet).
- A Boolean value (called ZoomIn). When set to True, the code increases the font size and the cell dimensions (“zoom in”). When False, the code decreases the font size and cell dimensions (“zoom out”).
Within DatasheetZoom, I’ve declared two constants that represent the minimum and maximum font sizes that I’ll allow my users to implement:
- cFontHeightMaximum is the maximum size allowed when zooming in.
- cFontHeightMinimum is the minimum size allowed when zooming out.
Each time the user calls the routine to zoom in, I increase the current font height by 25 percent. Similarly, each time the user calls the routine to zoom out, I decrease the font height by 20 percent. The reason for the discrepancy in the zoom in and out percentages is a matter of simple math. If the font is originally 12 point and I increase it by 25 percent, the new size is 15 point (25 percent of 12 = 3, which added to 12 gives 15). To get back to 12 point, I need to subtract 3, which is 25 percent of 15.
Here’s the routine:
Sub DatasheetZoom(subDataSheet As Variant, _ ZoomIn As Boolean) Const cFontHeightMinimum = 6 Const CFontHeightMaximum = 72 With subDataSheet.Form If ZoomIn Then If .DatasheetFontHeight < CFontHeightMaximum Then .DatasheetFontHeight = _ Int(.DatasheetFontHeight * 1.25) End If Else If .DatasheetFontHeight > cFontHeightMinimum Then .DatasheetFontHeight = _ Int(.DatasheetFontHeight * 0.8) End If End If End With DataSheetSizeToFit subDataSheet End Sub
The key property in this routine is DatasheetFontHeight, which accepts the integer value of the font height you want your datasheet to display. Since I allow the font height to range from a point size of 6 up to 72, the user has almost 12 zoom levels.
Once the font has been set, DatasheetZoom calls DataSheetSizeToFit, which handles setting the font size and width.
Making it fit
The last thing I needed to do was resize the datasheet cells to accommodate the new font height and subsequent change in the width of each column’s contents. In my original “Answers” column I used this code to adjust cell size to match the font of the data being displayed:
Sub DataSheetSizeToFit(subDataSheet As Variant)
Const SIZE_TO_FIT = -2
Dim qry As QueryDef, fld As Field
Set qry = CurrentDb.CreateQueryDef("", _
subDataSheet.Form.RecordSource)
For Each fld In qry.Fields
subDataSheet.Form!(fld.Name).ColumnWidth = _
SIZE_TO_FIT
Next fld
End Sub
This version of DataSheetSizeToFit has two restrictions. First, the form underlying the datasheet it’s working on must use a query or SQL statement for its recordsource. Secondly, the controls on that form must have the same name as the field referenced in their ControlSource properties. DataSheetSizeToFit uses that information to create an unnamed temporary query on the form’s recordsource. By enumerating through the fields in the temporary query, the code finds the like-named controls on the form and sets the ColumnWidth property to -2, which causes the control to automatically resize to fit the data it’s displaying.
While writing this article, it occurred to me that there’s a shorter way to do this in versions of Access from 2000 forward (and even in Access 97). Instead of creating a temporary query, my new method uses the Recordset property of the form to get the field list. Because I’m no longer referring to the RecordSource property, this new version handles forms based directly on tables:
Sub DataSheetSizeToFit(subDataSheet As Variant) 'Access 2000 forward Const SIZE_TO_FIT = -2 Dim fld As Field For Each fld In subDataSheet.Form.Recordset.Fields subDataSheet.Form!(fld.Name).ColumnWidth = _ SIZE_TO_FIT Next fld End Sub
Access 97 forms don’t have a Recordset property, unfortunately. However, I can duplicate the results by using the RecordsetClone property, which gives me a copy of the recordset being used by the form:
Sub DataSheetSizeToFit(subDataSheet As Variant) 'Access 97 version Const SIZE_TO_FIT = -2 Dim fld As Field For Each fld In subDataSheet.Form.RecordClone.Fields subDataSheet.Form!(fld.Name).ColumnWidth = _ SIZE_TO_FIT Next fld End Sub
Figure 2 shows the differences in the Orders subform datasheet on frmEmployees after zooming in and out, in conjunction with the main form’s Resize event code. A really nice feature of this technique is that Access automatically saves the settings made by my zooming code even if the user closes and reopens the database. As a result, once the users set their optimal zoom level, the database continues to use it. It amazes me how much work can be done in Access with so little code, provided you’re familiar with the available objects.
Figure 2
All of the objects and the source code referenced here are included in the database accompanying this article. If you use this technique in more than one place in your database, just copy DatasheetZoom and DataSheetSizeToFit from my database to a public module. But don’t tell your users how easy it was.
Other Pages On This Site You Might Like To Read
