A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Form doesn't sort records per the underlying query



 
 
Thread Tools Display Modes
  #21  
Old April 8th, 2009, 05:46 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Form doesn't sort records per the underlying query

"John S. Ford, MD" wrote in message
...

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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

  #22  
Old April 8th, 2009, 05:57 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Form doesn't sort records per the underlying query

"John S. Ford, MD" wrote in message
...
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

  #23  
Old April 8th, 2009, 05:58 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
John S. Ford, MD
external usenet poster
 
Posts: 215
Default Form doesn't sort records per the underlying query

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" wrote in message
...
"John S. Ford, MD" wrote in message
...

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, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



  #24  
Old April 8th, 2009, 06:10 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Dirk Goldgar
external usenet poster
 
Posts: 2,529
Default Form doesn't sort records per the underlying query

"John S. Ford, MD" wrote in message
...
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

  #25  
Old April 8th, 2009, 06:45 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
John S. Ford, MD
external usenet poster
 
Posts: 215
Default Form doesn't sort records per the underlying query

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


"Dirk Goldgar" wrote in message
...
"John S. Ford, MD" wrote in message
...
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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



  #26  
Old April 8th, 2009, 06:50 PM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access
Fred
external usenet poster
 
Posts: 1,451
Default Form doesn't sort records per the underlying query

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.



  #27  
Old April 8th, 2009, 06:52 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
John S. Ford, MD
external usenet poster
 
Posts: 215
Default Form doesn't sort records per the underlying query

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

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)



  #28  
Old April 8th, 2009, 07:08 PM posted to microsoft.public.access.forms,microsoft.public.access.formscoding,microsoft.public.access
Fred
external usenet poster
 
Posts: 1,451
Default Form doesn't sort records per the underlying query

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.


  #29  
Old April 8th, 2009, 07:48 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default Form doesn't sort records per the underlying query

"John S. Ford, MD" wrote in message
...
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 (Access MVP)
Edmonton, Alberta Canada



  #30  
Old April 8th, 2009, 07:50 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Albert D. Kallal
external usenet poster
 
Posts: 2,874
Default Form doesn't sort records per the underlying query

"John S. Ford, MD" wrote in message
...
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)


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:08 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.