Sorting Subform

[Guest]

Hi,

I've looked through the past questions but can't seem to take any of the
knowledge from them and apply it to my situation. I must be missing
something. I have a form with a subform that are linked on a date field in
the main form. I am having trouble in that ever time a user sorts or filters
the subform, they are forgetting that they have done that and when they
return to the form, it is not necessarily showing them the information in the
proper order or is leaving out records because of a filter. What I would like
to do is somehow write code to reset the subform's sort order and remove any
applied filters when the user opens the form or changes the date in the text
box which links the form and subform together. I have tried the OrderBy but
it's giving me grief in how I am applying it.

Any advice would be greatly appreciated...

 

[Marshall Barton]

I've looked through the past questions but can't seem to take any of the
knowledge from them and apply it to my situation. I must be missing
something. I have a form with a subform that are linked on a date field in
the main form. I am having trouble in that ever time a user sorts or filters
the subform, they are forgetting that they have done that and when they
return to the form, it is not necessarily showing them the information in the
proper order or is leaving out records because of a filter. What I would like
to do is somehow write code to reset the subform's sort order and remove any
applied filters when the user opens the form or changes the date in the text
box which links the form and subform together. I have tried the OrderBy but
it's giving me grief in how I am applying it.

The infamous Filter/Sort features strike again :-(

Use the subform control's Exit event to reset the subform's
....ON properties:

Me.subformcontrol.FORM.FilterON = False
Me.subformcontrol.FORM.OrderByON = False

FYI, my major client insisted that I create custom (or
remove) all menu/toolbars exactly for this (and more
serious) reasons.

 

[Guest]

Oh my! What a headache!

I still haven't got the exact thing I want and was trying to call the
'resetting' of the subform's sort order after I requery the subform using
me.subformcontrol.FORM.onexit but it doesn't work. Right now I have to click
into the subform and then click out of it before it executes the OnExit event
of the subform. Is this all that Access has got?

 

[Guest]

Oh no!!!
After it execute the onexit event of the subform, it will no longer requery
the subforms data. It 'flashes' as though it's doing a requery but the data
remains the same. If I open the form and change my date field it refreshes
the data perfectly until I do a resort and have the 'OnExit' event execute.
After that, when I change my date, the requery of the subform does nothing
but make it 'flash' briefly but it keeps the data the same. BUG???

 

[Marshall Barton]

Oh no!!!
After it execute the onexit event of the subform, it will no longer requery
the subforms data. It 'flashes' as though it's doing a requery but the data
remains the same. If I open the form and change my date field it refreshes
the data perfectly until I do a resort and have the 'OnExit' event execute.
After that, when I change my date, the requery of the subform does nothing
but make it 'flash' briefly but it keeps the data the same. BUG???

Weird. I've never experience that symptom, so I doubt I can
be any help here. I suggest that you look at all your other
code in the form and subform to see if there's anything that
could be interfering with the subform rebuilding its dataset
when the filter is turned off (maybe you have an excess
Requery somewhere??). It may(?) also be worthwhile clearing
the filter property too.

Me.subformcontrol.FORM.FilterON = False
Me.subformcontrol.FORM.Filter = ""
Me.subformcontrol.FORM.OrderByON = False
Me.subformcontrol.FORM.OrderBy = ""

Could all this be related to a bug? Sure, it can. Is it
defintely a bug, I don't know, the Filter/OrderBy thingies
have a lot of inconsistencies. So many that I refuse to
touch it with a ten foot pole. For more information, see:
http://members.iinet.net.au/~allenbrowne/bug-02.html

 

[Guest]

Hi Marshall,

I have tried your suggestion but same result so I changed one other thing.
In the subform, the query included a reference to the field in the main form
(that date field I mentioned before), I removed that reference and used the
link Master and Child fields on the subform object in the main form and
problem solved. A weird combination of things but it finally works! Thanks
for your advice. I really really appreciate it.