Aultofill Multiple entries on a subform based on another table

[ratercero]

Hello,




I'm creation a recipies management file, The recipies are build upon small recipies put together so this is what I have:

• Main Table "Indice Formulas" contains general information of recipies and should be used to select small recipies
• Standard Recipies Table "Tratamientos STD" should be used to pull the products and quantities of the small recipies
• Table "Cantidades" with products and quantities that each main formula uses ( all small recipies for each main recipie"

I have set up a form with the main table and a subform of "cantidades", and linked them toguether, but i do not know how to pull the information from one table to the other.

attached my tables and relationships.

Thanks for the help.

[Bulzie]

Not entirely sure what the issue is but on your subform, look in properties for LinkMaster and LinkChild and put in the field Cordigo de Formula in both places. This will pull only data in the subform that matches that record in the main form.

Few tips, remove all spaces and special characters from your fields and tables. If you need a space, use the underline character instead. Easier in coding and such. Also use naming conventions on objects, prefix Tables with tbl, Queries and qry and Forms with "frm" so example frmIndiceFormulas

[ratercero]

Hi bulzie,

Thanks on the feedback on naming conventions already changed it, there are a few issues:

First of all, I'm a newbie on access ( this is the main issue)

The second one is I’m probably not that good at explaining myself, however I'll try again:


My recipes have a combination of standard treatments or "small recipes". I want to be able to create a recipe and select the small recipes each that will be used with a combo box and store them in linked table.

Treatments or small recipes have multiple products each with a set up amount.


I'm pretty sure this can be done using table relationships and setting up properly forms/subforms. This is the part I need help with.


Do you have a specific question so I can elaborate more?

thanks

[ratercero]

Adding more information:

This data is currently on excel and Im trying to migrate everything to Access for obvious reasons of data managment.

The cells with the grey backgroung are the diretent treatments or small recipies I'm talking about. and the information of the top (white backgrount) is the main recipie.


Here is how Excel looks like:



Best Regards

[aytee111]

Small recipes table:
SR_ID, SR_Name, descrude(?)

Products table:
PR_ID, product name

Small recipes products table:
SRI_ID, SR_ID, PR_ID, percent

Main recipes table:
MR_ID, fields for main recipe

Main recipes to Small recipes table:
MRSR_ID, MR_ID, SR_ID

[aytee111]

Main form for main recipes:
- select main recipe from combobox
- subform showing MRSR table data linked with MR_ID

Main form for small recipes:
- select small recipe from combobox
- subform showing SRI table data linked with SR_ID

You could have these on one form using a tab control for ease of visibility if you wish.

[ratercero]

aytee111,

Is this the way it should look?

[aytee111]

Looks good. Add the additional fields and change the names as you desire.

In a sentence: each Main recipes has many Subrecipes; each Subrecipe has many Products.

There is a field called "tratamiento" which may break this structure, however. It looks like you may have to add an additional table between subrecipes and products.

[ratercero]

aytee111 ,

I Added the aditional fields, and the TBL_MAIN_RCP relation works with TBL_MRSR ( if I change Main recipe, MRSR also changes), however TBL_SR_MR remains the same in the form.

it seams somthing is wrong with the relashionships do you have any tougths?

Thanks

[aytee111]

however TBL_SR_MR remains the same in the form.

This table is not in the relationship diagram and I don't follow where it belongs on the form.

The TBL_SR_PR_subform should not be linked in any way to the main form.

This is the way I would see it:
- unbound combobox on main form to select recipe - once a selection is made, filter the form on that MR_ID (don't show ID's anywhere, they are meaningless to the user)
- a subform which is bound to TBL_MR_SR (or query) is linked to the MR_ID and displays all small recipes belonging to that main recipe
- another subform for TBL_SR_PR can be optionally linked to TBL_MR_SR subform, such as click on a small recipe and it's products will be displayed, or else it can be independent and have its own combobox selection for small recipe.

My lack of Spanish isn't helping me either!

[ratercero]

Thank you, Aytee11 this worked great!

I also found important to review how the index are defined in the tables, they were wrong and were causing a mess.

[aytee111]

Felicitaciones!