Fix saving column order in datasheet view
I have noticed this issue whereby you move columns and when the user exits the form, the datasheet columns revert back to the original (pre-saved) order. The work around I found was to hide/show columns, toggle one field on then off, close dialogue and then it will save. Not very intuitive.
Thanks for fixing.
Note that typically the reason this happens is that there is code behind the datasheet form that modifies properties on the form or controls (e.g. Textbox.Locked). Access intends to save the datasheet column widths without saving the properties changed in code, and that process was not working as expected. We are planning to fix this, but in the meantime, you may be able to work around this if you can avoid changing form/control properties in code (e.g. by using Conditional Formatting instead)
Ben Sacheri commented
@John, You got my vote, and thanks for suggesting a work-around. I found this post after Googling this problem.
Was able to fix this in AC2016 by changing the tab order of the controls in the subform - much easier fix.
OMG..Thank you so much! I have been struggling with this issue for weeks
Thank you so much!!!
To set a column to a specific place, add code in the forms OnLoad, like this:
Me.Unit.ColumnOrder = 4
In this example, field 'Unit' is in the fourth column position.
This is also an issue with Split forms
To clarify, this happens when using datasheet subforms on a main form.
Users need to be able to control the datasheet columns as they usually unique to each person.
Yes this behavior seems to have changed circa A2010, or at best inconsistent. In the past, you could set the ColumnOrder in Datasheet view - then File>>Save ... and the next time you open the Datasheet - that would be retained. However, this rarely seems to work now.
So, my workaround is in VBA in the Form Load event to set the ColumnOrder property for a given text box, which always works, but a bit of a hassle.