How to Add Fields as Needed on Form

[toasterlad]

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?

 

[jmacleary]

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:

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.

 

[welshgasman]

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]

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.