Form doesn't sort records per the underlying query

[Dirk Goldgar]

I'm almost certain it's the strFinalWHERE that's doing it (and not
anything sitting in the OpenArgs argument) of the DoCmd.OpenForm statement
from the calling form.

DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE
gives me unsorted records (though filtered).

DoCmd.OpenForm "frmAdmissionEntryForm"
gives me SORTED records (though unfiltered).

Somewhere the WHERE argument is sabotaging my underlying query's ORDER BY
clause.

That is certainly suggestive, but I can't reproduce it, and I still want to
see the code in your form's Open and/or Load event. What version of Access
are you using?

 

[Dirk Goldgar]

Dear Dirk,

I'm almost certain it's the strFinalWHERE that's doing it (and not
anything
sitting in the OpenArgs argument) of the DoCmd.OpenForm statement from the
calling form.

DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE
gives me unsorted records (though filtered).

DoCmd.OpenForm "frmAdmissionEntryForm"
gives me SORTED records (though unfiltered).

Somewhere the WHERE argument is sabotaging my underlying query's ORDER BY
clause. In both of these statements, the OpenArgs argument is left out.

As I said in my other message, I can't reproduce this. Please post the code
from your form's Open and/or Load event, and tell me what version of Access
you're using.

 

[John S. Ford, MD]

Dear Dirk,

I'm using Access 2000 and here's the code for the form's OnOpen event:

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
Cancel = True
MsgBox "No admissions meet these criteria.", vbExclamation
Else
strSearchCriteria = Me.OpenArgs
txtOpenArgs = strSearchCriteria
txtDCStatusCriteria = DLookup("DischargeStatusType",
"qryLIST_DischargeStatusTypes", "DischargeStatusTypeIDNum=" &
GetVarValFromArg(strSearchCriteria, "DCStatus"))
txtTeamCriteria = DLookup("TeamName", "qryLISTALL_Teams",
"TeamIDNum=" & GetVarValFromArg(strSearchCriteria, "Team"))
txtResidentCriteria = DLookup("ResidentName",
"qryLISTALL_Residents", "ResidentIDNum=" &
GetVarValFromArg(strSearchCriteria, "Resident"))
txtInternCriteria = DLookup("InternName", "qryLISTALL_Interns",
"InternIDNum=" & GetVarValFromArg(strSearchCriteria, "Intern"))
End If
End Sub

All of the stuff in the Else clause was commented out for the purpose of
this experiment so errors wouldn't be triggered without the OpenArgs
argument.

By the way, I REALLY appreciate the work you're doing on this, but believe
me, I'm not trying to get you to do this for me! Don't feel you have to
continue!!

John

 

[Dirk Goldgar]

Dear Dirk,

I'm using Access 2000 and here's the code for the form's OnOpen event:

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
Cancel = True
MsgBox "No admissions meet these criteria.", vbExclamation
Else
strSearchCriteria = Me.OpenArgs
txtOpenArgs = strSearchCriteria
txtDCStatusCriteria = DLookup("DischargeStatusType",
"qryLIST_DischargeStatusTypes", "DischargeStatusTypeIDNum=" &
GetVarValFromArg(strSearchCriteria, "DCStatus"))
txtTeamCriteria = DLookup("TeamName", "qryLISTALL_Teams",
"TeamIDNum=" & GetVarValFromArg(strSearchCriteria, "Team"))
txtResidentCriteria = DLookup("ResidentName",
"qryLISTALL_Residents", "ResidentIDNum=" &
GetVarValFromArg(strSearchCriteria, "Resident"))
txtInternCriteria = DLookup("InternName", "qryLISTALL_Interns",
"InternIDNum=" & GetVarValFromArg(strSearchCriteria, "Intern"))
End If
End Sub

All of the stuff in the Else clause was commented out for the purpose of
this experiment so errors wouldn't be triggered without the OpenArgs
argument.

Huh. I don't see anything there that explains what you're seeing. Do me a
favor -- comment out *all* the code in the Open event, and try it again.

By the way, I REALLY appreciate the work you're doing on this, but believe
me, I'm not trying to get you to do this for me! Don't feel you have to
continue!!

That's okay -- I'm interested. I just wish my ideas so far had panned out.
While a workaround has been suggested (setting the form's OrderBy property),
I'd like to get at the underlying cause.

I don't have a copy of Access 2000 installed to test with, unfortunately.
I'm using Access 2003, and can also test with Access 2007. Is your copy of
Access 2000 fully up-to-date with service packs? When it first came out, it
had a number of bugs.

 

[John S. Ford, MD]

Dear Dirk,

When I click "About Microsoft Access" I get: Microsoft Access 2000
(9.0.3821 SR-1)

I completely disabled the form's OnOpen and OnCurrent events completely. I
also used the following command in my calling form:

Do.Cmd.OpenForm "frmAdmissionEntryForm" (no other arguments).

My form opens fine and I get fully sorted, unfiltered records. Basically
the same as the underlying recordsource query.

It seems like the WHERE argument disables the query's inherent sort routine.
I'm surprised that the WHERE argument can do this but let me ask you one
question.

the WHERE statement is quite complex and uses aggregate functions. Could
the use of aggregate functions in a WHERE statement do "more" than just
filter the query its acting on?

John

 

[Fred]

I have replicated the problem in 2003. I have the world's simplest database
(no code) and 2 seemingly indentical forms (both with no sorts and filters)
sourced from the same query. Form1 does not honor the query's sort, form2
does.


Not sure how I made form1turn bad...other than it happened when I was
fiddling with creating and undoing filters and sorts on the query and form.

 

[John S. Ford, MD]

By the way, when I sorted the form's final output using the OrderBy property
in the properties list, it sorted perfectly. At least I know that I have a
viable workaround. But this is violating my general world view!

Like you, I'm also trying to identify the underlying cause. I just don't
see how adding a WHERE statement can alter the sort order.

John

 

[Fred]

I have replicated the problem in 2003. I have the world's simplest database
(no code) and 2 seemingly indentical forms (both with no sorts and filters)
sourced from the same query. Form1 does not honor the query's sort, form2
does.


Not sure how I made form1turn bad...other than it happened when I was
fiddling with creating and undoing filters and sorts on the query and form.

 

[Albert D. Kallal]

I have a form, frmMain that uses a query as its underlying datasource. The
query sorts the records in a certain way. How come the records aren't
sorted the same way by frmMain?

Does it matter that the frmMain is filtered by a WHERE statement passed by
a search form, frmSearch that calls and opens it?

The answer to this problem is quite simple.

first, open up the form in design mode and remove both the order by, and
filter settings.

If you have an order by setting and just open the form, the order by setting
is NOT respected until you execute

me.OrderByOn = True

If you open the form using an "where" clause, then if you viewed the forms
property sheet, you see that the filter setting has a value.

So, if you have orderby set in the form and use a "where" clause..then the
orderby setting in the property sheet IS respected. If you open the form
without a where clause...the orderby setting is ignored and the order will
by the the calling query...

 

[Albert D. Kallal]

By the way, when I sorted the form's final output using the OrderBy
property in the properties list, it sorted perfectly. At least I know
that I have a viable workaround. But this is violating my general world
view!

Like you, I'm also trying to identify the underlying cause. I just don't
see how adding a WHERE statement can alter the sort order.

See my other response. How this works if you pass a "where" clause, then the
forms order by setting is respected. If you don't pass a where, then the
query order is used....

You just have to open up the form in design mode and remove the orderby
value.....
(while you at this...remove anything in the filter setting also)

 

[Fred]

I had missed the fact that the form wizard automatically populated the
orderby.

BTW I discovered the other thing that made it complicated. The query has
an second "Orderby" which is separate from the SQL/grid. For view-in-query
purposes, it overrides the SQL/grid. The form view (with the form OrderBy
empty) does not follow it (it follows the SQL) but the Form Wizard DOES
look at it and automatically loads it into the form's OrderBy property.

 

[John S. Ford, MD]

Albert,

I've cleared both the OrderBy and filter settings from the form's property
sheet and it still doesn't sort per the underlying query.

John

 

[John W. Vinson]

Forms and reports ignore the order of your query.

In my experience, Forms honor the query sort (unless the form's OrderBy and
OrderByOn properties override it). Reports don't, they use their own Sorting
and Grouping feature to define the sort order; Forms don't have that feature.

 

[Fred]

Albert,

Re:
"If you open the form > without a where clause...the orderby setting is
ignored..." and the order will > by the the calling query..."


I'm sure that I know only 1/zillionth of what you do on this, but what I
did/do observe here is that the form's "orderby" is used when you simply
open the form.

 

[Albert D. Kallal]

Albert,

I've cleared both the OrderBy and filter settings from the form's property
sheet and it still doesn't sort per the underlying query.

John

While in the form, if you go records->remove sort....does that help?

 

[Albert D. Kallal]

Albert,

Re:
"If you open the form > without a where clause...the orderby setting is
ignored..." and the order will > by the the calling query..."


I'm sure that I know only 1/zillionth of what you do on this, but what I
did/do observe here is that the form's "orderby" is used when you simply
open the form.

correct...I have to explain this a bit better:

In a case of the forms filter option, and the order by option, neither of
them do anything when you open a form. However if you've opened the form and
used the GUI to set the filter or "order by" with the GUI (right click on a
field...sort for example), then YES the form remembers the settings when you
open the form next time.

When you have the form opened and it is remember the order by, now go

Records->remove Filter/Sort

Notice how the above option removes both the order by and the filtering
option
in the above IN ONE SHOT (it is one click...both options are disabled).

If you close the form and then reopen it, you'll notice that the order is
not being set. However if you open up the form in design mode and look at
the property sheet, you WILL see an order by setting is still in existence.
In
other words we have an order by set in the form, but it's not active
anymore. If you open up a form with a where clause, then that order
by setting kicks in!. (this describes pretty much John's scenario).

Furthermore, in my example I was stating that if you actually typed in the
order by command into the Property sheet while in design mode close/save
the form, you will notice a next time you open the form the order by clause
is ignored.

You can also open up the form, and also hit the apply filter sort button.

So what I was saying is that if you have a sort on the form that's not
active (you hit the remove/filter sort button), **or** you typed in the
order by command into the forms property sheet manually, than that order by
setting for the form is ignored until you open the form with a where clause
in code.

So my explanation really wasn't quite full before. You are correct that if
you sort of form using the graphical user interface, then
sort, when you reopen the form next time that's filtering and sorting will
remain in effect. However if you hit the remove filter sort button, you look
at the property sheet the filter and the order by are still set the form,
but they'll continue to be ignored - you get the underlying query order in
this case. at this point you can open and close the form many times and the
order will not be set, however if you hit the apply filter sort button
again, it'll go back to your original sorting and the form remembers this by
use the order by clause in the form's propery sheet...

 

[John S. Ford, MD]

Dear Albert,

Very interesting. If I do the records->"remove sort" the form REVERTS to
sorting presumably relying on the query's own sort.

What does THAT mean?

John

 

[Dirk Goldgar]

Very interesting. If I do the records->"remove sort" the form REVERTS to
sorting presumably relying on the query's own sort.

What does THAT mean?

It means that the form is acting as Albert has described; a behavior that I
never observed before, so I've learned something (cool!) . Removing the
sort sets the form's OrderByOn property to False, and so it reverts to the
query's sort sequence.

I expect that Albert's advice -- open the form in design view, clear the
Order By property, and save it -- will have solved your problem. If not,
you could add code to the form's Open event to set the OrderByOn property to
False:

Me.OrderByOn = False

.... and that should also solve your problem.

 

[Stuart McCall]

Dear Dirk,

The form can't be opened directly from the database window because it
triggers an error (it expects to see an OpenArgs string which isn't sent
if it's opened directly).

Hi John

PMFJI. For future reference, you could have tested the form standalone but
with OpenArgs in place so you don't have to comment out your OpenArgs
processing code. Simply open the form via a DoCmd.OpenForm call in the VBE's
Immediate window, supplying the OpenArgs argument.

Hope that comes in handy sometime.

 

[John S. Ford, MD]

Dear Dirk,

I did BOTH of those things (actually the property sheet shows my OrderBy
property is blank). I did put the line of code in my OnOpen event but that
didn't fix the problem.

John