How to sort records in a subform control?

I have created two forms: frmBrowseNegsMainform and
frmBrowseNegsSubform. I put a subform control on the
first of these. The SourceObject property for the subform
control is, of course, frmBrowseNegsSubform.

I would like to perform an ascending or descending sort
on any of the 7 columns shown in datasheet view in the
subform control. I've been unsuccessful.

Is there something tricky about sorting in subform controls?

Use command buttons for the column headings and in the Click event of each
button, set the OrderByOn property to True, the OrderBy property to the name of
the field under the button and requery the form.

Note: You can use labels also because they have a Click event but buttons make
it look like they can be clicked.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

"MLH" <CRCI@NorthState.net> wrote in message
news:brs5g0tj2u9s7hh6go8vvn02hrer09d70v@4ax.com...[color=blue]
> I have created two forms: frmBrowseNegsMainform and
> frmBrowseNegsSubform. I put a subform control on the
> first of these. The SourceObject property for the subform
> control is, of course, frmBrowseNegsSubform.
>
> I would like to perform an ascending or descending sort
> on any of the 7 columns shown in datasheet view in the
> subform control. I've been unsuccessful.
>
> Is there something tricky about sorting in subform controls?[/color]

I neglected to mention the problem is in Access 2.0, which supports
no OrderBy or OrderByOn properties. Please excuse.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx

On Sun, 25 Jul 2004 00:11:49 GMT, "PC Datasheet" <nospam@nospam.spam>
wrote:
[color=blue]
>Use command buttons for the column headings and in the Click event of each
>button, set the OrderByOn property to True, the OrderBy property to the name of
>the field under the button and requery the form.
>
>Note: You can use labels also because they have a Click event but buttons make
>it look like they can be clicked.[/color]

"MLH" <CRCI@NorthState.net> wrote in message
news:7i06g0lr86q9cqcmhbsfvu078qaam7g4pj@4ax.com...[color=blue]
> I neglected to mention the problem is in Access 2.0, which supports
> no OrderBy or OrderByOn properties. Please excuse.
>[/color]

That's pretty funny. You're excused!

I have never used Access 2.0 so don't know if this is available! Another way to
do it is to create the same command buttons (or use labels) and code each to
build a querydef on the fly. You would then requery the form.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

"MLH" <CRCI@NorthState.net> wrote in message
news:7i06g0lr86q9cqcmhbsfvu078qaam7g4pj@4ax.com...[color=blue]
> I neglected to mention the problem is in Access 2.0, which supports
> no OrderBy or OrderByOn properties. Please excuse.
>
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxx
>
> On Sun, 25 Jul 2004 00:11:49 GMT, "PC Datasheet" <nospam@nospam.spam>
> wrote:
>[color=green]
> >Use command buttons for the column headings and in the Click event of each
> >button, set the OrderByOn property to True, the OrderBy property to the name[/color][/color]
of[color=blue][color=green]
> >the field under the button and requery the form.
> >
> >Note: You can use labels also because they have a Click event but buttons[/color][/color]
make[color=blue][color=green]
> >it look like they can be clicked.[/color]
>[/color]

"MLH" wrote
[color=blue]
> I neglected to mention the problem is in Access 2.0, which supports
> no OrderBy or OrderByOn properties. Please excuse.[/color]

Have you tried replacing the RecordSource of the Form with an SQL Statement
that includes a different ORDER BY clause? I'm quite certain that Access
2.0's Jet SQL includes the clause.

Access 2.0 was and is a good, small-footprint database and database
development tool -- later versions did, or course, add features and
functions, and patched up the 'worst' hole in Access 2.0 security.

Larry Linson
Microsoft Access MVP

Yes, Larry, I did try that. I opened the actual subform in datasheet
view and verified that when I replaced the RecordSource of the
Form with an SQL statement having a different ORDER BY clause.
The sort order on the actual subform DID change as intended. But
nothing I did could make the changes show up in the subform control.

Do you think I can open the actual subform in design view, modify
the RecordSource property and save it with the new settings - all
from within code? I believe, if I do that, then requery the subform
control, I'll get what I want. Lets see...

DoCmd OpenForm "frmBrowseNegsSubform", A_DESIGN
MySQL = "blah blah blah"
Forms!frmBrowseNegsSubform.RecordSource = MySQL
Hmmm??? What command line would save the form & the changes???
Me!MySubformControl.Requery

Think that'll work?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxx

On Sun, 25 Jul 2004 01:20:07 GMT, "Larry Linson"
<bouncer@localhost.not> wrote:
[color=blue]
>"MLH" wrote
>[color=green]
>> I neglected to mention the problem is in Access 2.0, which supports
>> no OrderBy or OrderByOn properties. Please excuse.[/color]
>
>Have you tried replacing the RecordSource of the Form with an SQL Statement
>that includes a different ORDER BY clause? I'm quite certain that Access
>2.0's Jet SQL includes the clause.
>
>Access 2.0 was and is a good, small-footprint database and database
>development tool -- later versions did, or course, add features and
>functions, and patched up the 'worst' hole in Access 2.0 security.
>
> Larry Linson
> Microsoft Access MVP
>[/color]

Damn! The code below didn't work either. I could open the actual
subform in design mode easily enough, make the changes, close
the form, saving the changes on the way out. But neither the Requery
Action nor the Requery Method would rearranget the records already
displayed in the subform control. I even put a DoEvents statement
prior to the Requery Action/Method. The only thing that works is
closing the main form and reopening it. The subform control then
displays the records in the newly sorted order. But there's just no
way I'm gonna implement that as a strategy. I'm stuck here!

[color=blue]
>DoCmd OpenForm "frmBrowseNegsSubform", A_DESIGN
>MySQL = "blah blah blah"
>Forms!frmBrowseNegsSubform.RecordSource = MySQL
>Hmmm??? What command line would save the form & the changes???
>Me!MySubformControl.Requery[/color]

> >Forms!frmBrowseNegsSubform.RecordSource = MySQL

Two problems here:
1. A subform is not part of the Forms collection; it belongs to the main form's
collection. You need to reference the subform like this:
Forms!NameOfMainForm!frmBrowseNegsSubform.Form

2. A subform control does not have a recordsource property! The form in the
subform control does though. Try:
Forms!NameOfMainForm!frmBrowseNegsSubform.Form.Rec ordSource = MySQL
[color=blue][color=green]
> >Me!MySubformControl.Requery[/color][/color]
You can't requery a subform control either but again you can requery the form
the control holds:
Me!MySubformControl.Form.Requery

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdatasheet.com
www.pcdatasheet.com

"MLH" <CRCI@NorthState.net> wrote in message
news:9356g093g7m490bl9558h9ujdd6nmj0r3r@4ax.com...[color=blue]
> Damn! The code below didn't work either. I could open the actual
> subform in design mode easily enough, make the changes, close
> the form, saving the changes on the way out. But neither the Requery
> Action nor the Requery Method would rearranget the records already
> displayed in the subform control. I even put a DoEvents statement
> prior to the Requery Action/Method. The only thing that works is
> closing the main form and reopening it. The subform control then
> displays the records in the newly sorted order. But there's just no
> way I'm gonna implement that as a strategy. I'm stuck here!
>
>
>[color=green]
> >DoCmd OpenForm "frmBrowseNegsSubform", A_DESIGN
> >MySQL = "blah blah blah"
> >Forms!frmBrowseNegsSubform.RecordSource = MySQL
> >Hmmm??? What command line would save the form & the changes???
> >Me!MySubformControl.Requery[/color]
>[/color]

Thanks for the tip, PCD. I'll give it a try. At this point, all I've
tried has gotten me nowhere. I did look up this Access 2.0
help blurb. It seems to suggest that I can use the Requery
method with subform controls. But I'm not having any luck...

Requery Method
See Also Example 2.0

Applies To
Forms. Controls (combo boxes, list boxes, subforms/subreports).

Description

Updates the data in a specified form or in a control that is on the
active object by requerying the source of data of the form or control.

Syntax
objectname.Requery

Argument Description
objectname The name of the form or control you want to update.
Omit this argument to requery the source of data of the active object.

Remarks

Use this method to ensure that the form or control displays the most
recent data. The Requery method does one of the following:

Reruns the query on which the form or control is based.
Displays any new or changed records or removes deleted records
from the table on which the form or control is based.

Controls based on a query or table include:

List boxes and combo boxes
Subform controls
OLE objects, such as graphs
Controls containing aggregate functions such as DSum

If the specified control isn't bound to a field in a table or query,
this method forces a recalculation of the control.
If you omit objectname, the Requery method has the same effect as
pressing SHIFT+F9 when a form or control has the focus (it requeries
the underlying tables). If a subform control has the focus, this
method requeries only the source of data of the subform.

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Sun, 25 Jul 2004 02:17:21 GMT, "PC Datasheet" <nospam@nospam.spam>
wrote:
[color=blue][color=green][color=darkred]
>> >Forms!frmBrowseNegsSubform.RecordSource = MySQL[/color][/color]
>
>Two problems here:
>1. A subform is not part of the Forms collection; it belongs to the main form's
>collection. You need to reference the subform like this:
>Forms!NameOfMainForm!frmBrowseNegsSubform.Form
>
>2. A subform control does not have a recordsource property! The form in the
>subform control does though. Try:
>Forms!NameOfMainForm!frmBrowseNegsSubform.Form.Re cordSource = MySQL
>[color=green][color=darkred]
>> >Me!MySubformControl.Requery[/color][/color]
>You can't requery a subform control either but again you can requery the form
>the control holds:
>Me!MySubformControl.Form.Requery[/color]

Sigh! No relief in sight. I'm having no trouble at all when
it comes to manipulating the subform itself. Its the subform
control on the main form that isn't cooperating. I'm still in
the same boat - the only way I can see the new sort order
in the subform control on the main form is to close and
reopen the main form. I cannot seem to light a fire under
the subform control - nothing seems to work.

Its funny, I have another main form with a subform control
in the very same application. I get exactly what I want there
I created 2 subforms and I switch the subform control's
SourceObject Property setting to point to either of the
desired subforms, then I run this line...

Me![frmOnHoldAdministratorSub].Requery

from a command button on the main form and I get EXACTLY
what I want. In the above line, Me!frmOnHoldAdministratorSub
refers to the subform control on the main form. The Requery
Method works just fine there, but not in my other form. Heck!

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx

On Sun, 25 Jul 2004 02:17:21 GMT, "PC Datasheet" <nospam@nospam.spam>
wrote:
[color=blue][color=green][color=darkred]
>> >Forms!frmBrowseNegsSubform.RecordSource = MySQL[/color][/color]
>
>Two problems here:
>1. A subform is not part of the Forms collection; it belongs to the main form's
>collection. You need to reference the subform like this:
>Forms!NameOfMainForm!frmBrowseNegsSubform.Form
>
>2. A subform control does not have a recordsource property! The form in the
>subform control does though. Try:
>Forms!NameOfMainForm!frmBrowseNegsSubform.Form.Re cordSource = MySQL
>[color=green][color=darkred]
>> >Me!MySubformControl.Requery[/color][/color]
>You can't requery a subform control either but again you can requery the form
>the control holds:
>Me!MySubformControl.Form.Requery[/color]

I'm sorry I didn't make myself clear... you don't need to open the Form
embedded in the Subform Control in Design View, you can change the
RecordSource at runtime. Assume that on the Form embedded in the Subform
Control, you have two Buttons... one for Sort by Ascending JobCode and the
other Sort by Descending JobCode, and that the RecordSource is a Query
pulling selected fields from tblJobTable (a copy of which, without any
sorting, is stored in the Form-level variable strSQL of the Form embedded in
the Subform Control).

In the button click event for the first button

Me.RecordSource = strSQL & " ORDER BY JobCode ASC"

In the button click event for the second button

Me.RecordSource = strSQL & " ORDER BY JobCode DESC"

These settings will last until you change them by clicking the other button
or until you close the Form (by closing the main Form that contains the
Subform control in which this Form is displayed). They will not be preserved
until the next time the Form or the Database is Opened.

If you put those buttons on the main form, then the reference, as PC
Datasheet explained, is a little more complex.

Larry Linson
Microsoft Access MVP

"MLH" <CRCI@NorthState.net> wrote in message
news:07a6g0p78s53skhcn3cdvj8n448h2oi021@4ax.com...[color=blue]
> Sigh! No relief in sight. I'm having no trouble at all when
> it comes to manipulating the subform itself. Its the subform
> control on the main form that isn't cooperating. I'm still in
> the same boat - the only way I can see the new sort order
> in the subform control on the main form is to close and
> reopen the main form. I cannot seem to light a fire under
> the subform control - nothing seems to work.
>
> Its funny, I have another main form with a subform control
> in the very same application. I get exactly what I want there
> I created 2 subforms and I switch the subform control's
> SourceObject Property setting to point to either of the
> desired subforms, then I run this line...
>
> Me![frmOnHoldAdministratorSub].Requery
>
> from a command button on the main form and I get EXACTLY
> what I want. In the above line, Me!frmOnHoldAdministratorSub
> refers to the subform control on the main form. The Requery
> Method works just fine there, but not in my other form. Heck!
>
> xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xx
>
>
>
> On Sun, 25 Jul 2004 02:17:21 GMT, "PC Datasheet" <nospam@nospam.spam>
> wrote:
>[color=green][color=darkred]
> >> >Forms!frmBrowseNegsSubform.RecordSource = MySQL[/color]
> >
> >Two problems here:
> >1. A subform is not part of the Forms collection; it belongs to the main[/color][/color]
form's[color=blue][color=green]
> >collection. You need to reference the subform like this:
> >Forms!NameOfMainForm!frmBrowseNegsSubform.Form
> >
> >2. A subform control does not have a recordsource property! The form in[/color][/color]
the[color=blue][color=green]
> >subform control does though. Try:
> >Forms!NameOfMainForm!frmBrowseNegsSubform.Form.Re cordSource = MySQL
> >[color=darkred]
> >> >Me!MySubformControl.Requery[/color]
> >You can't requery a subform control either but again you can requery the[/color][/color]
form[color=blue][color=green]
> >the control holds:
> >Me!MySubformControl.Form.Requery[/color]
>[/color]

On Sat, 24 Jul 2004 23:39:22 -0400, MLH <CRCI@NorthState.net> wrote:
[color=blue]
>Sigh! No relief in sight. I'm having no trouble at all when
>it comes to manipulating the subform itself. Its the subform
>control on the main form that isn't cooperating. I'm still in
>the same boat - the only way I can see the new sort order
>in the subform control on the main form is to close and
>reopen the main form. I cannot seem to light a fire under[/color]

Why not with code close the form and reopen so fast no one notices