subform sort doesn't stay sorted

  • Thread starter Thread starter Angi
  • Start date Start date
A

Angi

I can't figure out why this happening! I have a subform that is
supposed to be sorted by last name. When I open the subform
separately, it works fine. When I open the main form, it sorts by
order entered even though the properties say last name. Does anyone
know why this would happen or how I can fix it? I've tried deleting
the subform and adding it again, but it does the same thing.

Thanks so much!
Angi
 
Have you checked:

* The ORDER BY clause in the RecordSource for the Subform

* The OrderBy and OrderByOn Prperty of the Form being used as the Subform
(more correctly, being used as the SourceObject for the SubformControl)

* Any code that changes the OrderBy & OrderByOn Property.
 
Van,
The Order By on ContactSubForm is set to ContactMain.LastName...which
is correct. As far as the SourceObject it's set to ContactSubForm.
Should there be something else there? The forms don't have any code.
Like I said, I'm confused as to why the subform does it on it's own but
not while it's being viewed in the main form. Even if I sort it while
viewing the main form, it doesn't stay that way.

Thx again!
 
I thought normally, you don't use the Table qualifier in the OrderBy. Just
the Field name should be enough.

However, it sounds like the code somewhere else doing this. Check the code
on the MainForm and see if anything referring to the OrderBy Property of the
Subform.
 
Van,
The A-Z sort put the table qualifier in there. When I took it out, it
stopped sorting again...even when I open it by itself. As far as code
on the main form, there isn't any except a command button. There's
absolutely no code on the subform.

Ok...just tried sorting again in the main form view and now it's
keeping it. I don't know what's going on, but it's working, so thank
you for all your time. Sorry about that. Don't know what happened!

Thx!
Ang
 
OK!!! This is absolutely driving me crazy!!! It's not sorting again
on the main form and I've done everything I know how to do. I've set
the table to sort ascending. The subform opened alone, sorts
ascending. I sort it with the main form open and all is good until I
close it and re-open. There is no code on the subform. The Order By
is set to contactmain.lastname, even when I view the main form through
design mode. I'm lost and tired!!! Is there a way to programmatically
sort the subform??
 
Have you tried setting the OrderBy (FieldName) & OrderByOn (Yes/True)
Property of the Form (in DesignView) being used as the Subform (more
accurately, being used as the SourceObject of the SubformControl)?
 
Van,
The OrderBy on the subform is set to contactmain.lastname. I don't
have a OrderByOn property. I'm using Acc2002.
 
Van,
Please forgive me, but what SQL string? I only know SQL relating to
queries and there aren't queries or SQL running on the main form or
subform. The RecordSource for the subform is ContactMainSubform. The
recordsource on the subform is the contactmain table. All tables are
connected by the CoID. Does that help at all? Should I write the SQL
that simulates what the form does (or is supposed to do)? Like I said,
please forgive me!!!
 
OK. The Table is the Problem.

If you want to ensure that the Records are returned in a certain order, you
need to specify the sort order in the query and use the query as the
RecordSource for the (Sub)Form. Database Theory does not require Records
are returned in any particular order for efficient retrieval and any
required ordering needs to be specified.

Click in the RecordSource row in the Property window of the Subform, a
"Build" button with 3 dots will appear on the right. Click on the "Build"
button and build a query with sorting / ordering the way you want.
 
Van,
Thank you for not giving up on me!! I didn't know that. I thought
that the subform would take on the same properties as the form, just
viewed on another form! Of course, your suggestion worked and it took
me all of 2 seconds! Thank you so much. I feel my sanity coming back!

Sincerely grateful,
Angi
 
Just when you thought this topic was closed...I'm back!

The sort for last name is working great. The problem I'm having now is
I need to add the first name. ie: lastname, then firstname. Btest
Whatever is above Atest Whatever and it shouldn't be.

I've tried adding a sort to the firstname field like I did the
lastname, but my subform comes up with #Name? in all the fields. The
fields are on the form so now what's the problem??
 
I am not sure what BTest / ATest meant. Perhaps you should post the Table
Fields + a bit of explanation of each Field and the SQL String of the Query
you used as the RecordSource for the Subform.
 
Sorry...Btest and Atest where supposed to represent first names and
Whatever the last name. On a report you can sort by one field, then by
another and another if needed. I just need it to sort by LastName
(works) and then by Firstname(#Name? error).

RecordSource: SELECT ContactMain.*, ContactMain.LastName FROM
ContactMain ORDER BY ContactMain.LastName;

The ContactMain fields are Salutation, FirstName, LastName, Phone, Ext,
Fax. All text fields, all allowed to be empty.

thx
 
You can have multiple Sort Fields in the Query also. Try:

SELECT ContactMain.*
FROM ContactMain
ORDER BY ContactMain.LastName, ContactMain.FirstName;

(don't need to specify LastName in the Select phrase. It is already
included in the *)
 
Back
Top