HOW to filter Combo BOX

[Glassjaw818]

Hi guys,


can you teach me on how to:

if I select a Supplier/vendor on a combobox, it will only select the item (combobox) that the supplier is selling.

example here are the given suplier:
-Supplier A
-Supplier B
-Supplier C
-Supplier D


Here are the items for sale:
Apple
Orange
Banana
Watermelon
Grapes
Pineapple
Potato
Cabbage
Lettuce
Carrots


How can i assign if SUPPLIER A only sells Apple, Orange, Banana

SUPPLIER B only sells Cabbage, Lettuce, Carrots

SUPPLIER C only sells Carrots

SUPPLIER D only sells Watermelon, Grapes, Pineapple, Potato

please see attached image for reference

hxxps://i.imgur.com/hlliUJ4.png





Thanks in advance!

 

[June7]

This is called cascading (or dependent) combobox. It is a common topic. For a start, you need a table that associates suppliers with items.

 

[Ranman256]

the query for the items, cboItems, looks at the supplier.
select [ITEM] from tInventory where [supplier] = forms!myForm!cboSupplier

but remember to refresh the combo after the user picks a supplier:

sub cboSupplier_afterupdate()
   cboItems.requery
end sub

 

[Glassjaw818]

This is called cascading (or dependent) combobox. It is a common topic. For a start, you need a table that associates suppliers with items.

Yup.. thanks for the term cascading, can you open my sample ms access data and experiment and fix on how to do it? big thanks!

 

[June7]

Have you attempted the structure and code? If you haven't, I am not going to download. I don't want to do your work for you.

 

[bob fitz]

Glassjaw818

Take a look at the attached db. It should help you to understand how cascading combo and list boxes work. You will also see that there is now a table called "tblSuppItems" which is a "Junction table" which is used to represent a many to many relationship like the relationship you have here between Suppliers and Items. I suggest you research junction tables.

You will also see that I have changed the names of tables and forms so that they do NOT have any spaces in them.
I have also changed the names of some field names to remove spaces.

 

[Glassjaw818]

Glassjaw818

Take a look at the attached db. It should help you to understand how cascading combo and list boxes work. You will also see that there is now a table called "tblSuppItems" which is a "Junction table" which is used to represent a many to many relationship like the relationship you have here between Suppliers and Items. I suggest you research junction tables.

You will also see that I have changed the names of tables and forms so that they do NOT have any spaces in them.
I have also changed the names of some field names to remove spaces.

Hi Bob fitz, Thanks for editing my attached db. Yes, I will thoroughly review examine this! and for the junction table! YES! I will research for this! Thanks alot! See you again!