Results 1 to 13 of 13
  1. #1
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26

    Filtering a combo box

    Hi


    I'm not really a beginner but not an expert either, I am having a problem with displaying only certain records in a combo box in a form that relates to an a record on another form

    I am trying to:

    wanting a form for ordering parts from a supplier, I Have a main form with a drop down combo box of the list of suppliers when a supplier is selected it will populate a subform with orders for that supplier, (got that bit worked out fine) there is another subform that selects items to be ordered from a parts list by selecting parts from a combo box which uses PartID Description Cost supplier as its row source from the Parts table. I want to only allow parts from the selected supplier on the Order subform to be displayed for selection. I've tried building an expression but it doesn't work I would like to uses a filter but can't seem to get my mind around the filtering method. Or is there any code I can use

    Hoping there is an answer the Parts table contains in excess of 100,000 records

  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
    Does this work for you?

    Baldy-Cascading Combos
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26
    Not really both forms are data sheets and I don't really understand how it can work between 2 subforms

  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
    Make a copy of your database and zip and attach it so people can see and experience the issue.
    Please provide instructions for getting to the problem area.
    What exactly do you want to happen?

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Combo boxes don't really have filters. What you have to do is reset the row source for the combo, and then requery it.

    The record source for the combo would be

    Select PartID, Description, Cost, supplier from tblParts WHERE ....

    The WHERE clause will specify which supplier you want to see the parts for. You are selecting the supplier in the main form combo box, so you can reset the row source for the subform combo in the After Update event of that main form supplier combo:

    SQL = "Select PartID, Description, Cost, supplier from tblParts WHERE Supplier_ID = " & me!cboSupplier
    me![PartsSubformControl].form!cboPartsSupplier.rowsource = SQL
    me![PartsSubformControl].form!cboPartsSupplier.requery

    Replace the object names in italics with the actual names of your objects.

    Note the somewhat tricky syntax to refer to the combo on the Parts subform - you do not use the name of the subform itself. Instead. you use the name of the control that contains the subform.

    Hope this helps - post back of you find it a bit confusing.

  6. #6
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26
    Thnx will try it although it is a bit like Ive been doing the main problem is I am using the nav tabs a main one then the form in the sub tab I have tried to in a query to the Parts Table to use the supplier from the main form to filter the combo it sort of works but doesn't and I cant point to the Order subform from the build process if that makes sense

  7. #7
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26

    The Forms

    Click image for larger version. 

Name:	access.png 
Views:	19 
Size:	57.0 KB 
ID:	25576this is what form looks like

  8. #8
    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
    II think John_G has given you the proper answer.
    Pay particular attention to
    use the name of the control that contains the subform.

  9. #9
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26
    Ok not really understanding the naming convention zip a small DB as an example

  10. #10
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26
    TestDB.zip
    attached zip file I hope

  11. #11
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26
    OK GOT IT SORTED JUST NEEDED THIS IN RowSource SQL OF THE SUBFORM COMBO BOX.
    WORKED OK BUT IN TRIMMING THE DATA IN TABLES SCREWED IT UP A BIT SOME SUPPLIERS HAD NO PARTS DATA

    SELECT Parts.PartID, Parts.PartName, Parts.Cost, Parts.Supplier_ID FROM Parts WHERE (((Parts.Supplier_ID)=[Forms]![WORKSHOP]![NavigationSubform].[Form]![cboSupplier]));

  12. #12
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26
    THANX FOR THE HELP TRIED TO ZIP THE FINAL dB BUT FAILED TO DO IT???? SO YOU COULD SEE DIDNT NEED A REQUERY IN AFTER UPDATE

  13. #13
    scorpio46 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    26
    TestDBRESOLVED.zip

    If anyone is interested in the resolution

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

Similar Threads

  1. Replies: 3
    Last Post: 10-02-2013, 07:28 AM
  2. Filtering with combo box
    By PoorBoy in forum Access
    Replies: 8
    Last Post: 02-18-2013, 03:22 PM
  3. Replies: 1
    Last Post: 07-12-2012, 08:39 AM
  4. Replies: 5
    Last Post: 03-12-2012, 02:58 AM
  5. Replies: 0
    Last Post: 08-17-2008, 12:19 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