Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Search Facility (AC 2000)

    Background.
    I have a database which tracks Imported Shipments. There is a table with the Contract Details including data for Contract Id (Key); Status; Supplier; Vessel Name; Est Arrival Date etc. In addition, there is a Contract Line Item table (key is Contract Id + LineItemNo which gives details of the different line items on the contract, This is a 1-2-many relationship.

    Problem.
    I wish to create a 'Search' form which will enable to user to 1) select all records, OR 2) filter the results based on one of the above fields.. eg If 'Supplier' is selected, a combo box will be enabled to show a list of suppliers, and when selected, this will be used to show the contracts from this supplier in a sub form. To drill down to the Contract Line items, a double click will open up a detail form. This part of the project is not a problem. In addition, I know I can use radio buttons in a frame to enable/disable the relevant combo boxes depending on what has been selected, but I am having a problem coming to grips with the coding required to create the Filter/SQL ?? , and how I would actually attach the filter to the sub form.

    Searches through this and other forums have got me this far, but additional help is required.

    Could someone point me to an article or post which might get me further along this project's evolution. Alternatively a better approach to the one I have started.


    TIA

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

    Re: Search Facility (AC 2000)

    You could build a query based upon what the user has selected. This requires you to amend the SQL of the query.

    Another way to do this in a query is to test a table field to a selected control that the user has keyed in, and test also if the control, has been entered, eg:
    tblControlName = forms!Formname!Controlname or forms!Formname!Controlname is Null

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Search Facility (AC 2000)

    Thanks Pat

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

    Re: Search Facility (AC 2000)

    How did you solve your problem, other loungers will probably want to know.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Search Facility (AC 2000)

    Problem is not solved yet, but found an interesting solution by Helen Feddema on another forum. The link is http://www.helenfeddema.com/access.htm and the Article is Access Archon #116 'Displaying Search results in a subform' . I am still trying to digest this 8 page document, but it appears to be the solution.

Posting Permissions

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