Make datasheet column layout persistent

[Fjordur]

Hi,
I want the user to be able to rearrange, (un)hide or enlarge columns of a
datasheet view. I want the layout to be persistent that is, the user should
find his new layout next time he uses the application (Access 2000 MDE). The
ability to rearrange the columns is a powerful UI feature but useless if the
layout can't be saved.
This seems to be recurring problem but I could not find a clear solution on
Google.
Can anyone help?

 

[Ron2006]

We don't use MDEs here but here is what I have noticed and what I did
to get around it. Here, every time the user runs the FE he/she gets a
new version. (The same would be true if you issued a new version - just

less frequently.)

1) If there is only one subform on the form, then when the user closes
the window, when they come back the arrangement is the same, IF they do

not exit the program. If they exit then they are back to what I as
developer showed the last time I was in the screen.


2) If there are more than 2 subforms on the form, then the above can be

done but only one subform at a time.


The problem with both of these is that they disappear the next time a
new version is run.


Here is how I got around it.
1) Each of our users has to logon with a unique ID.
2) I created a table with
logonid, formname, fieldname, column#, columnwidth, sequence
3) I added a button that said save format and it would (delete prior
records for id, formname) and then append new entries for each of
those. It would also grab the orderby and it saved the orderby in the
"Sequence" with 0 for width and 0 for column# and OrderBy for
fieldname.
4) I added a button that said restore format and it would bounce
through that table and update the column # and width and the orderby


This process does not cover color but it shore saved them a lot of
re-work each time,


Some users had a new sequence that was so far different from the prior
that the insertion of the new field sequence would not quite work
completely the first time. Typically if they did the restore twice then

all would work out. Although I believe I also changed the sequence of
the query so that column # was part of the order for restoring the
format which probably helped the most.


With the number of people we have working the app and the number of
columns that some of the forms had this literally saved hours a day. It

did take some time on my part to get it all down right but the payback
was probably in the first week we used it. And they loved it.


Ron

 

[jahoobob via AccessMonster.com]

I guess you are talking about a form in datasheet view. Changing the column
width by dragging the right column edge is pretty straightforward and should
remain unchanged after closing and opening. If the users have use of the
Access menu they can use Format, Hide Columns or Unhide Columns. If they
don't have use of the Access menu, you can create a custom menu and place
these two features on it.

Oops. I just saw that it is an MDE. Forms and reports can't be changed in
an MDE. It is not a problem but a feature. Give them an MDB if you want tem
to be able to change things in forms.

 

[Fjordur]

I guess you are talking about a form in datasheet view. Changing the column
width by dragging the right column edge is pretty straightforward and should
remain unchanged after closing and opening. If the users have use of the
Access menu they can use Format, Hide Columns or Unhide Columns. If they
don't have use of the Access menu, you can create a custom menu and place
these two features on it.

Oops. I just saw that it is an MDE. Forms and reports can't be changed in
an MDE. It is not a problem but a feature. Give them an MDB if you want tem
to be able to change things in forms.

??? I just saved as MDE and I still can play around with the columns... But
the layout is lost when I close the form.

 

[Fjordur]

Here is how I got around it.
1) Each of our users has to logon with a unique ID.
2) I created a table with
logonid, formname, fieldname, column#, columnwidth, sequence
3) I added a button that said save format and it would (delete prior
records for id, formname) and then append new entries for each of
those. It would also grab the orderby and it saved the orderby in the
"Sequence" with 0 for width and 0 for column# and OrderBy for
fieldname.
4) I added a button that said restore format and it would bounce
through that table and update the column # and width and the orderby

Got the idea, thanks. Only I don't understand the second sentence of
paragraph 3 above. What's the 'sequence' field for?
I suppose this strategy would work with an MDE, wouldn't it?

Some users had a new sequence that was so far different from the prior
that the insertion of the new field sequence would not quite work
completely the first time. Typically if they did the restore twice then
all would work out. Although I believe I also changed the sequence of
the query so that column # was part of the order for restoring the
format which probably helped the most.

Uh? not sure I understand, can you elaborate a bit?

 

[Ron2006]

1) The sequence number was used to force the records coming back in to
be in a specific order. If you change a field to be column 5, but then
turn around and change a field that used to be column 15 to be column 4
and you do it in that order then the field that you made column 5 now
becomes column 6.
Possibly if I had sorted ascending by the to be assigned column number
I would accomplish the same affect. This other aproach seemed to work,
so I did not get involved in testing various combinations to test the
theory.

2) Yes it should work for an mde. Obviously, a limited test on just one
or two columns might be in order, just to make sure. The other
respondent did not indicate that it didn't work. If you try it and IT
DOES NOT WORK PLEASE let me know.

3) One of the items I grabbed was the Orderby property. Rather than
create another field in the table just for that, I re-used one of the
other fields. Since column# and column width were numeric, I saved the
text value of the orderby in the sequence field which was defined as
text to allow for this. (perhaps a better name is "extra field")

Ron

 

[jahoobob via AccessMonster.com]

I believe that is because you can't change the form. You really aren't
changing the form when you adjust the column widths in the instance you view
it. What you want is not possible with an MDE.

I guess you are talking about a form in datasheet view. Changing the column
width by dragging the right column edge is pretty straightforward and should

[quoted text clipped - 6 lines]

an MDE. It is not a problem but a feature. Give them an MDB if you want tem
to be able to change things in forms.

??? I just saved as MDE and I still can play around with the columns... But
the layout is lost when I close the form.

 

[Fjordur]

3) I added a button that said save format and it would (delete prior
records for id, formname) and then append new entries for each of
those. It would also grab the orderby and it saved the orderby in the
"Sequence" with 0 for width and 0 for column# and OrderBy for
fieldname.

Ron,
Not being very expert (yet) with VB, I'm exploring your ideas above, and
trying to get the width (and order) properties. I wrote something like
For Each aControl In Me.Controls
Debug.Print "aControl.Name " & aControl.Name
Debug.Print "aControl.ColumnWidth " & aControl.ColumnWidth
and get a runtime error 438, "Object doesn't support this property or
method" on the line that has the ColumnWidth property. Why?

 

[Douglas J Steele]

Not all controls have a column width property. The ones that do are Bound
Object Frame, Chart, Check Box, Combo Box, List Box, Option Button, Option
Group, Text Box and Toggle Button.

Try:

For Each aControl In Me.Controls
Debug.Print "aControl.Name " & aControl.Name
Select Case aControl.ControlType
Case acBoundObjectFrame, acObjectFrame, acCheckBox _
acComboBox, acListBox, acOptionButton, acOptionGroup _
acTextBox, acToggleButton
Debug.Print "aControl.ColumnWidth " & aControl.ColumnWidth
End Select

 

[Ron2006]

Doug cover it.

For when I was doing it, The button was on the form and the columns,
etc were part of a subform on the same form. So all of the referenceing
was expressed through the Forms![Subform Name} format.

===========================================

Forms![HiddenKey]![LogonID] = Forms![Initials]![Text1]
Forms![HiddenKey]![FormID] = "Billing Validation Form"
Dim stDocName As String
DoCmd.SetWarnings False
stDocName = "Formating - Empty for Form"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Set Db = CurrentDb()
Set DbFormat = Db.OpenRecordset("Formating Table")


With DbFormat

.AddNew
![LogonID] = Forms![HiddenKey]![LogonID]
![FormName] = Forms![HiddenKey]![FormID]
![FieldName] = "BVPrimaryKey"
![ColumnWidth] = Forms![Billing Validation Tab form]![Billing
Validation Form]![BVPrimaryKey].ColumnWidth
![ColumnOrder] = Forms![Billing Validation Tab form]![Billing
Validation Form]![BVPrimaryKey].ColumnOrder
.Update
.AddNew
![LogonID] = Forms![HiddenKey]![LogonID]
![FormName] = Forms![HiddenKey]![FormID]
![FieldName] = "Project Num"
![ColumnWidth] = Forms![Billing Validation Tab form]![Billing
Validation Form]![Project Num].ColumnWidth
![ColumnOrder] = Forms![Billing Validation Tab form]![Billing
Validation Form]![Project Num].ColumnOrder
.Update
etc...


=====================================================

Ron