Sort order in sub forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with a sub form. Each "page" of the main form represents a
document. Each "line" of the sub form represents a paragraph of the
document. The main form is based on a table of documents, where the document
number is used as key field. The sub form is based on a table of paragraphs,
where a combination of the document number and the paragraph number is used
as key field.

For each "page" of the main form (each document) the sub form is indicating
the paragraphs of that particular document. I want the paragraphs to be
ordered by paragraph number. As the paragraph table is using a combination
of document number and paragraph number as key field, I would regard this to
be the most simple and logical way to order the paragraphs. If I open the
paragraph form (normally used as sub form) as a separate form, the paragraphs
are ordered correctly. If I open the main form including the sub form, the
paragraphs seem to be ordered by the time, when I created the paragraphs,
i.e. old paragraphs in the top and new paragraphs in the bottom.

Of course I always have to add new paragraphs in the bottom, but I would
expect, that when I first leave that "page" of the main form (that document)
and then enter it again, the new paragraph would be located on the logical
place in the paragraph number order. This doesn't happen. The new paragraph
is still in the bottom. Why? What can I do about it?
 
Lars,
First, don't use a table as the source for your subform. Use a query (including all
your subform fields), and set the sort order to your paragraph position number.
I think your confusing "form" view of the subform with the "table" view of the subform.
Table view defaults to your key indexed field sorting, and form view will always use the
sorting order you request in the query behind it.

Your PositionNo should be a single numeric field that you can manually adjust to force
a paragraph to a chosen position.
ex.
1.0 Paragraph1
2.0 Paragraph2
3.0 Paragraph3

To place 3 between 1 and 2...
1.0 Paragraph1
1.5 Paragraph3
3.0 Paragraph2
 
You're right. Of course it is possible to make a query only including fields
of one single table and thereby defining a sort order. That would solve my
problem. But it still bothers me a little, that I have to make that sort of
things, which should be unnecessary. The sort order, that I want, is
identical to the sort order defined by the key field(s) of the table. If I
wanted a "cronological" sort order, I would add a autonumber field to the
table and use that field as key field. I am actually using a PositionNo key
field, which I fill in manually, as you describe. I don't understand, how MS
Access can show the sub form in "cronological" sort order again and again,
except by saving automatically an "invisible" autonumber field or something
similar. Anyway, it seems that I have to accept it and make that query.
 
Lars,
Sorry I thought you were confused between table and form view. Lots of newbies on the
newsgroups... you seem to be right on top of the problem, and know what you're talking
about
Hmmm... that's odd. I have a SnowPlow table with an autonumber field for each client,
and a PlowOrder field similar to the PositionNo. The user can change that number at any
time (sort of a dewey decimal system) to "rearrange" the client plow order.
The table (without any sorting/filtering applied) sorts by ClientID (auto).
The Plow form (continuous) sorts by the PlowOrder field as forced by the query behind
the form... so I can't duplicate your problem.

I tried a Google Groups search, but couldn't find a similar question. Have you tried
the same?

Let me throw out a couple of wild shots, and if you still have no luck, I think a NEW
post might be in order. Some folks may think this thread has been resolved.

1. You could try to set the OrderBy of the subform on the form Open event. Ex...
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[PositionNo]"
Me.OrderByOn = True
End Sub
2. Or if not No. 1... then make sure there is no OrderBy.
3. Try a "saved" query behind the subform, rather than an internal query.
4. Any chance your Parent/Child link might be interfering in some way?

Otherwise, try a new post (my table does this..vs. my subform does that...)
I'll be keeeping an eye out...I'd like to see what might be causing this.
Sorry I couldn't be more help,
 
When I use a saved query behind the sub form, I can define the sort order,
exactly as I want it. In that sense the problem is solved. I am just still
wondering
1. why it is necessary to do it this way,
2. how it is possible for MS Access to show the "chronological" sort order,
when there is no autonumber field (or similar) in the table.
I guess I just have to accept that this is one of the "funny" things about
MS Access! Thank you for the help.


Al Campagna said:
Lars,
Sorry I thought you were confused between table and form view. Lots of newbies on the
newsgroups... you seem to be right on top of the problem, and know what you're talking
about
Hmmm... that's odd. I have a SnowPlow table with an autonumber field for each client,
and a PlowOrder field similar to the PositionNo. The user can change that number at any
time (sort of a dewey decimal system) to "rearrange" the client plow order.
The table (without any sorting/filtering applied) sorts by ClientID (auto).
The Plow form (continuous) sorts by the PlowOrder field as forced by the query behind
the form... so I can't duplicate your problem.

I tried a Google Groups search, but couldn't find a similar question. Have you tried
the same?

Let me throw out a couple of wild shots, and if you still have no luck, I think a NEW
post might be in order. Some folks may think this thread has been resolved.

1. You could try to set the OrderBy of the subform on the form Open event. Ex...
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[PositionNo]"
Me.OrderByOn = True
End Sub
2. Or if not No. 1... then make sure there is no OrderBy.
3. Try a "saved" query behind the subform, rather than an internal query.
4. Any chance your Parent/Child link might be interfering in some way?

Otherwise, try a new post (my table does this..vs. my subform does that...)
I'll be keeeping an eye out...I'd like to see what might be causing this.
Sorry I couldn't be more help,
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

LarsM said:
You're right. Of course it is possible to make a query only including fields
of one single table and thereby defining a sort order. That would solve my
problem. But it still bothers me a little, that I have to make that sort of
things, which should be unnecessary. The sort order, that I want, is
identical to the sort order defined by the key field(s) of the table. If I
wanted a "cronological" sort order, I would add a autonumber field to the
table and use that field as key field. I am actually using a PositionNo key
field, which I fill in manually, as you describe. I don't understand, how MS
Access can show the sub form in "cronological" sort order again and again,
except by saving automatically an "invisible" autonumber field or something
similar. Anyway, it seems that I have to accept it and make that query.
Click to expand...
Click to expand...
 
Lars,
OK... no use "whizzing" into the wind. There's got to be something, because I've had
subforms ordered every which way, and never had a problem. (even with "internal'" query
sorting)
Did you mention your Access version?
But I'm pretty sure someone should have an idea why this happens... so a New post
wouldn't be a problem, and can't hurt.

If you ever discover the reason, or the cause, I'd appreciate a note via my web site
below... Very interesting.
Otherwise, I'll watch the NG if you do repost...
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions



LarsM said:
When I use a saved query behind the sub form, I can define the sort order,
exactly as I want it. In that sense the problem is solved. I am just still
wondering
1. why it is necessary to do it this way,
2. how it is possible for MS Access to show the "chronological" sort order,
when there is no autonumber field (or similar) in the table.
I guess I just have to accept that this is one of the "funny" things about
MS Access! Thank you for the help.


Al Campagna said:
Lars,
Sorry I thought you were confused between table and form view. Lots of newbies on
the
newsgroups... you seem to be right on top of the problem, and know what you're talking
about
Hmmm... that's odd. I have a SnowPlow table with an autonumber field for each
client,
and a PlowOrder field similar to the PositionNo. The user can change that number at
any
time (sort of a dewey decimal system) to "rearrange" the client plow order.
The table (without any sorting/filtering applied) sorts by ClientID (auto).
The Plow form (continuous) sorts by the PlowOrder field as forced by the query
behind
the form... so I can't duplicate your problem.

I tried a Google Groups search, but couldn't find a similar question. Have you
tried
the same?

Let me throw out a couple of wild shots, and if you still have no luck, I think a
NEW
post might be in order. Some folks may think this thread has been resolved.

1. You could try to set the OrderBy of the subform on the form Open event. Ex...
Private Sub Form_Open(Cancel As Integer)
Me.OrderBy = "[PositionNo]"
Me.OrderByOn = True
End Sub
2. Or if not No. 1... then make sure there is no OrderBy.
3. Try a "saved" query behind the subform, rather than an internal query.
4. Any chance your Parent/Child link might be interfering in some way?

Otherwise, try a new post (my table does this..vs. my subform does that...)
I'll be keeeping an eye out...I'd like to see what might be causing this.
Sorry I couldn't be more help,
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

LarsM said:
You're right. Of course it is possible to make a query only including fields
of one single table and thereby defining a sort order. That would solve my
problem. But it still bothers me a little, that I have to make that sort of
things, which should be unnecessary. The sort order, that I want, is
identical to the sort order defined by the key field(s) of the table. If I
wanted a "cronological" sort order, I would add a autonumber field to the
table and use that field as key field. I am actually using a PositionNo key
field, which I fill in manually, as you describe. I don't understand, how MS
Access can show the sub form in "cronological" sort order again and again,
except by saving automatically an "invisible" autonumber field or something
similar. Anyway, it seems that I have to accept it and make that query.


:

Lars,
First, don't use a table as the source for your subform. Use a query (including
all
your subform fields), and set the sort order to your paragraph position number.
I think your confusing "form" view of the subform with the "table" view of the
subform.
Table view defaults to your key indexed field sorting, and form view will always use
the
sorting order you request in the query behind it.

Your PositionNo should be a single numeric field that you can manually adjust to
force
a paragraph to a chosen position.
ex.
1.0 Paragraph1
2.0 Paragraph2
3.0 Paragraph3

To place 3 between 1 and 2...
1.0 Paragraph1
1.5 Paragraph3
3.0 Paragraph2
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


I have a form with a sub form. Each "page" of the main form represents a
document. Each "line" of the sub form represents a paragraph of the
document. The main form is based on a table of documents, where the document
number is used as key field. The sub form is based on a table of paragraphs,
where a combination of the document number and the paragraph number is used
as key field.

For each "page" of the main form (each document) the sub form is indicating
the paragraphs of that particular document. I want the paragraphs to be
ordered by paragraph number. As the paragraph table is using a combination
of document number and paragraph number as key field, I would regard this to
be the most simple and logical way to order the paragraphs. If I open the
paragraph form (normally used as sub form) as a separate form, the paragraphs
are ordered correctly. If I open the main form including the sub form, the
paragraphs seem to be ordered by the time, when I created the paragraphs,
i.e. old paragraphs in the top and new paragraphs in the bottom.

Of course I always have to add new paragraphs in the bottom, but I would
expect, that when I first leave that "page" of the main form (that document)
and then enter it again, the new paragraph would be located on the logical
place in the paragraph number order. This doesn't happen. The new paragraph
is still in the bottom. Why? What can I do about it?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top