473,326 Members | 2,126 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Sorting a SubForm


I have a relatively simple relational database. One of my forms is based
on a query with a 3 parameter sort: Status / Priority / Customer.

When I open the form, the records are sorted properly.

When I go to filter the form, to get a subset of the form, e.g. filter
by Responsible Party, I would like the form to sort this way:

Reposponsible Party / Status / Priority / Customer.

However, when I do the Filter by Form, the sort gets all screwed up, so
much so that I can't even determine that it is sorted in ANY order - it
appears random.

Virtually all my forms are doing the same thing - when I filter the
form, it loses its sort.

Help!

thanks,
carl

*** Sent via Developersdex http://www.developersdex.com ***
Dec 28 '05 #1
1 4900
There are timing issues if the form or subform's RecordSource is a query
that uses parameters that read values from the form or subform itself. Then
if you apply a filter to the form and the subform, there are bugs in Access
where it gets confused about which has a filter applied. If you try to sort
it out by setting the OrderBy and OrderByOn properties, there are further
bugs where Access gets so confused that the entire approach is unworkable.

If you want to seedetails of the bugs and a sample database to demonstrate
them, bring up this article:
Incorrect filtering
at:
http://allenbrowne.com/bug-02.html

The simplest way around the entire problem is to programmatically set the
RecordSource of your subform instead of relying on the Filter and OrderBy
properties. The RecordSource can handle both the filtering (the WHERE clause
of the query statement), and sorting (the ORDER BY clause.) Typically, you
would set this in the AfterUpdate event of some control and/or the Current
event of the form or subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"aintnorock" <no****@xyz.com> wrote in message
news:gC***************@news.uswest.net...

I have a relatively simple relational database. One of my forms is based
on a query with a 3 parameter sort: Status / Priority / Customer.

When I open the form, the records are sorted properly.

When I go to filter the form, to get a subset of the form, e.g. filter
by Responsible Party, I would like the form to sort this way:

Reposponsible Party / Status / Priority / Customer.

However, when I do the Filter by Form, the sort gets all screwed up, so
much so that I can't even determine that it is sorted in ANY order - it
appears random.

Virtually all my forms are doing the same thing - when I filter the
form, it loses its sort.

Help!

thanks,
carl

Dec 28 '05 #2

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

Similar topics

15
by: Rey | last post by:
Howdy all. Appreciate your help with several problems I'm having: I'm trying to determine if the Visit subform (subformVisits) has a new record or been changed, i.e. dirty. The form that...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
4
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the...
18
by: Scott | last post by:
I have a collection where the items in the collection are dates. I want to iterate over the collection and build a value list string for the rowsource of a listbox. The dates in the collection are...
6
by: DMUM via AccessMonster.com | last post by:
Hello I am trying to pass the name of my subform to a function/sub but I can't seem to get it to work. I am using an autokey function (ctrl E) to unlock text boxes on a subform. I have a few...
2
by: John | last post by:
I have a mainform with a subform. The subform is sorted on a specific field and when I just open the subform, the sorting is OK. However, when I open the mainform, the sorting in the subform is...
1
by: danisk | last post by:
My database has a main form with a subform with several sub-subforms. When I sort data in my sub-subforms, the sort will save while viewing only the subform and sub subform but not when linked to...
25
by: hjozinovic | last post by:
Hi! I have a MainForm and a SubForm on it. They're linked and can be filtered, sorted etc... I designed MainReport and SubReport that should match filter and sort criteria from MainForm and...
1
by: pyotr | last post by:
Hello,,,, I have a form with its subform that is filtered through the combination of two independent comboboxes to facilitate administration. However, I want to arrange/sort the viewed...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.