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

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

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  
Old April 8th, 2009, 04:44 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
...
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  
Old April 8th, 2009, 04:53 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,

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  
Old April 8th, 2009, 04:59 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
...

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

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  
Old April 8th, 2009, 05:19 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 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  
Old April 8th, 2009, 05:27 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 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  
Old April 8th, 2009, 05: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

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

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:26 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.