How to Add Fields as Needed on Form

toasterlad

New Member
Joined
May 27, 2010
Messages
10
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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top