[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Customizing datasheets to users needs (Part I)

Published on
13,925 Points
2 Endorsements
Last Modified:
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(1), 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.  Embedded-Datasheet.jpgOne 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

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

Open in new window

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

TempVars!Userid = fOSUserName

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(2), 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(3). 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.


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). 
Author:Dale Fye
  • 2
LVL 52

Author Comment

by:Dale Fye

I've tested it in 2007, 2010, and 2013, but no longer have a machine configured for 2003.
LVL 52

Author Comment

by:Dale Fye

This is interesting, because when I went in to add the line about which versions it applies to, the code blocks were missing the code again.  There has to be some sort of bug in the editor, possibly when you add text above the code window.

Administrative Comment

by:Eric AKA Netminder

Congratulations; your article has been published, and has been awarded EE-Approved status.

Page Editor

Featured Post

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month