Retaining Sort Order

[Chris]

Dear All,

I have a subform in datasheet view whose record source is a table (not
a query) A user can right click and customise sort order. Now when the
form is closed VBA code saves for subform with the intention that the
sort order is retained. This works fine in an mdb file but with an mde
the form sort order is not saved.

To work around this I thought about setting the Order by in the table
properties but that particular property doesn't appear to be accessable
through Code. Is this the case?

Can anyone suggest a workaround.

Thanks kindly

[Swans, What Swans?]

1) What code are you using to dynamically change the sort and save the
changes?

2) My rule of thumb is to NEVER have a form directly work from table but
always go through a query.
In article <11**********************@s34g2000cwa.googlegroups .com>,
ch********@silverbirches.com.hk says...

Dear All,

I have a subform in datasheet view whose record source is a table (not
a query) A user can right click and customise sort order. Now when the
form is closed VBA code saves for subform with the intention that the
sort order is retained. This works fine in an mdb file but with an mde
the form sort order is not saved.

To work around this I thought about setting the Order by in the table
properties but that particular property doesn't appear to be accessable
through Code. Is this the case?

Can anyone suggest a workaround.

Thanks kindly

[Rick Brandt]

"Chris" <ch********@silverbirches.com.hkwrote in message
news:11**********************@s34g2000cwa.googlegr oups.com...

Dear All,

I have a subform in datasheet view whose record source is a table (not
a query) A user can right click and customise sort order. Now when the
form is closed VBA code saves for subform with the intention that the
sort order is retained. This works fine in an mdb file but with an mde
the form sort order is not saved.

To work around this I thought about setting the Order by in the table
properties but that particular property doesn't appear to be accessable
through Code. Is this the case?

Can anyone suggest a workaround.

The sort order of a form in an MDE cannot be changed permanently. You need to
either store the desired sort in a table that can be looked up and applied upon
form opening or else use a query and dynamically change the ORDER BY clause of
the query. That CAN be changed permanently in an MDE and it need only be a
basic...

SELECT * FROM TableName ORDER BY FieldName
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com