Results 1 to 4 of 4
  1. #1
    tagteam is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    486

    Filter form by criteria not available in form

    I track changes to a car and those changes are given a construction number. For example, when a car is built it may have a number like LT67334. If any significant changes are made to the car it will be given a new number like Lrr8644.

    I have a form that tracks a bunch of information about each car and each car can have one or more construction numbers. So to solve this I created another table that will hold the additional construction numbers and only show the current number. However, my team needs to sometimes filter the form to find any cars that have ever used a particular number, not just the current number.

    Is there any way to make this work in a regular filter by form type scenario. Basically I would like to be able to filter the form to show any car that has ever used that construction number even though the number could be in the current form field OR it could be in a separate table.



    thanks for any help

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I suppose you could create a union query that pulled the tables together and base a form or subform on that.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Consider setup:
    tblCars: CarID, ...
    tblCarBuilds: CarBuildID, CarID, RegisteringDate, ConstructionNumber, ..., IsCurrent.

    You have a form for car general info based on tblCars, and in it a subform based on tblCarBuilds. Subform may be continuous to display all builds history, or single with combo to select the build. with single form, by default the current build is displayed (the car builds form ordered by RegisteringDate DESCENDING).

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    tagteam,
    What exactly is the purpose of Car number?
    Along the line that Arvi suggested, I would start with 2 tables in a 1:Many relationship:
    tblCar with a PK (meaningful to the database)
    tblCarModification with a PK that uniquely identifies a modification and FK to the CarID

    Each table would have a meaningful Date field -- BuildDate/ModDate
    Good luck

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 07-09-2015, 08:27 AM
  2. Filter Query Criteria via Textbox in a form
    By r32chaos in forum Queries
    Replies: 3
    Last Post: 08-19-2014, 10:03 PM
  3. Replies: 3
    Last Post: 04-09-2014, 09:43 AM
  4. Multiple Form Filter Criteria vba code
    By Moonman in forum Programming
    Replies: 6
    Last Post: 11-16-2013, 12:42 AM
  5. Replies: 9
    Last Post: 06-12-2012, 10:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums