Creating a form for records in a 1-n link table in Base

[Vyruz64]

I’m trying to create a form in Base, but I’m having some struggles.

I know how to create basic forms for tables and how to make a form for multiple tables.

But I’m having no luck creating a form following the following requirements:

I have 3 tables; Recipes, Ingredients and a link table Recipe_Ingredients.

The link table is 1-n, meaning a recipe can have multiple ingredients.

The link table also has a field (int) to specify the amount of the ingredient.

The form should allow the user to pick a recipe from a list box, show all the ingredients from the recipe plus their amount AND the user should be able to add/remove/adjust the ingredients.

I’ve been messing around all day trying to get this to work, but no amount of searching has lead me to any instructions for my situation.

I hope someone can point me in the right direction.

[charlie.it]

I think it would be easier to answer by having a sample of your database files

[frofa]

Given you are OK with the schema, here is a sketch of the form setup:

1. setup an additional filter table to store the ID (integer key) from the user listbox choice of recipe.

2. the listbox for user recipe-selection will be in the main form.

3. use a linked subform to display all your recipe information in a list (data grid). Note: the contents of the subform data will be determined by the value of the recipe ID stored in the filter table.

4. the datasource for the recipe information in the sub-form will probably need to be a query joining info from the ingredients and recipe-ingredients table (this query would need to include all the primary keys to be writable through the form).

See this background article which discusses the general approach in more depth with some examples.

[Vyruz64]

I already figured that part out. As I said I have a link table Recipe_Ingredients. The question is how I can create a form that I can use to insert/delete/update records in the Recipe_Ingredients table. And with that I mean a form where I can select a recipe and then view/add/adjust the ingredients.

[peterwt]

Recipes.odbThere are various ways you can achieve this result. Most involve the use of a Macro but I have attached a database that does not require a Macro.

It uses a Table Filter that stores the Recipe selected from a List Box populated from the Recipes Table and then filters the Recipes to show in the Subform the ingredients for that Recipe. You can modify the quantity of an Ingredient, delete an Ingredient, or add an Ingredient. When adding an ingredient it has to be one that is already in the Ingredients Table. To add a new Recipe add it to the Recipes Table then you can add the ingredients through Recipes Form. Clicking on the IDIngredient column in the Subform will open a List Box showing all the Ingredients so it can be selected by name.