Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter-by-Form in a subform (Access 2000)

    I have a main form with invoice info ( Invoice #, Date of Purchase, Company Name) and a subform with product info (model, serial #, ...). I want to be able to filter -by-form on any field, either in the main form or the subform, and have it work like it does when there isn't any subform. Can this be done without getting too complex, or is there a better way to accomplish filtering with subforms?

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

    Re: Filter-by-Form in a subform (Access 2000)

    What kind of behavior would you like if you filter on a field in the subform? Do you want to show only matching records in the subform, or do you want to restrict the main form to records that have sub-records that match the criteria, or something else?

  3. #3
    New Lounger
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter-by-Form in a subform (Access 2000)

    I would like to restrict the main form to to records that have sub-records that match the criteria when I apply a filter to a field in the subform. This way I see the data in the main form and subform.

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

    Re: Filter-by-Form in a subform (Access 2000)

    I have attached some code written by Tim Curran for Access 95. It works - more or less - in more recent versions too, though not without problems, in particular if you try to filter on a lookup field.

    The code is meant to go into the On Apply Filter event of the subform.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Nov 2002
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter-by-Form in a subform (Access 2000)

    Most of the fields in my subform are lookup fields. Is there a way to get the find/replace button to work in the subform. It works on the Main form but the find/replace button only finds subform records on the current main form record, not all main form records.

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

    Re: Filter-by-Form in a subform (Access 2000)

    There is no way (as far as I know) to make the built-in Find/Replace search both the main form and subform. You can create your own search option, but if you want to make it flexible, it'll be complicated. I have only tried to provide a "global" search option on one or two fields in a subform in my databases, not a complete solution. You'll need two recordsets. The first must be based on a query/SQL statement that joins the record source of the main form to the record source of the subform. Search for the value provided by the user in this recordset. Retrieve the value of a key field (or fields) from the record source of the main form from the record you found, and use this value to search the RecordsetClone of the main form. Clear as mud?

Posting Permissions

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