Customizing datasheets to users needs (Part I)

Applies to:  2007, 2010, 2013 (desktop) - untested in earlier versions

Datasheets are so easy to work with and provide so much flexibility that I sometimes wonder why anyone would want to use a listbox, but I keep going back to those listboxes anyway. 

In this article I'm going to provide you with an incredibly easy way to save your datasheet column order and column width properties, for all of your users, for all of your datasheets⁽¹⁾, and yet ensure that those same settings will be present when you field the next version of your application.

Most developers will embed a datasheet subform within a main form, and add a variety of controls and features to the main form, while displaying data in the subform.  One of the handy features of a datasheet is that users can move, resize, hide, and even freeze columns on the left side of the datasheet; they can configure the datasheet exactly the way they want and when they close the application, their settings are saved and restored the next time they open the application (at least sometimes they are). Unfortunately, the next time you distribute a new version of the application, their custom settings will be gone; they will have to go through the entire process of reconfiguring the datasheet the way they want it.

To avoid this headache,  I developed a couple of relatively simple procedures that store the datasheet column properties in a table each time the datasheet is closed, and then restore those settings each time the datasheet is opened.  In order to implement this solution you only need to add two lines of code to your datasheet forms code module.

Private Sub Form_Load()
                      DatasheetLoad Me
                      End Sub
                      Private Sub Form_Unload(Cancel As Integer)
                      DatasheetSave Me
                      End Sub

Select all Open in new window

Then add two tables (tbl_Datasheets_01_Names and tbl_Datasheets_02_Columns) and two modules (mod_Datasheet_Columns and API_OS_Username) from the attached database, and then modify the DatasheetLoad procedure by replacing this line:

TempVars!Userid = frm.Parent.cbo_UserName.Value

Select all Open in new window

which was used for the example database only, with this one:

TempVars!Userid = fOSUserName

Select all Open in new window

The attached database includes the two datasheet tables, a table of sample data, two forms (main and subform), and a couple of modules. When you open the application, the subform control is empty and the SourceObject is blank. As soon as you select a user from the combo box, the SourceObject gets assigned to the subform and the DatasheetLoad procedure is called in that subforms Load event. This functionality in the sample database allows you to simulate three different users column configurations, but in your application you would only be concerned with the current user.

The code to accomplish this is very simple, but could be tailored to your specific needs. As an example, you could add an option to restore the defaults, which would load the settings from the "Admin" user, or you might even want to allow your users to load settings already established by another user (similar to the way the combo box in the application works). Another option, which I have implemented in several of my applications is to track whether columns are moved or resized⁽²⁾, and rather than automatically saving the new settings providing the user with the option of saving or cancelling the changes.

When the DatasheetLoad procedure is called, it unfreezes any columns that were previously frozen in that datasheet. It then retrieves records from tbl_Datasheets_02_Columns table which correspond to the datasheet form that was passed to the procedure and the current user. It then loops through those controls and repositions them within the datasheet⁽³⁾. Finally, after all of the colums have been positioned and sized, it freezes the appropriate columns.

When the datasheet is unloaded, the code deletes all of the previous records in tbl_Datasheets_02_Columns for the current datasheet/user combination, and then loops through the controls to save the column order, width, and hidden properties before updating the records to indicate which columns (if any) are frozen.

With this technique, every user can customize their datasheets to meet their own needs and be assured that they will stay that way, even when you distribute a new version of the application. If you change the names of the controls in the subform then the code will raise an error when it attempts to move a control that no longer exists; the error handler ignores these errors. If you add controls to the subform, then the first time that subform is opened, those new controls will show up to the far right side of the datasheet.

DatasheetArticle.accdb

Notes:
1. This technique does not work with queries that are used as the SourceObject for a subform control.
2. This is done by using a combination of the MouseDown and MouseUp events in the datasheet, along with checking the columnOrder, ColumnWidth, and ColumnHidden properties of the datasheets ActiveControl in those events.
3. In order to get the columns to end up in the proper order, you must load them in reverse order and set each controls ColumnOrder property to zero (ensuring that it becomes the left most column).