How to Add Fields as Needed on Form

toasterlad

New Member
Joined
May 27, 2010
Messages
4
I'm creating a recipe database, and have a table with 19 fields for ingredients (Ingredient1, Ingredient2, etc). However, not all recipes call for 19 ingredients, and I'd prefer not to have tons of empty fields on the form used to input the recipes. How can I add the fields as needed when the recipe calls for additional fields? Is there a way to use a command button to add fields to a form? Or would some other method work better?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
979
Office Version
2007
Platform
Windows
Hi there. you could set 18 of the 19 to not visible, and in the change event for each fields make the next one visible. So, your form would start out with 1 visible and 18 hidden, then as each ingredient is entered, the next field will appear. Combine that with making the form 'grow' by altering the height, and it should look pretty good. Make the start height of the form only show the first box (in my case it was 100 as I only had the one textbox on it).

This code shows you the idea:

Code:
Private Sub TextBox1_Change()
TextBox2.Visible = True
UserForm1.Height = 130
End Sub
Private Sub TextBox2_Change()
TextBox3.Visible = True
UserForm1.Height = 160
End Sub
and so on for the 19.
 
Last edited:

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
822
You are approaching this in the wrong way.
You should have a table tblIngredients and have a record for each ingredient and any other info you want.
Then it does not matter if it is 2 ingredients or 200.

HTH
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
979
Office Version
2007
Platform
Windows
Hi toasterlad

I misread your details, my answer was based on you using excel not access. As you are indeed using access, I fully agree with welshgasman's observation.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,448
Messages
5,486,958
Members
407,574
Latest member
Greso

This Week's Hot Topics

Top