I have used the code below to save the user settings for datasheet forms between front-end updates, and I think it could work for you.
Try to save the datasheet form that your users are manipulating as another form and use that as a subreport embedded in an empty report (with title,etc.).
In the Load event of the new form add this line:
Code:
Call LoadUserColumnSetup(Forms!frmYourOriginalDatasheetForm)
Put in new module:
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
On Error Resume Next
' Only apply to forms in datasheet view.
Const cDatasheetView As Long = 2
If frm.CurrentView <> cDatasheetView Then Exit Sub
strBlob = GetSetting("DB_Columns", "Settings", frm.Name, "")
If strBlob <> "" Then
Call GetOrderedColumns(strBlob, strColumns)
intColumns = UBound(strColumns) + 1
If intColumns <> 0 Then
For intColumn = 0 To intColumns - 1
If Trim(strColumns(intColumn)) <> "" Then
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
Next
End If
End If
End Sub
Private Sub GetOrderedColumns(ByVal strData As String, _
ByRef strColumns() As String)
Dim strTemp() As String
Dim intCols As Integer
Dim intCol As Integer
Dim intCurr As Integer
Dim strValues() As String
On Error Resume Next
strTemp = Split(strData, vbCrLf)
intCols = UBound(strTemp) - 1
ReDim strColumns(intCols)
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
Next
Next
End Sub
Public Sub SaveUserColumnSetup(ByRef frm As Form)
Dim Ctl As Control
Dim strBlob As String
Dim strCtl As String
On Error Resume Next
' Only apply to forms in datasheet view.
Const cDatasheetView As Long = 2
If frm.CurrentView <> cDatasheetView Then Exit Sub
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
Next
SaveSetting "DB_Columns", "Settings", frm.Name, strBlob
End Sub
Cheers,
Vlad