Column Order for each user

[ECEK]

I have a split form that I distribute and create as a runtime.

I would like the user to be able to sort the columns in their chosen order and for this to be retained as opposed to the default pre-designed order every time it is opened.

Is there a field within the properties of the form or will I have to use VBA (if that is indeed possible?)

Thanks for your time and advice.

 

[isladogs]

The problem with split forms is that it is very difficult to modify the supplied layout in any significant way. For example you can't save a change to the data sheet column order as you've discovered.

To do that, you either need to create a standard form/subform using the same record source for each....but it can get tricky to keep both synchronised
OR you can try the emulated split from which was a project designed to use the best features of the split form whilst overcoming its limitations.

Have a look and see what you think https://www.access-programmers.co.uk/forums/showthread.php?t=294421

NOTE
The current version uses a continuous form rather than a datasheet as testers generally preferred that.
However you could change it back if you wish

FYI there is another update about to be posted with a further enhancement

 

[Ranman256]

youd need a query to hold the sort for every user

qsSortBob
qsSortMary

a form could collect the userID when it opens and put it in a non-editable/hidden text box, and set the query based on User too:

sub form_load()
txtUser = Environ("Username")
txtQry = "qsQry" & txtUser
end sub

the user can open or edit their query with buttons:

  'user runs their query
sub btnRunQ_Click()
docmd.openquery me.txtQry
end sub

  'user can edit their sort
sub btnEditQ_Click()
docmd.openquery me.txtQry,acViewDesign 
end sub

 

[ECEK]

Thanks guys.
Plenty of homework there, however I feel that this "simple".... "ask" by a user is ultimately unachievable. The editable query etc is too complex for the user.

 

[MajP]

Are you talking order of records or order of the columns? If order of the columns, this is easy to do in a datasheet. So you just need to simply roll your own split form. You unfortunately will not get a window slider. In a datasheet you can order and show hide columns by using the columnOrder property of a control. This property is hidden and can only be set in vba.

 

[Gasman]

If you were to use the emulated split form with a datasheet, the user can move the columns as they see fit, and it stays that way when you close and reopen Access.?

In fact I have just created a split form and moved the columns in the same way and that also remains in the same order even after closing and reopening access and the form.?

 

[isladogs]

In fact I have just created a split form and moved the columns in the same way and that also remains in the same order even after closing and reopening access and the form.?

I thought it did as well but when I tried on a built in split form, it reverted back to the original layout when I reopened the form.
Did you go to design view and save?

 

[Gasman]

Hi Colin,
Yes, I created the from from a table, Just Create Split Form, and saved it.
Then just moved the columns, and exited the form, reopened, still the same, exited Access and reopened Access and the form and it remains the same. This is on an accdb though.

Edit: Does not work on an accde though.

 

[isladogs]

Just tried it again. No idea what I did wrong before as it works now!

No need for the OP to use the emulated split form or create your own!

BTW I've just posted v11 of the ESF which I know you use a lot.
See https://www.access-programmers.co.uk/forums/showthread.php?t=294421
See what you think.

 

[Gasman]

I've already noticed that and said Thank You.

I wouldn't say I use it a lot, perhaps more than some, as it appears most others do not like them. Then again I only use it for selection of a record and edit in the main form. Simple usage at the moment.

However the accde option prevents this ability to move columns and leave them as is.?

 

[isladogs]

You can't save the objects in an ACCDE so the columns will revert back to the previous order

 

[CJ_London]

what you can do, before the form is closed and providing you know who the user is, is store the column order in a table against the user name. And when the form is opened, interrogate the table for the required column order.

I use something similar for certain user preferences - and of course it is the same principle as user rights.

store the data vertically in a table structured something like this

PK
Userfk
FormName
ColumnName
ColumnOrder