Save and Retrieve Datasheet Layout with the Windows Registry

Friday Aug 18th 2006 by Danny Lesandrini

Danny Lesandrini shares a block of code that will allow users to modify (and save) datasheet layout when they are using a compiled MDE version of your Access applications.

The Problem

It was back in December of 2003 when I published my code for   deploying a new client  file automatically. Since then, I have used that code everywhere and I recently ran into a problem.

Some users like to rearrange the columns of datasheets to meet their personal needs, and then when the new file is pushed down, their changes are lost. Needless to say, they didn't like that very much and as the developer, I started to hear about it. This article is the fruits of my solution to this problem.

Code Download For This Article

In addition to the scenario I described above, this process must be followed if you want to allow users to modify datasheet layout when they are using a compiled MDE version of your Access applications. Since the design view is locked in an MDE, users cannot save "changes" to the form. They can move around columns and resize them, but once the form is closed, the changes are lost forever. The only way around it is to implement the code I show below.

The Demo

Below is a screen shot of the demo code, but you probably can't readily see what it does by simply looking at it. The demo form contains two subforms. Each subform has code on it's Load event which retrieves the datasheet column info and code on the Unload event which saves the current datasheet layout settings.

Every datasheet you wish to manage must have this code:

Option Compare Database
  Option Explicit
  Private Sub Form_Load()
      Call LoadUserColumnSetup(Me)
  End Sub
  Private Sub Form_Unload(Cancel As Integer)
      Call SaveUserColumnSetup(Me)
  End Sub

The data for each datasheet's settings are stored in the Windows Registry under a key with the name of the datasheet. The LoadUserColumnSetup pulls that data and applies it, while the SaveUserColumnSetup writes the latest data back to the Registry. Each of those functions take an Access.Form variable, which can be identified by the Me object to simplify the application of the code. Below is a listing of all the code involved. Comments interspersed will explain the process.

The Code

 Option Compare Database
  Option Explicit
  Public Sub LoadUserColumnSetup(ByRef frm As Form)
      Dim ctl As Control
      Dim strBlob As String
      Dim strColumns() As String
      Dim intColumns As Integer
      Dim intColumn As Integer
      Dim strValues() As String
      Const cDatasheetView As Long = 2
      On Error Resume Next
      ' Only apply to forms in datasheet view.
      ' Otherwise, exit the sub to cease processing
      If frm.CurrentView <> cDatasheetView Then Exit Sub
      ' Grab previous settings from Registry as a blob of data
      strBlob = GetSetting("Demo", "Settings", frm.Name, "")
      ' Blob data looks like this:
      '    name : postion : hidden : size
      '    ------------------------------
      '    fname:1:False:855
      '    lname:3:False:870
      '    hire_date:4:False:3090
      '    minit:2:False:840
      ' Only continue if blob contains data.
      If strBlob <> "" Then
        ' This is the clever bit.  For the code to work right,
        ' the columns must be assigned in correct order.  This 
        ' method reorders the blob entries.
        Call GetOrderedColumns(strBlob, strColumns)
        ' Loop through the columns (if any exist) and set the
        ' properties of the corresponding control
        intColumns = UBound(strColumns) + 1
        If intColumns <> 0 Then
          For intColumn = 0 To intColumns - 1
            If Trim(strColumns(intColumn)) <> "" Then
               ' Split the line into values and assign properties
               strValues = Split(strColumns(intColumn), ":")
               Set ctl = frm.Controls(strValues(0))
               ctl.ColumnOrder = CInt(strValues(1))
               ctl.ColumnHidden = CBool(strValues(2))
               ctl.ColumnWidth = CLng(strValues(3))
            End If
        End If
      End If
  End Sub
  Private Sub GetOrderedColumns(ByVal strData As String, _
                                ByRef strColumns() As String)
      ' The data is passed, along with the empty array.
      ' I tried returning an array, but couldn't get it
      ' to work, so fell back to passing the array ByRef.
      Dim strTemp() As String
      Dim intCols As Integer
      Dim intCol As Integer
      Dim intCurr As Integer
      Dim strValues() As String
      On Error Resume Next
      ' Each datasheet control's info is on its own line,
      ' so split the blob by Line Feed/Carriage Returns
      strTemp = Split(strData, vbCrLf)
      intCols = UBound(strTemp) - 1
      ReDim strColumns(intCols)
      ' Loop through the unordered array and convert it into a
      ' sorted list: Col 1 at the top and Col n at the bottom.
      For intCol = 0 To intCols
          For intCurr = 0 To intCols
              strValues = Split(strTemp(intCurr), ":")
              If CInt(strValues(1)) = intCol + 1 Then
                  strColumns(intCol) = strTemp(intCurr)
                  Exit For
              End If
  End Sub
  Public Sub SaveUserColumnSetup(ByRef frm As Form)
      Dim ctl As Control
      Dim strBlob As String
      Dim strCtl As String
      Const cDatasheetView As Long = 2
      On Error Resume Next
      ' Only apply to forms in datasheet view.
      If frm.CurrentView <> cDatasheetView Then Exit Sub
      ' Loop through the controls, processing only those that matter.
      For Each ctl In frm.Controls
        Select Case ctl.ControlType
          Case acLabel, acLine, acSubform, acCommandButton
              ' do nothing for these controls.
          Case Else
            strCtl = ctl.Name & ":" & _
                     ctl.ColumnOrder & ":" & _
                     ctl.ColumnHidden & ":" & _
                     ctl.ColumnWidth & vbCrLf
            strBlob = strBlob & strCtl
        End Select
      SaveSetting "Demo", "Settings", frm.Name, strBlob
  End Sub


This code took a little tinkering to get it to work, but once it was working, it was a simple matter to apply it and my users are happy again. True, it is an obscure little problem, but if it happens to be a problem you're currently facing then this little block of code will be a welcome addition to your toolbox.

» See All Articles by Columnist Danny J. Lesandrini

Mobile Site | Full Site