Display Form based on Query - Sorting Issue

[dhstein]

I have a form which has a record source as a query. The query specifies two
columns for sorting. If I run the query the data is sorted exactly right.
But the form has the data in a different (unsorted) order. I would display
the query, except I don't want the user to change the data. So I use the
form and set the properties to no edit - no update - etc. Is there a way to
display the records sorted as I specify in the query and prevent the user
from changing the data? Thanks for any help on this.

 

[Tom van Stiphout]

On Mon, 16 Mar 2009 18:54:02 -0700, dhstein

The whole story is longer than I write up right now, but the short
answer is that you can set the OrderBy and OrderByOn properties of
that form.

-Tom.
Microsoft Access MVP

 

[dhstein]

Thanks Tom. That worked for a few of the forms. But I have one form where
the query is sorted on fields that are not displayed. I tried to refer to
those fields in the form Order By property but that isn't working. I gave
them a label in the query (SortA: [tblKit].[KitID] as an example) and
used SortA in the order by for the form, but that doesn't work. Any
suggestions on this? Thanks.

 

[Clif McIrvin]

By "not displayed" I presume you mean you unchecked the "Show" property
for those columns in the query design grid...

If that is the case, the form cannot see those columns. Show the
columns, and put them on your form in hidden (visible=no) controls.

HTH
--Clif

Thanks Tom. That worked for a few of the forms. But I have one form
where
the query is sorted on fields that are not displayed. I tried to
refer to
those fields in the form Order By property but that isn't working. I
gave
them a label in the query (SortA: [tblKit].[KitID] as an example)
and
used SortA in the order by for the form, but that doesn't work. Any
suggestions on this? Thanks.

On Mon, 16 Mar 2009 18:54:02 -0700, dhstein

The whole story is longer than I write up right now, but the short
answer is that you can set the OrderBy and OrderByOn properties of
that form.

-Tom.
Microsoft Access MVP

 

[dhstein]

Tom, Clif,

Thanks for your replies. I got it working (sort of). The problem is that
the form can be sorted by the user which ruins the output in the sense that
only the sorted version (the way the original query sorts it) displays the
information needed. Also, it seems that once it has been sorted by the user,
it always comes up in that "sorted" and therefore worthless format. Is this
something that needs to be done in a report or am I missing something? The
bottom line is I want to display this information the way the query produces
it and not allow the user to change it or modify it in any way. Thanks.

 

[Clif McIrvin]

dhstein,

You're asking a question that is beyond the scope of my experience with
Access.

However, I believe Ken Sheridan's reply to your previous thread "Prevent
sorting of data in a form" at


is relevant to your question, specifically this quote:

<q> ...it would be better to deny users access to this
facility by using a custom menu and toolbar for the form, excluding
the relevant menu items and buttons.

Ken Sheridan
Stafford, England
</q>

 

[Clif McIrvin]

Let me try this again ... I got the wrong link in my first attempt and
pointed to the wrong thread.

(I'd seen others post links to prior threads ... and wasn't quite sure
how to do it.)