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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|