Glitch in filtering a subform

[Edward G]

Could someone please verify this problem and/or propose a remedy?
Let Access 97 create an Order Entry Database with a Wizard. (I haven't seen
newer versions of Access, but my guess is they aren't much changed in this
regard).
Select the check box for allowing sample data and just keep hitting next
till the database opens.
Create a new form in design view based on the Customers table.
Use the Subform tool to insert a pre-existing subform "Orders by Customer
Subform". Accept the default SQL statement for which records to show.
Next use the Toolbox to add a List box. In the list box wizard you want the
list box to find records on your form, this is choice 3.
Select Company Name as the field to show in your list box. Click Next till
you finish.

Now, open the form and select Rattlesnake Canyon Grocery from the list box.
The records in the subform will be numbered 11 thru 15.
Highlight any date on the subform and hit the Filter by Selection button.
There will only be one record showing now.
Click the remove filter button next. You will have five records showing
again but the records on the subform are now numbered 1 thru 5 and actually
belong to Let's Stop and Shop. But Rattlesnake Canyon Grocery is still
selected on your List Box.
When I remove the filter I would like the records showing in my subform to
be the correct ones (11-15) not (1-5). How might one accomplish this?
Thanks for your indulgence.

 

[Allen Browne]

Hi Edward.

There are *many* bugs associated with filtering a form and a subform. As you
guessed, they are not fixed in the later versions.

Perhaps you would like to contact Microsoft and tell them what you think of
them not only leaving these bugs unfixed for 9 years (introduced in A95),
but continuing to release new versions with the same bugs. They won't take
any notice unless enough people contact them so they understand the business
impact of their issues.

More info on some of these bugs in:
Incorrect filtering of forms and reports
at:
http://members.iinet.net.au/~allenbrowne/bug-02.html

 

[Edward G]

Allen,

You mean there isn't some snippet of VBA I can insert somewhere to fix
this????

Ed

 

[Allen Browne]

You coud code aound it by avoiding filters.

Provide an interface that sets RecordSource property of the main form or the
subform to a SQL statement string that returns only the desired records.
When you do that, you may find that Access automatically sets the
LinkMasterFields/LinkChildFields property of the subform control to whatever
it likes, so you may also need to clear or set these properties again also.

 

[Edward G]

I was thinking along the lines of some code that would simulate a mouse
click of the list box when the filter is removed from the
subform. I have just visited Dev Ashish's website (via yours) and find I am
already a violator of the 10 Commandments of Access. So, even if code such
as I have described is considered bad form, it is already too late for me to
follow the path of righteousness.

Edward J the unholy - user of Lookup Fields, Send Keys and Unsplit databases

 

[Allen Browne]

Hi Edward. Thank you for your confession.

I think you are talking about:
http://www.mvps.org/access/tencommandments.htm

Hopefully you understood that we all hate the problem with the Lookup field
is NOT about using lookup tables, that the problem with SendKeys is not a
law but a recommendation, and the unsplit databases are not necessarily a
problem if you are the only person using the database.

It does help to laugh.

 

[Edward G]

Allen,
Is it possible to simulate a mouse click of the list box when the filter is
removed from the subform?
Or is the suffering supposed to be endless?

Edward J the unholy- smiling user of Lookup Fields, Send Keys and Unsplit
databases

 

[Allen Browne]

By "simulate a mouse click", do you mean set the value of the list box:
Me.Parent!MyListbox = 999

Or do you mean call its Click event procecure:
Call Form_Form1.Listbox_Click