473,605 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to sort records in a subform control?

MLH
I have created two forms: frmBrowseNegsMa inform and
frmBrowseNegsSu bform. I put a subform control on the
first of these. The SourceObject property for the subform
control is, of course, frmBrowseNegsSu bform.

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?
Nov 13 '05 #1
12 19001
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
re******@pcdata sheet.com
www.pcdatasheet.com
"MLH" <CR**@NorthStat e.net> wrote in message
news:br******** *************** *********@4ax.c om...
I have created two forms: frmBrowseNegsMa inform and
frmBrowseNegsSu bform. I put a subform control on the
first of these. The SourceObject property for the subform
control is, of course, frmBrowseNegsSu bform.

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?

Nov 13 '05 #2
MLH
I neglected to mention the problem is in Access 2.0, which supports
no OrderBy or OrderByOn properties. Please excuse.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxx

On Sun, 25 Jul 2004 00:11:49 GMT, "PC Datasheet" <no****@nospam. spam>
wrote:
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.


Nov 13 '05 #3

"MLH" <CR**@NorthStat e.net> wrote in message
news:7i******** *************** *********@4ax.c om...
I neglected to mention the problem is in Access 2.0, which supports
no OrderBy or OrderByOn properties. Please excuse.


That's pretty funny. You're excused!
Nov 13 '05 #4
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
re******@pcdata sheet.com
www.pcdatasheet.com
"MLH" <CR**@NorthStat e.net> wrote in message
news:7i******** *************** *********@4ax.c om...
I neglected to mention the problem is in Access 2.0, which supports
no OrderBy or OrderByOn properties. Please excuse.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxx

On Sun, 25 Jul 2004 00:11:49 GMT, "PC Datasheet" <no****@nospam. spam>
wrote:
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 ofthe field under the button and requery the form.

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

Nov 13 '05 #5
"MLH" wrote
I neglected to mention the problem is in Access 2.0, which supports
no OrderBy or OrderByOn properties. Please excuse.


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
Nov 13 '05 #6
MLH
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 "frmBrowseNegsS ubform", A_DESIGN
MySQL = "blah blah blah"
Forms!frmBrowse NegsSubform.Rec ordSource = MySQL
Hmmm??? What command line would save the form & the changes???
Me!MySubformCon trol.Requery

Think that'll work?

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxx
On Sun, 25 Jul 2004 01:20:07 GMT, "Larry Linson"
<bo*****@localh ost.not> wrote:
"MLH" wrote
I neglected to mention the problem is in Access 2.0, which supports
no OrderBy or OrderByOn properties. Please excuse.


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


Nov 13 '05 #7
MLH
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!
DoCmd OpenForm "frmBrowseNegsS ubform", A_DESIGN
MySQL = "blah blah blah"
Forms!frmBrows eNegsSubform.Re cordSource = MySQL
Hmmm??? What command line would save the form & the changes???
Me!MySubformCo ntrol.Requery


Nov 13 '05 #8
> >Forms!frmBrows eNegsSubform.Re cordSource = 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!NameOfMai nForm!frmBrowse NegsSubform.For m

2. A subform control does not have a recordsource property! The form in the
subform control does though. Try:
Forms!NameOfMai nForm!frmBrowse NegsSubform.For m.RecordSource = MySQL
Me!MySubformCo ntrol.Requery
You can't requery a subform control either but again you can requery the form
the control holds:
Me!MySubformCon trol.Form.Reque ry
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"MLH" <CR**@NorthStat e.net> wrote in message
news:93******** *************** *********@4ax.c om... 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!
DoCmd OpenForm "frmBrowseNegsS ubform", A_DESIGN
MySQL = "blah blah blah"
Forms!frmBrows eNegsSubform.Re cordSource = MySQL
Hmmm??? What command line would save the form & the changes???
Me!MySubformCo ntrol.Requery

Nov 13 '05 #9
MLH
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.Requ ery

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.

xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxx
On Sun, 25 Jul 2004 02:17:21 GMT, "PC Datasheet" <no****@nospam. spam>
wrote:
>Forms!frmBrows eNegsSubform.Re cordSource = 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!NameOfMa inForm!frmBrows eNegsSubform.Fo rm

2. A subform control does not have a recordsource property! The form in the
subform control does though. Try:
Forms!NameOfMa inForm!frmBrows eNegsSubform.Fo rm.RecordSource = MySQL
>Me!MySubformCo ntrol.Requery

You can't requery a subform control either but again you can requery the form
the control holds:
Me!MySubformCo ntrol.Form.Requ ery


Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2404
by: Sabine Oebbecke | last post by:
Hi Experts! I have several forms and reports where controls get their values with 'Forms!MainForm!Control' or 'Forms!MainForm!Subform!Control' resp 'Forms!MainForm!Subform.Form!Control' which works without any problem in Acc97, 2000 and 2002, but now in Access 2003 there seem to be a problem with this. A user is working with my app, and he uses an Access 2002 MDE in an Access 2003 environment.
3
2468
by: Melissa | last post by:
I have forty-two small subforms on a form. I need help with the code that will return the name of the subform control when I click on any subform control. I know how to select all the subform controls at once and then enter =MyFunction in the Enter event; I need the code for MyFunction. Thanks, Melissa
10
19346
by: Sally | last post by:
When I click on a control in a subform, what is the syntax to return the name of the subform control on the main form that contains the subform? MsgBox "MySubform is in " & < ???? > Thanks, Sally
4
2194
by: MLH | last post by:
I have a subform control on a mainform. Each time I do anything experimentally with it - like even change its Width property, A97 takes the liberty of setting values in the link child field and link master field to a common field value. This is happening, I suppose, because I have purposefully left the two property settings empty. I'm just using the unbound subform control as a glorified list box. Can I prevent this undesirable...
6
3791
by: MLH | last post by:
When the vehicle entry form (frmVehicleEntryForm) first opens, the additional owner SubForm control (frmAddnlOwnrListSubForm) is enabled. You can click on it and it will accept the focus. But after you enter data to the first field on the main form (a VIN#), that is not the case. The SubForm control becomes disabled and will not accept the focus. Dunno why. There's no child/master link fields. They have no settings - at least not in design...
1
2281
by: MLH | last post by:
I have a form with a subform control on it listing records returned by a saved query named UnbilledVehicles. I would like to put command buttons on the main form to apply dynamic filters to the records displayed in the subform control. Say, for instance, to list only 2004 model cars. I'm seeking the simplest approach. The main form is frmCreateInvoice, the subform control is named frmCreateInvoiceSubFormCtl and the actual sub- form is...
3
10375
by: ApexData | last post by:
I am using the Shortcut Menu Bar property of many combobox controls to execute a single shared custom function. When the function executes, I need it to immediately identify the following items: MainFormName, SubFormControlName (if this is a subform), and the ComboBox Control Name that launched the event. The following code seems to work, but I have my doubt. I am particulary concerned about the subForm control identity. If the
1
3240
by: veteranwebdesign | last post by:
Hello, I have a main form. I want forms to open in a subform control box. What is the code for the option group to open the subforms in the control box. I didn't create subforms, I created regular forms, but will create subforms if that is needed for this to work. My main goal is when a user clicks on a menu item and comes to a main
4
2728
by: MLH | last post by:
If I have a main form - Form3 - with a subform control named SF4 and they are NOT linked on any common field (no child or master field links) - can I write code on Form3 to filter the records displayed in the subform SF4? I want to dynamically change the records displayed in the subform control at the click of a button.
0
8009
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8427
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8078
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8298
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5452
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3914
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3962
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2441
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1548
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.