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 |
#21
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|