Results 1 to 10 of 10
  1. #1
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194

    how to filter multiple tables by combo box without master/link method.


    I'd like to know if there is a way to have 4 tables on a form via subforms and filter all tables for one value in a search field? I've been doing this via child/master links to a combo box, but after much deliberation, I have resolved to change the datatype of all tables involved to lookups for field 'raw material'. This is because, on other forms, users rely on the lookup to find the value they need to enter.

  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 wouldn't use a lookup field, but you should be able to manually filter the form and subforms by setting either the Filter or RecordSource properties from the after update event of the combo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Hi Paul,
    Thanks for answering. I'm not sure how to do what you suggested. Do you have an alternative to using a lookup field? My users need the information to present as they type in a value on another form. On the other form, they have a subform that is one of the tables in question. I would make a form for them to fill in but they make an average of 10-30 records each time they use the form.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I have resolved to change the datatype of all tables involved to lookups for field 'raw material'.
    If you are thinking of making table field into lookup type, I advise you don't do that. Lookup fields present all sorts of difficulties if you try to use them in query WHERE clauses (among other things). You can use a normal table as a lookup source, and keep the FK (index) values in your other tables. You can do this with little or no change to what the users see on the forms - combo boxes (if that's what you use) can get their values from the lookup table.

    See this link for a more detailed explzanation: http://access.mvps.org/Access/lookupfields.htm

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    To set the record source, presuming a numeric data type:

    Me.RecordSource = "SELECT * FROM TableName WHERE FieldName = " & Me.ComboName

    For text:

    Me.RecordSource = "SELECT * FROM TableName WHERE FieldName = '" & Me.ComboName & "'"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    I tried setting the following because I'm not great at writing SQL. I used the query builder. It doesn't work though. I don't think it lines up with what you say, but again, I used query builder.

    SELECT qry_Percentages.*, qry_Percentages.[Raw Material] FROM qry_Percentages WHERE (((qry_Percentages.[Raw Material])=[me].[cbofilter]));

  7. #7
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    SELECT qry_Percentages.*, qry_Percentages.[Raw Material] FROM qry_Percentages WHERE (((qry_Percentages.[Raw Material])=[me].[cbofilter]));
    Where are you using that SQL? Is it the source of a combo box, in which case qry_Percentages.* surely isn't needed?
    What values are you intending to display in cboFilter, and how are you using the combo?

  8. #8
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    This combo sits atop a form where 4 tables reside as subforms. I tried to test your SQL on table 1 (qry_percentages). I opened the subform in design mode and went to record source. From there I used query builder as I'm not good at SQL. The combo is linked to tbl_rawmaterial where all ingredients lie as a list. The ingredients occur multiple times on each table depending on the recipe. If it's a coating ingredient, it's on table 2, if it's a batch ingredient, it's on table 3, etc... I'd like for the user to be able to select an ingredient and see where it's used.

  9. #9
    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'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    That's not good. Does that mean it isn't possible to filter 4 tables simultaneously with a combo? I don't want to use this data type but on another form there is a subform that is a table where users input recipes. They need to see the ingredients to know which one.................. *lightbulb* I could create a split form (to show the recipe as it stands) and form fields with a combo box for the ingredients and a button to add each record in. Would that work John?

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

Similar Threads

  1. Replies: 11
    Last Post: 01-13-2017, 06:58 PM
  2. Replies: 0
    Last Post: 08-02-2016, 02:21 AM
  3. Replies: 1
    Last Post: 08-19-2013, 10:59 AM
  4. combining multiple tables in to one master
    By joebox8 in forum Queries
    Replies: 5
    Last Post: 06-23-2011, 06:18 AM
  5. Multiple tables served by one link
    By htchandler in forum Import/Export Data
    Replies: 3
    Last Post: 01-13-2011, 01:49 AM

Tags for this Thread

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