ACCDE doesn't remember sorting

[BrianWen]

Hi everybody!

I don't know about accdb, but mdb remembered what sorting the user had selected in a certain view before closing Access. So that when opening the same form, the records (in continious form) would be sorted the way the user had last.
I'm talking about the two A-Z and Z-A buttons.

As I see it, that doesn't work in ACCDE format. Is there a way to make it work in ACCDE's too?

 

[lameid]

The sorting is part of the object definition... So users need to be able to save the object.

Instead of just closing the form, I expect if you save changes it will remember. Although that seems goofy to do in an ACDE to me.

 

[genomon]

As far as I know in mde's and accde's functionality to allow the user to make changes to database objects is disabled.

Beir bua agus beannacht!

 

[lameid]

You can't make changes to code, but everything else can be touched. I change the SQL property of querydef objects in ACCDE saved as ACCDR for runtime all the time. The order by and filter properties should save without any issue.

 

[BrianWen]

lameid: Sounds interesting, but I'm not sure how to save? - The save buttons are grayed out and CTRL + S doesn't do anything. Is there a trick?

 

[dhookom]

Since this is a "forms" forum, I assume Brian would like to save the Order By property of a form. Since forms can't be changed in an ACCDE file, I doubt the Order By property can be saved.

If there was no Order By property on the form, I think the order of the Record Source would be used. The design of the query should be updateable as suggested by lameid.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Put a button on the form that saves...

Something like the following

docmd.Close acForm, Me.Name, acSaveYes

 

[dhookom]

lameid,
Have you tried saving a form like this in an MDE or ACCDE? Or, are you suggesting the save for an MDB or ACCDB?

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Duane,

It had been a long time since I tried something like this in an MDE but at your pressing I tested by creating an ACCDE in Access 2010 and it works.

You can do anything BUT change VBA code programmatically in the compiled version (MDE/ACCDE) is my expectation.

Of course if you are not the one creating the MDE/ACCDE, this does not help much.

 

[dhookom]

Doesn't work for me. I created a continuous form with a command button using your code. I compiled my Access 2007 into an accde file. While the form was open, I could sort all I wanted and then click the close/save button. Every time I re-opened the form, it was in the order specified when I created the accde. Any sorting changes did not stick when I closed the form.

This is what I expected to happen. What did I do different than you did?

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

Only difference I see is you used Access 2007 and I used Access 2010.

Hmmm... Unless I applied a sort and therefore had a populated Order By property before saving to an ACCDE?

 

[lameid]

Taking a look back I can't replicate my success. It makes the most sense I re-opened the ACCDB accidentally for testing.


That being the case, you would have to save the order by properites to a table on close and assign them on open/load.

 

[BrianWen]

Seems like it's not possible, unless if I save it in a table. I'll consider that...

Thanks all!!

 

[dhookom]

I believe the sort order of the saved query will transfer to the form if you don't have an Order By property set on the form.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

What Duane is suggesting is that you can change the order by clause in the query behind the form so long as the form does not have an order by property.

Maintainability, reuse/portability and performance are the issues I see in picking a solution.

Duane's solution should be fastest at retrieving data, it does mean that you are likely storing the SQL in code or writing a complex procedure to change the SQL clause which brings up maintainability and portability concerns to me. You might have to go into the code for the form to change its recordsource SQL and if that is the case the code is not reusable on other forms. Although if you took the time to parse out all possible clauses of the SQL you could alleviate the issue (complex code).

The table solution is the quick and dirty solution. Just save the orderby property and form name and you can reuse the procedures on multiple forms.