Mark Andrews said:
Dirk,
Sounds like a possible solution. Do you think it's possible to save:
- which columns are visible
- order of columns
- widths of columns
a generic routine which works with a form passed in to save info and to
retrieve info etc....
Anyone feel like trying to write that code? Do you MVPs get together and
write things people could use etc...?
Well, now that you mention it ... here's something I just threw together.
'------ start of code ------
Function SaveDatasheetColumnInfo(frm As Access.Form)
' Save the widths, orders, and visibility of all
' the columns of the datasheet form passed to
' this function.
On Error GoTo Err_Handler
Dim ctl As Access.Control
Dim db As DAO.Database
Dim lngErrNo As Long
Dim lngColumnOrder As Long
Dim lngColumnWidth As Long
Dim blnColumnHidden As Boolean
' The form must be in datasheet view.
If frm.CurrentView <> 2 Then Exit Function
Set db = CurrentDb
For Each ctl In frm.Controls
' Only process controls that have a ColumnOrder property.
On Error Resume Next
lngColumnOrder = ctl.ColumnOrder
lngErrNo = Err.Number
On Error GoTo Err_Handler
If lngErrNo = 0 Then
' This control has a ColumnOrder property, so save its
configuration.
lngColumnWidth = ctl.ColumnWidth
blnColumnHidden = ctl.ColumnHidden
' If there's an existing record for this form & column,
' update it; if not, add one.
With db
.Execute _
"UPDATE ColumnInfo SET " & _
"ColumnOrder = " & lngColumnOrder & _
", ColumnWidth = " & lngColumnWidth & _
", ColumnHidden = " & blnColumnHidden & _
" WHERE FormName = " & _
Chr(34) & frm.Name & Chr(34) & _
" AND ColumnName = " & _
Chr(34) & ctl.Name & Chr(34), _
dbFailOnError
If .RecordsAffected = 0 Then
.Execute _
"INSERT INTO ColumnInfo " & _
"(FormName, ColumnName, ColumnOrder,
ColumnWidth, ColumnHidden) " & _
"VALUES (" & _
Chr(34) & frm.Name & Chr(34) & _
", " & Chr(34) & ctl.Name & Chr(34) & _
", " & lngColumnOrder & _
", " & lngColumnWidth & _
", " & blnColumnHidden & ")", _
dbFailOnError
End If
End With
End If
Next ctl
Exit_Point:
Set db = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Function
Function RestoreDatasheetColumnInfo(frm As Access.Form)
' Restore the widths, orders, and visibility of all
' the columns of the datasheet form passed to this function,
' from the table ColumnInfo.
On Error GoTo Err_Handler
Dim ctl As Access.Control
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngErrNo As Long
Dim lngColumnOrder As Long
Dim lngColumnWidth As Long
Dim blnColumnHidden As Boolean
' The form must be in datasheet view.
If frm.CurrentView <> 2 Then Exit Function
Set db = CurrentDb
Set rs = db.OpenRecordset( _
"SELECT ColumnName, ColumnOrder, ColumnWidth, ColumnHidden FROM
ColumnInfo" & _
" WHERE FormName = " & Chr(34) & frm.Name & Chr(34) & _
" ORDER BY ColumnOrder", _
dbOpenSnapshot)
With rs
Do Until .EOF
Set ctl = frm.Controls(rs!columnName)
ctl.ColumnOrder = !ColumnOrder
ctl.ColumnWidth = !ColumnWidth
ctl.ColumnHidden = !ColumnHidden
Set ctl = Nothing
.MoveNext
Loop
End With
Exit_Point:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End Function
'------ end of code ------
Beware of lines broken by the newsreader's line wrap. You'll probably have
to fix some.
The above functions use a table named ColumnInfo to save and restore the
column information, defined as follows:
ColumnInfo
---------------------------------------------------------
FormName (Text, compound PK)
ColumnName (Text, compound PK)
ColumnOrder (Number/Long Integer)
ColumnWidth (Number/Long Integer)
ColumnHidden (Yes/No)
These functions haven't been thoroughly tested, so it's entirely possible
that some quirks or bugs may be discovered. If you find and fix such bugs,
please let me know.
I liked my 2 lines of code solution, so it's tough to get excited about
writing something to replace it.
Complex Solutions R Us.
I have about a dozen forms this needs to work for,
Sounds like something others might need as well?
I got the hint.
