Access2007 show hide columns in datasheet

  • Thread starter Thread starter Mark Andrews
  • Start date Start date
M

Mark Andrews

I'm looking to create some software in Access2007 with a good deal of split
forms with datasheets taking up most of the screen. I have a nice little
button to Show/Hide fields that works well. I do a DoCmd.save after the
call to show/hide fields so the new datasheet layout is saved. This is
similar to most of the Microsoft templates.

All works well.

Now I change the database to an ACCDE.

No more saving allowed.

Anyone have a good solution to replace this functionality? Something to
think about with newer versions of Access as well (can datasheets work and
be flexible with columns shown/not shown in an ACCDE). You can change the
layout just not leave the form and come back with it remembering the layout.
In my case it's not much good if it doesn't remember.

Would love to hear any ideas,
Mark
 
Mark Andrews said:
I'm looking to create some software in Access2007 with a good deal of
split forms with datasheets taking up most of the screen. I have a nice
little button to Show/Hide fields that works well. I do a DoCmd.save
after the call to show/hide fields so the new datasheet layout is saved.
This is similar to most of the Microsoft templates.

All works well.

Now I change the database to an ACCDE.

No more saving allowed.

Anyone have a good solution to replace this functionality? Something to
think about with newer versions of Access as well (can datasheets work and
be flexible with columns shown/not shown in an ACCDE). You can change the
layout just not leave the form and come back with it remembering the
layout. In my case it's not much good if it doesn't remember.

Would love to hear any ideas,
Click to expand...


Maybe save the form's last-used configuration in a table? Read the table
and configure the form in the form's Open event, update the table either as
fields are shown/hidden, or just in the form's Close event.
 
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...?
I liked my 2 lines of code solution, so it's tough to get excited about
writing something to replace it.

I have about a dozen forms this needs to work for,
Sounds like something others might need as well?
Mark
 
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...?
Click to expand...

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.
Click to expand...

Complex Solutions R Us.
I have about a dozen forms this needs to work for,
Sounds like something others might need as well?
Click to expand...

I got the hint.
 
Very Appreciated!!! I'll test it out right away.
You have my vote for MVP of the year.

Thanks,
Mark
 
Back
Top