I am completely new to Access, but I've done the tutorials, and have some VBA experience with Excel, so I shouldn't be hopeless.
I haven't progressed to forms yet, right now I'm focusing on getting the tables defined and populated, and relationships defined.
I've changed the actual tables in the example here, but the context and problem hold.
Table 1 is Food. Other than the autofield key, it has two fields
FoodCat FoodType
Fruit banana
Fruit apple
Fruit pear
Meat lamb
Meat beef
Meet Chicken
Vegetable Broccoli
etc....
This table is used by another table, in a lookup and relationship setup.
It is easy to set it up so the other table has a field that is restricted to entries with the items in the FoodCat Field (although it is repetitive). I can also restrict the next field to entries with the FoodType Field, but this gives me all the choices.
What I'd like to do, in the new table, is restrict the FoodType choice to the entries that have a matching FoodCat choice.
So, if the item selected on the new table under FoodCat is Meat, I only want the FoodType choices to be lamb, beef, or chicken.
Is this even possible within the table? Do I need to use Forms to get this done?
Answering to your comment:
When you use "Lookup" fields" you are forcing a new relation between the tables.
The best way to avoid the problems listed in the Ten Commandments of Access (specifically: The evils of the lookup fields in tables ) is to create the relations manually, and then use them to create the adequate "lookup" control in a form or report.
Let's say you have the following tables:
tblCategories
id | catName
---+-----------
1 | Fruit
2 | Meat
3 | Vegetable
tblFood
id | idCat | foodName
---+-------+-------------
1 | 1 | Apple
2 | 1 | Banana
3 | 2 | Lamb
4 | 2 | Beef
5 | 3 | Broccoli
In this example is obvious that there's a one to many relation between these two tables, and that the idCat field in tblFood is a foreign key. You can create this relation by hand in the "Relations" window.
With the following query you can get both the foodName and catName fields properly (a good thing to build reports):
select f.*, c.catName
from tblFood as f
inner join tblCategories as c on f.idCat = c.id;
This would throw something like this:
id | idCat | foodName | catName
---+-------+----------+------------
1 | 1 | Apple | Fruit
2 | 1 | Banana | Fruit
3 | 2 | Lamb | Meat
4 | 2 | Beef | Meat
5 | 3 | Broccoli | Vegetable
which is a nice and useful query that you can use in a report... without the need of using Combo Boxes in the report.
So: How on earth to build a control, for example, a ComboBox, that filters only the foodName values depending on a catName value?
First: Create the form
Then: Create a Combo Box and put this in the RowSource property:
select id, catName from tblCategories
Set the control to have two columns, and column widths: 0, 10. This will make the id column in the drop-down list "invisible". Be sure to know the name of this combo box (let's say it is named comboBox01).
Now, create a second combo box, keep the RowSource property empty, and set the column widths to 0, 10. Also be sure to know the name of this second combo box (let's say it is named comboBox02).
And now let's make VBA do what you need: On the properties sheet of the comboBox01 control, look for the afterUpdate event, clic on the ellipsis button, select "VBA code" and write the following
Sub comboBox01_afterUpdate() ' This is added automatically by the VBA editor
strSQL = "select id, foodName from tblFood where idCat=" & comboBox01.Value
comboBox02.rowSource = strSQL
comboBox02.Requery
End Sub ' This is added automatically by the VBA editor
"Lookup controls" (no "lookup fields") are very useful when used in forms. They can save a lot of headaches to the users of your database (you included). But I strongly recommend you to avoid creating them directly in the tables.
Hope this helps.
Yes, it is possible to do within the table. However, it's easier to do through a form and it's more practical to do through a form. You NEVER, EVER want your user interacting directly with a table.
