Table sort on open (1 Viewer)

Is there a way to ensure that when a user opens the table, it will be sorted by the ID field, regardless of how the last user saved the table?

Nancy

No. And asking that question is a sign that your database isn't developed correctly for its use.

Normally tables are hidden from users. You build forms and reports for users to interact with data. Those forms and reports can have sorts and filters automatically (or manually by the user) applied to them. Now, there are exceptions for sophisticated users--sometimes access can be given to them; but your question leads me to believe that your users aren't that type.

Its best to only allow them to interact with tables in a safe way you control---make forms and reports for them.

Thanks for a quick response.
So, if a user (say a lab technician) is required to have access to a table to do various sorts or filters, would you recommend I create a split form from that table?

My recommendation is to not have users directly access tables. Instead build forms and reports for them to do what they need.

Nancy,

Find out from the lab tech EXACTLY what they are asking for and HOW they want it formatted. Often they will ask for something and you find out what they are doing with it after is something that you should have your app do for them.

The 'lab tech' may be asking for this to make labels for specimen collection. If you can print this out for them, you can also update when the labels are made without them having to go back in to update the system.

And as plog has posted, don't let them directly access the tables. Doing so allows them to bypass any ability you have at safeguarding data or data entry.

If they are more comfortable with "table" view, you can create forms in data sheet view. When I do this, I normally make them subforms so I can have an unbound main form to serve as a header because DS forms do not show theirs.

The point of using a form is that you can take advantage of form level events such as BeforeUpdate to perform validation on the changes that are being made to protect the validity of the data as best you can. You can't control anything if you allow users direct access to tables or queries.

Simple validation on all dates for example prevents a user from accidentally typing 2/14/208 when he meant 2/14/2018. Validation can of course be much more sophisticated depending on your needs.

since you are using form for input you can set the sort order on the form's property (design view).

nancy, the first (sad) thing that people learn about sorted tables is that they don't exist in Access. The trick is that you can create a query that contains an ORDER BY clause to implement a sort and can then open the QUERY. Just about anything you do with tables you can do with queries. (I DID say "just about"!)

When you create a form, you can make it use the table, or you can give it a query name for a query that contains everything the table contains but with an ORDER BY as part of the query. Your users will NEVER know the difference and the table contents will ALWAYS be shown in whatever order you designed into the query.

As a general rule, you NEVER show a user the navigation elements of the database because you can't trust them. (No, I'm serious.) Their agenda is not your agenda. You want to keep the database clean. They want to do what they need to do as quickly as possible. Those two goals are rarely well-aligned.

One solution is called a "switchboard" form. You can search this forum for many articles on that subject.

Thanks everyone for your input. I see the now that I should hide the tables and create forms instead.