You Can Do That with Datasheets?

<< Click to Display Table of Contents >>

Navigation:  Sub Forms >

You Can Do That with Datasheets?

200102_mk1 Michael Kaplan        

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.

ad1468x60

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.

200102_mk1 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:

200102_mk2  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 102KAPLAN.zip   in the file SA200102_down.zip

This can be purchased with all the other downloads on this page

 

Why not Try ?

 

Zoom, Zoom, Zoom (your subforms)

And The Good News Is

Visual Basic .NET Development

SQL Without Joins