Results 1 to 7 of 7
  1. #1
    onechriswhite is offline Novice
    Windows Vista Access 2002 (version 10.0)
    Join Date
    Jul 2010
    Posts
    22

    Filter combo box data based on a form control

    I have a form with two combo boxes, UnitIDKey and ConceptIDKey. Each ConceptIDey is linked to a UnitIDKey in that one unit contains many concepts.



    I want the data in the ConceptIDKey combo to filter based on the value already in the UnitIDKey field. This works, but I also need it to select ALL Concepts when the UnitIDKey is empty.

    This is my query on the ConceptIDKey Combo:

    SELECT TBLConcept.ConceptID, TBLConcept.ConceptCode & " - " & TBLConcept.ConceptDescription AS ConceptCodeDescription
    FROM TBLConcept
    WHERE (((TBLConcept.UnitIDKey)=[Forms]![FRMTests].[UnitIDKey]));

    But how do I make it only work when UnitIDKey has data in it, and otherwise select all records from TBLConcept?

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Should give controls a name different from fields, like cbxUnitKey.

    Try:

    WHERE UnitIDKey LIKE [cbxUnitKey] & "*"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you research "add all option to combobox" you'll get lots of leads, many of which involvel lengthy functions. The premise of dependent combo or listboxes is that child controls are not populated until a selection has been made in the parent. OK, then how do you get the child box to display all the records if the first one is always going to be a filter for the second? The answer is to add an "all" option to the first.

    A simple way would be to have "all concepts" as the first record in your table name, provided that it will always be first alphabetically. If not, you can ensure it will be by adding a numeric sort field to your table, but if there are a lot of records, or the list will change frequently, this would not be a good method. I increment sort fields by 5 so that I have room to insert something later without having to re-define the entire list for one new entry. The way to use this method follows the next method.

    Here's a short method I found that doesn't involve table mods which I adapted to your situation but have never tested. I assume your first UnitIDKey records are text data type. In design view, make the following the source for UnitIDKey and it should add "all concepts" to the control records. Again, this value has to be first, alphabetically speaking. You can drop the sql into the property sheet rowsource for UnitIDKey or make this a stored query and add the query name.

    SELECT TBLConcept.ConceptID, TBLConcept.ConceptCode & " - " & TBLConcept.ConceptDescription AS ConceptCodeDescription
    FROM TBLConcept WHERE (((TBLConcept.UnitIDKey)=[Forms]![FRMTests].[UnitIDKey]))
    UNION
    SELECT Null, '(all concepts)' FROM TBLConcept ORDER BY ConceptID; NOTE: not sure about sort; you don't show one in your example

    ConceptIDKey has no rowsource if you look at it in design view. Using the first combobox AFTER UPDATE event, you set the second control source progammatically. Use a Select Case structure or
    Code:
    If Me.UnitIDKey = "all concepts" then
    Me.ConceptIDKey.rowsource = "a sql statement here"
    else
    Me.ConceptIDKey.rowsource = "the other statement here"
    Me.ConceptIDKey.requery
    end if
    You should always back up your db or affected forms/tables etc first before trying design changes.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  5. #5
    onechriswhite is offline Novice
    Windows Vista Access 2002 (version 10.0)
    Join Date
    Jul 2010
    Posts
    22
    Thanks, that would work but I need it to filter for UnitIDKey when it is present, and show all records when it is not.

  6. #6
    onechriswhite is offline Novice
    Windows Vista Access 2002 (version 10.0)
    Join Date
    Jul 2010
    Posts
    22
    Hi, thanks for the reply. I can't make this work and I think it's because my UnitIDKey is numeric; it's a primary key, and so it won't always add tothe top in the sort order. Is there a way I can simply say, if Me.UnitIDKey is null then Me.ConceptIDKey.rowsource = "..sql.." else Me.ConceptIDKey.rowsource = ".. different sql .."
    ? It doesn't seem to like the "is null" thing, but then my SQL skills are limited to hacking around in Unify Accell many years ago!
    Thanks

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hi, thanks for the reply.
    Umm, is that directed to me?
    So is UnitIDKey an autonumber field? These make OK foreign keys (FK's) but not good primaries IMHO.
    Then again, it would not make sense to have users pick an autonumber as you seem to be saying with
    WHERE (((TBLConcept.UnitIDKey)=[Forms]![FRMTests].[UnitIDKey]));
    . Maybe the issue is that your combo has the wrong number of fields in it, or the wrong field is bound to the control.
    If it's not autonumber, then maybe you could make "All Concepts" a huge number that fits within your data type constraints and do a descending order instead.

    As for your question, the only time UnitIDKey will be null is on form opening. Once a selection is made, you will not be able to get back the "all" value that would re-populate ConceptIDKey with all of the values. You might have to post a scaled down db version so we can look at what you're dealing with. Alternately, do the search I mentioned and see if anything proves useful to your situation.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-18-2014, 10:40 AM
  2. Replies: 1
    Last Post: 12-29-2013, 11:21 AM
  3. Replies: 5
    Last Post: 01-16-2013, 11:51 AM
  4. Replies: 2
    Last Post: 04-17-2012, 12:56 PM
  5. filter form based on combo box
    By lloyddobler in forum Forms
    Replies: 8
    Last Post: 09-10-2009, 07:33 AM

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