Form doesn't sort records per the underlying query

[John S. Ford, MD]

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

 

[golfinray]

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.

 

[Jeanette Cunningham]

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

 

[KC-Mass]

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

 

[Jeanette Cunningham]

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

 

[Dirk Goldgar]

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]

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]

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?

 

[John S. Ford, MD]

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]

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

 

[John S. Ford, MD]

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]

Sorry. This was a mistaken post meant for another post (another problem).
The problem discussed here has NOT been solved.

John

 

[Dirk Goldgar]

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?

 

[John S. Ford, MD]

Dear Dirk,

Yes, this is the recordsource for the form (which sorts the data as
expected). The calling form opens it using this code here:

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]

Yes, this is the recordsource for the form (which sorts the data as
expected). The calling form opens it using this code here:

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?

 

[John S. Ford, MD]

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

 

[John S. Ford, MD]

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]

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.

 

[John S. Ford, MD]

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

 

[John S. Ford, MD]

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