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 |
#11
|
|||
|
|||
Form doesn't sort records per the underlying query
File this under "I'll never figure this out." For reasons that completely
elude me, this code has suddenly started working as expected. Thanks for the help. I still don't understand what I did to make it suddenly start working. 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 |
#12
|
|||
|
|||
Form doesn't sort records per the underlying query
Sorry. This was a mistaken post meant for another post (another problem).
The problem discussed here has NOT been solved. John "John S. Ford, MD" wrote in message ... File this under "I'll never figure this out." For reasons that completely elude me, this code has suddenly started working as expected. Thanks for the help. I still don't understand what I did to make it suddenly start working. John |
#13
|
|||
|
|||
Form doesn't sort records per the underlying query
"John S. Ford, MD" wrote in message
... 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; And this is the actual recordsource for the form? Or does the form use a query that selects from this query? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#14
|
|||
|
|||
Form doesn't sort records per the underlying query
Dear Dirk,
Yes, this is the recordsource for the form (which sorts the data as expected). The calling form opens it using this code he DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE, , , _ "DCStatus=" & lstDischargeStatusCriteria & _ ":Team=" & lstTeamCriteria & _ ":Attending=" & cboAttendingCriteria & _ ":Resident=" & cboResidentCriteria & _ ":Intern=" & cboInternCriteria strFinalWHERE is a WHERE statement build up by the calling form (which is a search form). frmAdmissonEntryForm, the form then opens and everything is perfectly filtered, the right records are shown but they're not sorted the way the underlying query is sorted. John "Dirk Goldgar" wrote in message ... "John S. Ford, MD" wrote in message ... 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; And this is the actual recordsource for the form? Or does the form use a query that selects from this query? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#15
|
|||
|
|||
Form doesn't sort records per the underlying query
"John S. Ford, MD" wrote in message
... Yes, this is the recordsource for the form (which sorts the data as expected). The calling form opens it using this code he DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE, , , _ "DCStatus=" & lstDischargeStatusCriteria & _ ":Team=" & lstTeamCriteria & _ ":Attending=" & cboAttendingCriteria & _ ":Resident=" & cboResidentCriteria & _ ":Intern=" & cboInternCriteria strFinalWHERE is a WHERE statement build up by the calling form (which is a search form). frmAdmissonEntryForm, the form then opens and everything is perfectly filtered, the right records are shown but they're not sorted the way the underlying query is sorted. That is odd. If you open the form directly from the database window (or navigation pane, if Access 2007), does it open unordered or in sequence according to the query? Is there any code (or macros) in the Open or Load events of frmAdmissionEntryForm? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#16
|
|||
|
|||
Form doesn't sort records per the underlying query
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). John "Dirk Goldgar" wrote in message ... "John S. Ford, MD" wrote in message ... Yes, this is the recordsource for the form (which sorts the data as expected). The calling form opens it using this code he DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE, , , _ "DCStatus=" & lstDischargeStatusCriteria & _ ":Team=" & lstTeamCriteria & _ ":Attending=" & cboAttendingCriteria & _ ":Resident=" & cboResidentCriteria & _ ":Intern=" & cboInternCriteria strFinalWHERE is a WHERE statement build up by the calling form (which is a search form). frmAdmissonEntryForm, the form then opens and everything is perfectly filtered, the right records are shown but they're not sorted the way the underlying query is sorted. That is odd. If you open the form directly from the database window (or navigation pane, if Access 2007), does it open unordered or in sequence according to the query? Is there any code (or macros) in the Open or Load events of frmAdmissionEntryForm? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#17
|
|||
|
|||
Form doesn't sort records per the underlying query
Dear Dirk,
I changed the DoCmd.OpenForm to simply open it without the strFinalWHERE and the OpenArgs argument: DoCmd.OpenForm "frmAdmissionEntryForm" and commented out the code using the OpenArgs argument in frmAdmissionEntryForm. When I do this, the form's records ARE sorted correctly (but obviously not filtered). So it appears to be something related to the DoCmd.OpenForm code I'm using. John "Dirk Goldgar" wrote in message ... "John S. Ford, MD" wrote in message ... Yes, this is the recordsource for the form (which sorts the data as expected). The calling form opens it using this code he DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE, , , _ "DCStatus=" & lstDischargeStatusCriteria & _ ":Team=" & lstTeamCriteria & _ ":Attending=" & cboAttendingCriteria & _ ":Resident=" & cboResidentCriteria & _ ":Intern=" & cboInternCriteria strFinalWHERE is a WHERE statement build up by the calling form (which is a search form). frmAdmissonEntryForm, the form then opens and everything is perfectly filtered, the right records are shown but they're not sorted the way the underlying query is sorted. That is odd. If you open the form directly from the database window (or navigation pane, if Access 2007), does it open unordered or in sequence according to the query? Is there any code (or macros) in the Open or Load events of frmAdmissionEntryForm? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#18
|
|||
|
|||
Form doesn't sort records per the underlying query
"John S. Ford, MD" wrote in message
... I changed the DoCmd.OpenForm to simply open it without the strFinalWHERE and the OpenArgs argument: DoCmd.OpenForm "frmAdmissionEntryForm" and commented out the code using the OpenArgs argument in frmAdmissionEntryForm. When I do this, the form's records ARE sorted correctly (but obviously not filtered). So it appears to be something related to the DoCmd.OpenForm code I'm using. I suspect that it's something in the code you commented out, that is processing the OpenArgs argument. Please post that code. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#19
|
|||
|
|||
Form doesn't sort records per the underlying query
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. John "Dirk Goldgar" wrote in message ... "John S. Ford, MD" wrote in message ... Yes, this is the recordsource for the form (which sorts the data as expected). The calling form opens it using this code he DoCmd.OpenForm "frmAdmissionEntryForm", , , strFinalWHERE, , , _ "DCStatus=" & lstDischargeStatusCriteria & _ ":Team=" & lstTeamCriteria & _ ":Attending=" & cboAttendingCriteria & _ ":Resident=" & cboResidentCriteria & _ ":Intern=" & cboInternCriteria strFinalWHERE is a WHERE statement build up by the calling form (which is a search form). frmAdmissonEntryForm, the form then opens and everything is perfectly filtered, the right records are shown but they're not sorted the way the underlying query is sorted. That is odd. If you open the form directly from the database window (or navigation pane, if Access 2007), does it open unordered or in sequence according to the query? Is there any code (or macros) in the Open or Load events of frmAdmissionEntryForm? -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
#20
|
|||
|
|||
Form doesn't sort records per the underlying query
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. John "Dirk Goldgar" wrote in message ... "John S. Ford, MD" wrote in message ... I changed the DoCmd.OpenForm to simply open it without the strFinalWHERE and the OpenArgs argument: DoCmd.OpenForm "frmAdmissionEntryForm" and commented out the code using the OpenArgs argument in frmAdmissionEntryForm. When I do this, the form's records ARE sorted correctly (but obviously not filtered). So it appears to be something related to the DoCmd.OpenForm code I'm using. I suspect that it's something in the code you commented out, that is processing the OpenArgs argument. Please post that code. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|