Allow/Deny saving of datasheet column widths and positions.

[deekay]

I want to allow users to resize and reposition columns of a datasheet
but for a prompt to be brought up and only the layout only to be saved
if they select "save changes". This is the way it works at the moment
for queries but for datasheet forms I find that it automatically saves
column positions and widths without prompting to save changes.

I should mention i am using Access 2000

[deekay]

deekay wrote:

I want to allow users to resize and reposition columns of a datasheet
but for a prompt to be brought up and only the layout only to be saved
if they select "save changes". This is the way it works at the moment
for queries but for datasheet forms I find that it automatically saves
column positions and widths without prompting to save changes.

I should mention i am using Access 2000

I noticed one of way of doing this is when the prompt "do you want to
save changes to the design of form xxxxx" is brought up, but this is
only brought up when altering a form in design view and not in
datasheet view. Still looking for a way

[Allen Browne]

To achieve what you want, you would need to create a table to hold the
user's preferences for each field.

Presumably you will add a Save Layout button to the toolbar for the
datasheet form, so that clicking this button saves the Column Order and
ColumnWidth for each column, information about which columns are frozen,
etc.

Then in the Open event of the form, you read the values from the table, and
assign them to the various columns, which effectively restores the form to
the user's last *saved* settings, rather than Access's last saved settings
for the form.

Here's an example of how to set the properties of the columns in the Open
event of the form:

Private Sub Form_Open(Cancel As Integer)
Me.RowHeight = -1

With Me.ClientNum
.ColumnHidden = False
.ColumnOrder = 1
.ColumnWidth = 864
End With

With Me.Surname
.ColumnHidden = False
.ColumnOrder = 2
.ColumnWidth = 2880
End With

With Me.Address
.ColumnHidden = False
.ColumnOrder = 4
.ColumnWidth = -2 'Set for visible text.
End With

RunCommand acCmdUnfreezeAllColumns
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deekay" <de*****@gmail.comwrote in message
news:11********************@h54g2000cwb.googlegrou ps.com...

>
deekay wrote:

>I want to allow users to resize and reposition columns of a datasheet
but for a prompt to be brought up and only the layout only to be saved
if they select "save changes". This is the way it works at the moment
for queries but for datasheet forms I find that it automatically saves
column positions and widths without prompting to save changes.

I should mention i am using Access 2000

I noticed one of way of doing this is when the prompt "do you want to
save changes to the design of form xxxxx" is brought up, but this is
only brought up when altering a form in design view and not in
datasheet view. Still looking for a way

[deekay]

Thanks for the response I think this may the only way of doing it.
However what I would really like is something very simple like just
closing the form and not saving any changes rather than having to store
values, but I think I will have to do it your way.
Allen Browne wrote:

To achieve what you want, you would need to create a table to hold the
user's preferences for each field.

Presumably you will add a Save Layout button to the toolbar for the
datasheet form, so that clicking this button saves the Column Order and
ColumnWidth for each column, information about which columns are frozen,
etc.

Then in the Open event of the form, you read the values from the table, and
assign them to the various columns, which effectively restores the form to
the user's last *saved* settings, rather than Access's last saved settings
for the form.

Here's an example of how to set the properties of the columns in the Open
event of the form:

Private Sub Form_Open(Cancel As Integer)
Me.RowHeight = -1

With Me.ClientNum
.ColumnHidden = False
.ColumnOrder = 1
.ColumnWidth = 864
End With

With Me.Surname
.ColumnHidden = False
.ColumnOrder = 2
.ColumnWidth = 2880
End With

With Me.Address
.ColumnHidden = False
.ColumnOrder = 4
.ColumnWidth = -2 'Set for visible text.
End With

RunCommand acCmdUnfreezeAllColumns
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deekay" <de*****@gmail.comwrote in message
news:11********************@h54g2000cwb.googlegrou ps.com...

deekay wrote:

I want to allow users to resize and reposition columns of a datasheet
but for a prompt to be brought up and only the layout only to be saved
if they select "save changes". This is the way it works at the moment
for queries but for datasheet forms I find that it automatically saves
column positions and widths without prompting to save changes.

I should mention i am using Access 2000

I noticed one of way of doing this is when the prompt "do you want to
save changes to the design of form xxxxx" is brought up, but this is
only brought up when altering a form in design view and not in
datasheet view. Still looking for a way

[Tim Marshall]

deekay wrote:

Thanks for the response I think this may the only way of doing it.
However what I would really like is something very simple like just
closing the form and not saving any changes rather than having to store
values, but I think I will have to do it your way.

My own experience in A97 and A2003 is that a datasheet, when a subform
of a main form, always retains its user settings. I always include a
pop up menu item to allow a user to have default widths, heights, plus
any frozen columns, restored.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[tina]

it's just a matter of how the main form is closed. i only allow my users to
close a form from a command button, which always includes the code

DoCmd.Close , , acSaveNo

subform column widths are not saved in this instance. changing the code to

DoCmd.Close

allows the default acSaveYes to apply, and subform column widths *are* saved
in this instance.

deekay, you can add a command button to your main form, and change the
form's ControlBox and CloseButton properties to No, so the user is forced to
close the form using the command button. then add an unbound checkbox to the
form so the user can checkmark the box when s/he wants to save his/her
current changes to the subform datasheet. add the following code to the
command button's Click event procedure, as

If Me!CheckboxName = True Then
DoCmd.Close
Else
DoCmd.Close , , acSaveNo
End If

replace CheckboxName with the correct name of the checkbox, of course.
recommend you precede the above code with an explicit save of the current
record, to make sure you don't lose any changes on Close, as

If Me.Dirty Then Me.Dirty = False

also, suggest you consider Tim's suggestion of a pop-up menu to restore
default settings; that's an excellent option that your users will be
grateful to have available to them.

hth
"Tim Marshall" <TI****@PurplePandaChasers.Moertheriumwrote in message
news:ei**********@coranto.ucs.mun.ca...

deekay wrote:

Thanks for the response I think this may the only way of doing it.
However what I would really like is something very simple like just
closing the form and not saving any changes rather than having to store
values, but I think I will have to do it your way.

My own experience in A97 and A2003 is that a datasheet, when a subform
of a main form, always retains its user settings. I always include a
pop up menu item to allow a user to have default widths, heights, plus
any frozen columns, restored.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me