Solved How to choose data from 2 different tables?

[asteropi]

So I have a Supplies table which is my non-cooking materials like bottles, jars etc
And I have an Ingredients table which is my cooking ingredients like oils, butters etc

And I have a Purchases Table where I input what purchases I make for the company. How can I set it that when I purchase an item it will be able to find data from 2 tables, both the supplies and the ingredients table? (right now it's only for the supplies)

 

[Minty]

Put the data all in one table and have a field that identifies the ProductType
e.g. Supplies/Ingredients/Third Category you haven't though of yet

 

[Gasman]

Like Utensils perhaps?

 

[asteropi]

Put the data all in one table and have a field that identifies the ProductType
e.g. Supplies/Ingredients/Third Category you haven't though of yet

That would make it very hard to find what I'm looking for when checking them on the list

 

[arnelgp]

create a Union query on your 2 tables and use it for your combobox selection.

 

[asteropi]

create a Union query on your 2 tables and use it for your combobox selection.

I found how to choose the different materials. The problem is that when it goes to store it in the purchase details it recreates inputs, like it's a new material

 

[Gasman]

That would make it very hard to find what I'm looking for when checking them on the list

Not really, as you would filter for the category, which would just be the same result as if they were in their own table?

 

[arnelgp]

it recreates inputs, like it's a new

maybe there is a code that only Look at the supply table and not on both supply/ingredient tables.

 

[asteropi]

I went with the category solution

 

[asteropi]

Is there a way to refresh the Purchase List Form if I close the Purchase Form?

 

[Minty]

If you are on the form
Me.Recordset.Requery

Will do the job.

 

[Pat Hartman]

That would make it very hard to find what I'm looking for when checking them on the list

No, it wouldn't. Change your queries in the appropriate places to look for a specific type. That will get you the exact list you now get from each single table.

 

[asteropi]

No, it wouldn't. Change your queries in the appropriate places to look for a specific type. That will get you the exact list you now get from each single table.

I was talking about a combo box in a form

 

[Pat Hartman]

I was talking about a combo box in a form

All of my combo box RowSources are in a single table of tables. Therefore every combo has a RowSource that is a query that selects the necessary data from the single table using a Where clause. That was the suggestion Minty made. Do you think Amazon keeps a separate table for each category of items they sell?

 

[Minty]

Add another column to your form or put a combo in the header (Which is purely for data entry not stored) which is Product type.

Filter the Product combo to only those items - you can get funky with it and only apply it when the combo gets focus on the detail line, so it doesn't affect the other lines display.