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)
For accdb file types, this issue is fixed as it saves the column order. But for accde and accdr file types it is still not working.
John G. commented
I hope it's OK to piggyback on this subject. I have a database with many queries and macros. Our procedure to run this database is to copy data from an Excel spreadsheet and paste it into a table. Both the Excel template and the table in the database are in the same order regarding field names. We then run a macro to build a load file which is then exported for processing in other programs.
The problem we have, is, many times after we run the macro and try to use the database again, the order of the columns have changed and we can no longer copy/paste from the Excel template to the table.
Is there anyway to lock the table in the database to prevent the columns from rearranging on their own?
Thanks in advance.
Kevin Roy commented
After I've rearranged and re-sized columns in an Access Form data sheet view, I do the following to make the changes stick: right-click any column header; select "Hide column" from the context menu; right-click any other column header; click "Unhide Columns", then check the unchecked column that you had previously hidden. This apparently forces Access to remember the column order and column widths.
Amazing this is still an issue. I have always been able to just reposition the columns and save and it is fine. Now that ability has been changed and doesn't work. yep lots of code in my forms but nothing that messes with the form controls, only data. This has gone backwards and relates to the fundamental user interface design that access is and was so good at. None of the workarounds above have worked for me at all.
If it cannot be resolved after 5 years then the advantage that access provides is gone and going to a different interface is not an option for some applications.
I was trying this in a form and the suggested fix did not work. However, I adjusted the tab order and it did retain the order I wanted.
Matt Albrecht commented
Crazy this is still an issue. This was posted 5 years ago. The MS Access team must be 2 people and a service dog.
That worked! Thanks
How did you figure that out!!!
I add my thanks, John. This has been driving me nuts!
Thank you, thank you.
Wow. Thank you so much for posting this. I was at my wits end. MS definitely needs to work on this.
I've set the ColumnOrder at form load and am receiving the error:
Run-time error '7771':
You can't set the ColumnOrder property when you are in Form view or PrintPreview.
Joe's answer at the bottom is the best method, in my opinion; use VBA in the load event to set the subform's controls' columnorder & columnwidth.
Thanks a million!
Thank you very much!
Thanks for sharing work around!!
Denise Walters commented