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
  #1  
Old April 8th, 2009, 07:53 AM 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

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?

Thanks in advance,
John


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

Yes, the where statement will affect the sort. Put a label on your form.
Right click on the label to get properties and go to events. Go to the
onclick event and start the code builder. Type:
Me.orderby = "[yourfield]"
Me.orderbyon = True
You can then push the label and sort. You can have multiple labels for
multiple ways of sorting.

"John S. Ford, MD" wrote:

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?

Thanks in advance,
John



  #3  
Old April 8th, 2009, 01:07 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Form doesn't sort records per the underlying query

Hi John,
what happens when you open frmMain from the database window or nav pane?
How does it sort in that scenario?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"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?

Thanks in advance,
John



  #4  
Old April 8th, 2009, 01:28 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Kc-Mass
external usenet poster
 
Posts: 362
Default Form doesn't sort records per the underlying query

Hi John

Forms and reports ignore the order of your query.

Open the form in design view and fill in the OrderBy field with
the NameOfQuery!Nameof Field.

Unfortunately you can only sort by one field but you could
build a composite field in your query depending on the fields of interest.

Regards

Kevin

"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?

Thanks in advance,
John



  #5  
Old April 8th, 2009, 01:34 PM posted to microsoft.public.access,microsoft.public.access.forms,microsoft.public.access.formscoding
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Form doesn't sort records per the underlying query

Hi KC-Mass,
you wrote Forms and reports ignore the order of your query.
That is not correct. Forms and reports do generally sort in the same way as
the underlying query is sorted. That is one of the main reasons to use a
query for a form or report's record source instead of a table.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"KC-Mass" connearney_AT_comcast_Period_net wrote in message
...
Hi John

Forms and reports ignore the order of your query.

Open the form in design view and fill in the OrderBy field with
the NameOfQuery!Nameof Field.

Unfortunately you can only sort by one field but you could
build a composite field in your query depending on the fields of interest.

Regards

Kevin

"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?

Thanks in advance,
John





  #6  
Old April 8th, 2009, 02:56 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

"KC-Mass" connearney_AT_comcast_Period_net wrote in message
...
Hi John

Forms and reports ignore the order of your query.


Forms follow the order of the query, unless the OrderBy property is in
effect. By contrast, in reports the Sorting and Grouping specified in the
report design overrides the query/ However, if there is no sorting/grouping
in the report, and no OrderBy property in effect, then the query's ort order
is respected.

Open the form in design view and fill in the OrderBy field with
the NameOfQuery!Nameof Field.

Unfortunately you can only sort by one field but you could
build a composite field in your query depending on the fields of interest.


Actually, you can specify more than one field in the Order By property.
Just list the field names, separated by commas.

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

(please reply to the newsgroup)

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

"golfinray" wrote in message
...
Yes, the where statement will affect the sort.


I don't think it should. I'm not sure what's going on in Dr. Ford's case.

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

(please reply to the newsgroup)

  #8  
Old April 8th, 2009, 03:05 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 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?


I don't think it should, if you're doing what I think. Would you mind
posting the code that opens the form, including any surrounding, relevant
code? Is it possible that you are modifying the form's RecordSource on the
fly, either in the code that opens the form or in the form's Open event?

When you say "the query sorts the records in a certain way," do you mean
that the query explicitly sorts the records using an ORDER BY clause? Or do
you just mean that they come out a particular way in the absence of an
explicit ordering?

Does the form show the same sort sequence when you open it from the database
window, rather than from your search form?

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

(please reply to the newsgroup)

  #9  
Old April 8th, 2009, 04:26 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

Thanks to everyone who is trying to solve this problem!

Here is the command from another form that opens the form in question:

DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE, , , _
"DCStatus=" & lstDischargeStatusCriteria & _
":Team=" & lstTeamCriteria & _
":Attending=" & cboAttendingCriteria & _
":Resident=" & cboResidentCriteria & _
":Intern=" & cboInternCriteria

John

"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?

Thanks in advance,
John



  #10  
Old April 8th, 2009, 04:30 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

Dirk,

Here is the SQL for the underlying query:

SELECT tblDATA_Admissions.*, tblDATA_Patients.*
FROM tblDATA_Admissions LEFT JOIN tblDATA_Patients ON
tblDATA_Admissions.PatientIDNum = tblDATA_Patients.PatientIDNum
ORDER BY tblDATA_Patients.PatientNameLast,
tblDATA_Patients.PatientNameFirst, tblDATA_Admissions.PatientDateAdmit DESC;

It sorts the data as expected. When I use the form, the sort is unordered.

John

"Dirk Goldgar" wrote in message
...
"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?


I don't think it should, if you're doing what I think. Would you mind
posting the code that opens the form, including any surrounding, relevant
code? Is it possible that you are modifying the form's RecordSource on
the fly, either in the code that opens the form or in the form's Open
event?

When you say "the query sorts the records in a certain way," do you mean
that the query explicitly sorts the records using an ORDER BY clause? Or
do you just mean that they come out a particular way in the absence of an
explicit ordering?

Does the form show the same sort sequence when you open it from the
database window, rather than from your search form?

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

(please reply to the newsgroup)



 




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 04:42 PM.


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