Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    May 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unwanted Filter (Access 97)

    Hi all, this is the first posting I have ever had to make as I ALWAYS manage to find the answers to my questions and problems by searching the forum (a great testament to the effectiveness of The Lounge and it's members). Unfortunately as this feature is disabled at the moment I can't easily tell if this question has been dealt with before so my apologies in advance.

    Basically my problem involves a main form, a tab control and a sub form embedded in a page (Page 1) of the tab control. A temporary table is the record source for the sub form and only 1 field is enabled for edit. My problem occurs when the cursor is placed in the "editable" field for any of the available records (let's say record 4 of 6)and the user selects another tab on the tab control (Page 2). Upon returning to Page 1, Records 1, 2 and 3 are no longer visible. No filters have been applied either through code or via the menus, yet the only way to refresh the form to show all records is to select the "Remove Filter/Sort" menu item. The underlying record source has not been touched (i.e. no records have been deleted in the process). I cannot understand why all records above the selected record are hidden from view until the "filter" is removed.

    Thanks
    GiT

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unwanted Filter (Access 97)

    Has the DataEntry property of the subform been set to Yes? That means that you are only allowed to add new records in the subform, but Remove Filter/Sort will cancel the DataEntry setting.

  3. #3
    New Lounger
    Join Date
    May 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unwanted Filter (Access 97)

    Hans,

    Thanks for the quick response! The DataEntry property is set to No as the purpose of the form is to edit a set of records in a temp table which are then appended to the "real" table. (The form is used daily to enter the prices of a number of managed funds).

    Thanks
    GiT

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unwanted Filter (Access 97)

    Strange. I don't understand how just switching to another tab and back would set a filter; you stated explicitly that you have no code that sets a filter. I don't know how to analyze this further from a distance. Perhaps you could post a stripped down version of the database so that Loungers can have a look at it.
    <UL><LI>Make a copy of the database.
    <LI>Delete all tables, queries, forms etc. that are not relevant to the problem.
    <LI>Delete most records from the tables; leave only the bare minimum needed to demonstrate the quirky behavior you described.
    <LI>Remove or modify sensitive information.
    <LI>Compact the database.
    <LI>Create a zip file containing the database - it should be less than 100 K.
    <LI>Attach the zip file to a reply. Warning: if you preview a post, the attachment box gets cleared.[/list]Maybe someone else has a bright idea that makes this unnecessary...

  5. #5
    New Lounger
    Join Date
    May 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unwanted Filter (Access 97)

    Hans,

    Thanks again for your speedy response. I have some further information which is hopefully not going to brand me a liar :-) Now keep in mind that when I said I wasn't explicitly setting a filter, I meant I wasn't setting the Filter or FilterOn properties of the sub form or main form. Now, when "Page 1" is selected the tab control's Change event is invoked and the following code is executed:

    DoCmd.RunSQL "DELETE * FROM <temp table>"
    DoCmd.OpenQuery "qapptmpTable"

    Me.<sub form>.Requery

    Does this constitute setting a filter? I wouldn't have thought so (although my interpretation of setting a filter may be naive), and the fact that this code is executed each time the tab gets the focus led me to think that the underlying record source would be updated and the form refreshed.

    Again, I'm sorry if this added information changes the nature of this problem.

    Regards,
    GiT

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unwanted Filter (Access 97)

    I don't see how this code would set a filter either. I'm afraid I can't offer more insights <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Unwanted Filter (Access 97)

    Do you mean that the records on the sub form have disappeared? Is it because that you are moving to a different Main form record?

  8. #8
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unwanted Filter (Access 97)

    This may be a really dumb question, but it's not just that the subform view has scrolled upwards so the selected record is now the top one, is it?

    Simon

  9. #9
    New Lounger
    Join Date
    May 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unwanted Filter (Access 97)

    Hi all,

    Well whilst I've managed to work around this problem, I still for the life of me can't work out why a filter is being set.

    Hans, thanks for all of your help.
    Patt, not all of the records were disappearing, only those above the currently selected record.
    SimonC, Unfortunately no. There were only 6 records to start with, not enough to invoke the vertical scroll bar.

    Anyway, the solution has been to replace the Me.<subform>.Requery command with a command to reload the entire subform:
    Me.<subform>.SourceObject = <subform name>.

    Thanks again
    GiT

  10. #10
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unwanted Filter (Access 97)

    Did you try Me.<subform>.Form.Requery rather than just Me.<subform>.Requery?
    Charlotte

  11. #11
    New Lounger
    Join Date
    May 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unwanted Filter (Access 97)

    Just tried it then and it worked!!

    That's great. I don't suppose you have any clue as to why it happens in the first place? Just so I can get closure ... :-)

    GiT

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unwanted Filter (Access 97)

    There appears to be a different between requerying the subform control and actually requerying the underlying subform in 97. I suspect it has to do with the difference between other controls, which actually exist on the parent form, and the subform control, which is merely a window into another form. If you want a subform to behave as expected, it's usually best to requery the underlying form, not just the control, and it isn't as time consuming as resetting the sourceobject.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •