Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Remove Filter causes Access to lose Record (2003)

    Hi y'all

    I've created a DB that contains Companies, Branches, Finance Details, etc. From the Main Company Form, a user can select to see the Finance details for a particular company. The Finance details can obviously be for the 2003/2004 Financial Year, the 2002/2003 Financial year and so-on. Naturally, users may want to Filter this data to make it easier to read, eg, only show Invoices for the 2003/2004 Financial Year. When they've finished, they would (naturally) remove the Filter should they wish to view another year's information.

    However, when you click the Toggle Filter/Remove Filter button, Access removes the Filter for the Current Company too.

    For example . . .
    I Open the Coca Cola company form; I then click the Finance CMD to open the Financial Details for Coca Cola.
    I want to see just invoices sent this year, so I click in a 2003/2004 field and click the Filter icon.
    Now I click the Remove Filter/Toggle Filter icon, but instead of all the Financial Details for Coca Cola now being visible, Access jumps back to the First Record in the Recordset, e.g., AAA Road Services.

    Is it possible to remove a filter for a form without it removing the Filter that was applied to open the Record that equaled the Original Form's Record ID?

    Thanks in advance for your help and advice.

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

    Re: Remove Filter causes Access to lose Record (2003)

    A form has but a single filter, not a hierarchical set of filters. So when you open a form with a Where-condition to display the records for a specific company, "the" filter is set. When the user selects a fiscal year, "the" filter is modified. If the user removes the filter, there is no filter to return to. So the behavior you describe is to be expected.

    To work around this, you could set the Record Source of the form to a query or SQL expression that selects the records for a specific company. For example, you could have a query like this:

    SELECT * FROM [Finance Details] WHERE [CompanyID] = [Forms]![Main Company Form]![CompanyID]

    (substitute the appropriate names). Set the record source of the Finance Details form to this query, and remove the Where-condition from the DoCmd.OpenForm instruction in the On Click procedure for the command button that opens the details form.

    When the details form is opened, it is not filtered, because the selection on company is made in the record source. The user can apply and remove a filter for fiscal year.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Remove Filter causes Access to lose Record (2003)

    Hans

    As usual, you are my hero!

    I probably should have thought of that myself, but I am just so brain dead at the moment, I can hardly remember my name. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

    As always, thanks again.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Remove Filter causes Access to lose Record (2003)

    Question.

    Your reply got me thinking about something else . . .

    Do you know whether there's a difference b/w Select * and a Full Select Query?

    I.E. Is

    SELECT * FROM tbl_company_details WHERE (((tbl_company_details.active)=True));

    Faster than

    SELECT tbl_company_details.company_id, tbl_company_details.screen_name, tbl_company_details.name, tbl_company_details.account_manager, tbl_company_details.parent, tbl_company_details.locations, tbl_company_details.no_locations, tbl_company_details.bitc_member_company, tbl_company_details.bitc_member, tbl_company_details.joining_date, tbl_company_details.resigned_date, tbl_company_details.opp_now_member, tbl_company_details.opp_now_join_date, tbl_company_details.no_employees, tbl_company_details.sector, tbl_company_details.charity_budget, tbl_company_details.facilities, tbl_company_details.products_services, tbl_company_details.notes, tbl_company_details.top100, tbl_company_details.active, tbl_company_details.website
    FROM tbl_company_details
    WHERE (((tbl_company_details.active)=True));


    Just wondering whether this would speed up my DB?

    Thanks in advance for your reply. [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Remove Filter causes Access to lose Record (2003)

    Whether you will really notice the difference depends on the size of the table and the speed of your machine, but yes, * should be faster than enumerating all fields. Here are the possibilities, in decreasing order of efficiency/speed:
    - If possible, specify a limited number of fields, as in SELECT Field1, Field2 FROM Table
    - Use * as in SELECT * FROM Table
    - Specify all fields explicitly, as in SELECT Field1, Field2, ..., FieldN FROM Table

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

    Re: Remove Filter causes Access to lose Record (2003)

    The fastest queries are the ones that select just the fields you need. If you need all the fields, you aren't going to see a noticeable difference in speed between specifying the fields and using the asterisk, although you can't use the asterisk in some kinds of queries, like Totals queries.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Remove Filter causes Access to lose Record (2003)

    Thank you both for your replies. In the instance above, all fields are selected from the Table, so I could just use the *. It may not speed it up greatly, then, but anything that makes the DB faster is good!

    Thanks again. [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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