Question What is the best way to make an auto new control on a form? (1 Viewer)

[cwats]

I am trying to figure out the best way to make a form that automatically creates a new field when you enter info on an existing one. Or maybe there is a way where you type the quantity of ingredients and the form populates the fields. This form is going to be an ingredients list for customer products. I don't want to create set fields because there is the possibility that a new product might use more ingredients than expected.



I think a continuous form as a sub-form would be the solution, but I wanted to ask around for any ideas before getting started. Any comment or suggestion would be greatly appreciated.



Thanks,

 

[Ranman256]

you do not create new fields on a form/table. These are made in advance.
Do you mean ,create a new Value in a field?

if you do mean create new field, then you are doing it wrong...
you don't have a table with fields: recipe, salt, sugar, flour...
instead you add values:

recipe, Ingredient, parts
pie, salt, 1
pie, sugar, 5
pie, flour, 20

 

[cwats]

Ranman256,



I Guess what your are saying makes sense in MS access. I am new to MS access so some of the terminology i dont quite understand yet. I am currently using an excel program that does what i need it to but i am starting to get products with larger lists of ingredients. I cant keep adding a new line for that extra ingredient because its very time consuming. Another reason i am moving away from excel is because i cant save this product information for future orders. I have to re-enter the data every time.



Here is a quick look at the data input page in the excel program. You will see the area of 40 or so comboboxes that would be link to an ingredients list page. i am trying to figure out the best way to stream line this part of the process.



View attachment Book1.zip

 

[MajP]

This is something that is very common. If you google Access Recipe Database Download, you will get lots a free examples to look at.

 

[MajP]

I kind of lied because when I went looking a lot of the examples looked like they were made by chefs not knowledgeable database people. This one is simple and pretty good design IMO.

https://access.microsofttemplates.o...ates/recipe-collection-database-template.html

 

[The_Doc_Man]

Let me explain what you want by telling you the correct way to look at it.

You start with a recipe. You define a recipe by its title and maybe a general description. When you get to the ingredients, though, you create a CHILD TABLE and a sub-form that perhaps is in "continuous form" mode. You enter as many ingredients as are required. Could be 1. Could be 100. Every recipe can have a different number of ingredients in the child table, all linked by having a Recipe ID number in the parent table as the primary key (PK) and copying that PK value into a linking field that WE call the foreign key (FK) in the child table. Two tables. One main form and one sub-form. Access can do this for you with wizards (and examples such as provided by MajP).

I wrote this because you appeared to not know the correct nomenclature. It is hard as hell to look up something if you don't know what to call it, so now you know.

 

[MajP]

Every recipe can have a different number of ingredients in the child table, all linked by having a Recipe ID number in the parent table as the primary key (PK) and copying that PK value into a linking field that WE call the foreign key (FK) in the child table. Two tables. One main form and one sub-form.

The example I posted is a little more complicated in that it uses a many to many relationship. So a recipe can be assigned many ingredients and a single ingredient can be related to many recipes. So a third table is needed to relate a recipe from the recipe table to an ingredient in the ingredient table, allowing the many to many. This is often called a junction table.

 

[cwats]

MajP,


Thank you for linking that example DB. I was able to see the relationship structure and was able to fiddle with it to see how things worked. I will keep looking at examples like that.

 

[Uncle Gizmo]

I will keep looking at examples like that.

I note that you have come to MS Access from Excel. It is very common for Excel users to assume MS Access is a more powerful version of Excel. However this can lead to serious design issues in your database. I've blogged about these design issues on my website here:-

Excel In Access

I also provide a Tool you can download for converting tables you've imported in an Excel format into an MS Access format.

 

[cwats]

Uncle Gizmo,

Thank you very much for that. I'll definitely spend some time read your blog.