You Can Do That with Datasheets?

Everyone who uses datasheets knows they’re powerful, but very few people know all of the things you can do with them. Michael Kaplan pulls out a handful of tricks from the unlocked Access 97 wizards that can help you really get the best of these useful items.

One of the things that even the most diehard Visual Basic developer admits is that they wish that Visual Basic had a grid as powerful as Access’s datasheet. And with all of that power, there are still many things that no one knows how to do with them. Datasheets are routinely dismissed by developers, who seem to favor continuous forms.

However, datasheets provide a simple yet powerful way to display data to your users. Datasheets also allow you to provide a user interface (UI) that your users can configure to meet their needs. Most developers feel that datasheets give too much control to their users. However, by taking control of your datasheets, you can incorporate them into your applications. In this article, I’ll show some of the features of these powerful UI objects that most developers don’t understand.

Using the “sorta hidden” properties

Datasheets have a lot of properties meant only for them, which are listed in Table 1.

Table 1. The properties in Access related to datasheets.

Property Meaning and usage
ColumnHidden Exists on columns in the datasheet, controls whether the column is visible or not.
ColumnOrder Exists on columns in the datasheet, controls the order in which columns are displayed.
ColumnWidth Exists on columns in the datasheet, controls the width of the column.
DatasheetBackColor Exists on the datasheet itself, specifies the background color for the whole datasheet.
DatasheetCellsEffect Exists on the datasheet itself, handles whether special effects are used for the cells (flat, raised, or sunken are the only effects supported).
DatasheetFontHeight Exists on the datasheet itself, this unfortunately named property specifies the font size.
DatasheetFontItalic Exists on the datasheet itself, controls whether all of the text is italic.
DatasheetFontName Exists on the datasheet itself, controls the name of the font.
DatasheetFontUnderline Exists on the datasheet itself, controls whether all of the text is underlined.
DatasheetFontWeight Exists on the datasheet itself, controls whether the text is bolded.
DatasheetForeColor Exists on the datasheet itself, specifies the foreground color for the whole datasheet.
DatasheetGridlinesBehavior Exists on the datasheet itself, controls which gridlines will be displayed (if any).
DatasheetGridlinesColor Exists on the datasheet itself, specifies the color of the gridlines.
FrozenColumns Exists on the datasheet itself, specifies how many columns have been frozen by the user (discussed later in the article).
ShowGrid Exists on the datasheet itself, but has been superseded by the DatasheetGridlinesBehavior property.
SubdatasheetExpanded Exists on the datasheet itself, specifies whether all subdatasheets should be expanded. (Access 2000 only)
SubdatasheetHeight Exists on the datasheet itself, specifies the number of records to display for subdatasheets (a scrollbar appears if there are more records than this property allows). (Access 2000 only)
SubdatasheetName Exists on the datasheet itself, specifies the name of the table’s subdatasheet. (Access 2000 only)
TabularCharSet Exists on the datasheet itself, and is hidden. It specifies the font character set and can often cause bad things to happen if it’s set to an incorrect value. It’s best not to set it, or to set it to 1 (which uses the DEFAULT_CHARSET for the machine).

With the exception of the Subdatasheet properties, you have no direct design-time access to these properties: None of these properties show up in the datasheet’s property sheet. As a result, they can only be set at runtime from VBA code in order to make changes. Interestingly, none of the properties are exposed by ADO or ADOX, so if you want to change them, you’ll have to use DAO.

While you can’t access these properties through property sheets, many of them can be set in the user interface. They are, for example, what’s changed when you set the font of a datasheet from the Format menu. For full control over the datasheet, though, you’ll want to explicitly set the properties in code and save the object when you’re done.

It’s worth noting that a datasheet is a form—it says so right in the object browser. The object browser considers the datasheet’s columns to be the controls on the form. As a result, a datasheet can consist of any control that can be displayed, which means all TextBox, ComboBox, and CheckBox controls.

Okay, enough introduction; I want to show you some of the cool things you can do with datasheets. Obviously, many of the datasheet properties need no explanation: Anyone can tell what a font or a grid property does. Instead, I’ll move to less obvious items of interest…

Hiding columns at runtime

Sometimes, you want to hide specific columns in a datasheet. For example, the Access ListBox/ComboBox Wizard does this. The wizard uses a generic, 20-column datasheet as part of its user interface. The wizard hides specific columns to limit its display to the number of columns appropriate to the sample data. Even if your needs aren’t quite so grandiose, you might want to hide all but the relevant columns on every display. You might even want to provide a bunch of CheckBoxes at the top of the datasheet to allow your users to hide and show columns. I personally find that kind of user-configurable UI really interesting.

To see this in action, you can check out frmHideShowColumns and sfrmHideShowColumns in the sample database in the accompanying Download file. The subform is just a simple datasheet based on the Products table from Northwind, while the form is unbound and contains check boxes to show and hide the various columns on the subform. The frmHideShowColumns form looks like Figure 1.

 
Figure 1

In truth, most of the work in this form was just in naming all of the controls consistently (I used “ck” as a prefix for every check box on the main form, “tb” as a prefix for every text box on the subform, and the actual field name for the rest of the bound controls on both the form and subform). Once the appropriate names were in place, there were just two steps left:

  1. Select all of the CheckBoxes and then add a call to a procedure I wrote called ShowHideColumns (see Figure 2).
  2. Add code behind the form to make sure that the proper CheckBoxes are checked when the form is loaded and that the form is updated whenever a CheckBox value is changed. The code to check the CheckBoxes is in the Form_Load event:

 
Figure 2

Private Sub Form_Load()

Dim ctl As Control

Dim stCtl As String



Set sfrm = Me.sfrmHideShowColumns

For Each ctl In Me.Controls

If TypeOf ctl Is Access.CheckBox Then

stCtl = "tb" & Mid$(ctl.Name, 3)

ctl.Value = Not sfrm.Form(stCtl).ColumnHidden

End If

Next ctl

End Sub

The code to show and hide the columns is in a routine called ShowHideColumn. You’ll notice that it looks a lot like the code in the Form_Load event:

Private Function ShowHideColumn()

Dim sfrm As SubForm

Dim ctl As Control

Dim stCtl As String



Set sfrm = Me.sfrmHideShowColumns

For Each ctl In Me.Controls

If TypeOf ctl Is Access.CheckBox Then

stCtl = "tb" & Mid$(ctl.Name, 3)

sfrm.Form(stCtl).ColumnHidden = _

Not ctl.Value

End If

Next ctl

End Function

Voilà! You can now show and hide columns on demand!

You can certainly expand this technique. Here are some suggested ways to enhance the functionality:

  • Instead of touching every column when one is changed, you could take a parameter in the ShowHideColumns function that would contain the control name.
  • You might want to keep some columns from being shown, ever.
  • Some columns might always need to stay visible.

Repositioning columns – not!

Believe it or not, once users start realizing that they can reconfigure their UI, they can’t get enough of it. Many users know that they can just stick the mouse at the header of the column, click to select, and drag the column around. Easy! But this can be very annoying if the result isn’t appropriate for the applications—especially if they save those changes!

The problem is that sometimes you want to make sure that your users can’t make dramatic changes to the layout of your datasheet. For instance, you might need to make sure that some data is always visible. Unfortunately, there’s no property to fix a column in position. However, you can use the events associated with controls to get the same effect.

To control repositioning, you need to use the MouseUp event of the controls in the datasheet. Set the properties for all of the columns to a single procedure in the same way I described in the previous section (and as shown in Figure 2), and then use the following procedure to keep the columns from wandering:

Private Function DontMuckWithSize()

Dim ctl As Control

Dim ictl As Integer



For ictl = 0 To Me.Controls.Count - 1

Set ctl = Me.Controls(ictl)

If TypeOf ctl Is Access.TextBox Then

ctl.ColumnOrder = ictl + 1

End If

Next ictl

End Function

This code takes advantage of the Controls collection of the datasheet. While the user may reposition the column on the screen, the column’s postion in the Controls collection doesn’t change from what it was when the form was originally displayed. The code just loops through the collection, setting each column’s ColumnOrder back to its position in the Controls collection. The 1 that gets added to the ictl variable is just there to handle the difference in the position in the Controls collection (which is 0-based) and the ColumnOrder number (which is 1-based). You can see this in action in the frmNoReposition form in the sample database.

You can obviously do a lot more with the ColumnOrder property. This example always reorders them back the original order, whereas you might actually want to move your columns to a new position. In fact, you can combine ColumnOrder and ColumnHidden to produce all kinds of complicated schemes that allow you to reuse the same subform across several forms. All you have to do is use these properties to change the emphasis to particular fields in the datasheet. My one piece of advice is to make sure your changes are intuitive. It can be very unsettling for users to have things move around on the fly. You should usually restrict these techniques to changing your layout on load of the form and then leaving the UI alone.

Now, the advanced developer who’s had to deal with this problem before knows that there’s one case that isn’t handled by this sort of code. If the user chooses “Freeze Column” from the Format menu, the selected column is moved all the way to the left-hand side of the screen and fixed there, no matter how you move around the datasheet (much like Excel’s column freezing capabilities). You can detect this situation by looking at the FrozenColumns property, which indicates how many columns are frozen (it will always start with 1, because Access counts the record selector as a column that’s always frozen). If you want to freeze or unfreeze columns from your code, you can use DoCmd.RunCommand, passing acCmdFreezeColumn and acCmdUnfreezeAllColumns as the parameters.

What’s missing from this functionality is an event that informs you that the user has frozen some columns, and also a property on each column that tells you which columns have been frozen. If you want to control which columns are frozen, you’ll have to check the FrozenColumns property to see whether the number is wrong, unfreeze all of the columns, and then freeze the ones that you want.

Resizing columns

The ability to resize columns programatically to display the data currently in the column is the original reason I wanted to write this article: It’s just an amazing feature. The first place I ever saw it was in Access’s Normalizer, better known as the Access Table Analyzer (the names of all of the wizard’s objects are still prefixed with “NORM_” because the marketing change to “Table Analyzer” didn’t force any code changes!). The Normalizer creates a query using DAO, and then it sets the columns of the resulting datasheet to be the appropriate width to display the data in the columns. And remember, tables and queries in Access are displayed using datasheets (at least, when you have them in browse view), which are forms.

The trick to resizing columns automatically to the data currently in them requires two steps:

  1. You must set the datasheet’s ColumnWidth properties to -2, a number that seems to mean “best fit” to Access, internally. Access will then change the ColumnWidth property value to an appropriate number (in twips).
  2. To make the change permanent, you have to add a property called ColumnWidth to DAO’s Property collection (which won’t contain the property by default for all queries) and set the property’s value to the same value as the control’s ColumnWidth property from Step 1.

To do this, you can use my procedures FixColumnWidthsOfQuery or FixColumnWidthsOfTable and their helpful subroutine, SetDAOFieldProperty:

Public Function FixColumnWidthsOfQuery _

(stName As String)

Dim db As Database

Dim qdf As QueryDef

Dim fld As DAO.Field

Dim frm As Form

Dim ictl As Integer

Dim ctl As Control



Set db = CurrentDb

Set qdf = db.QueryDefs(stName)

DoCmd.OpenQuery stName, acViewNormal

Set frm = Screen.ActiveDatasheet

For ictl = 0 To frm.Controls.Count - 1

Set ctl = frm.Controls(ictl)

ctl.ColumnWidth = -2

Call SetDAOFieldProperty(qdf.Fields(ictl), _

"ColumnWidth", ctl.ColumnWidth, dbInteger)

Next ictl

DoCmd.Save acQuery, stName

End Function



Public Function FixColumnWidthsOfTable _

(stName As String)

Dim db As Database

Dim tdf As TableDef

Dim fld As DAO.Field

Dim frm As Form

Dim ictl As Integer

Dim ctl As Control



Set db = CurrentDb

Set tdf = db.TableDefs(stName)

DoCmd.OpenTable stName, acViewNormal

Set frm = Screen.ActiveDatasheet

For ictl = 0 To frm.Controls.Count - 1

Set ctl = frm.Controls(ictl)

ctl.ColumnWidth = -2

Call SetDAOFieldProperty(tdf.Fields(ictl), _

"ColumnWidth", ctl.ColumnWidth, dbInteger)

Next ictl

DoCmd.Save acTable, stName

End Function



Private Sub SetDAOFieldProperty _

(fld As DAO.Field, _

stName As String, vValue As Variant, _

lType As Long)

Dim prp As DAO.Property



For Each prp In fld.Properties

If StrComp(prp.Name, stName, _

vbBinaryCompare) = 0 Then

prp.Value = vValue

Exit For

End If

Set prp = Nothing

Next prp



If prp Is Nothing Then

Set prp = fld.CreateProperty(stName, _

lType, vValue)

fld.Properties.Append prp

End If

End Sub

You can simply pass the name of any table or query to these routines and let them do the rest!

If you need to do the same task with a Form, it’s even easier: Just set all of the ColumnWidth properties to -2. No extra steps are needed.

Conclusion

Really, this article only touched the surface of datasheets. Looking toward Access 2000, there are new features that can be used for datasheets:

  • Conditional formatting allows you to change the way individual cells are formatted based on their values (a feature that’s been long requested). Given how many people are still using Access 97, I usually recommend that people use Stephen Leban’s solution for this problem, because his will work in Access 95, 97, and 2000.
  • Subdatasheets are a powerful capability, which can be used to show hierarchical data very effectively. Like other features I discussed, they depend on Jet properties that can only be created/modified via DAO. Unfortunately, you have to upgrade to Access 2000 if you need this functionality.
  • For Access 2000, a developer on the Access team noted how datasheets were “forms” in many senses (the primary reason that you view tables and queries as Forms by using the ActiveDatasheet property). Access 2000 takes this a step further to support using tables and queries directly in the Subform control as a SourceObject. You can take advantage of this change to cut down on the number of forms you need in a project by assigning tables and queries directly to Subforms (modifying properties such as ColumnWidth and ColumnHidden at runtime to change their look and feel).

It’s clear that datasheets have some real power behind them, in terms of how they can be used by your customers and how they can be manipulated by you. You might find one or more of these tips useful, and even be inspired to find other useful features from datasheets in Access!

 Your download file is called   Kaplan_Datasheet.accdb

 

Why not Try ?

 Zoom, Zoom, Zoom (your subforms)

And The Good News Is

SQL Without Joins

About Michael Kaplan

Michael Kaplan was the owner and lead developer of Trigeminal Software, Inc., a consulting firm that focuses on all types of unusual interconnectivity solutions in Microsoft Access, SQL Server, Visual Basic, and Office. He worked for Microsoft till 2015. Unfortunately he died in 2015 :(
This entry was posted in Forms. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.