Input in Form Used in Query

cterjesen

Board Regular
Joined
Jun 23, 2003
Messages
119
I want the user of a database that I'm creating to be able to input a number in a form (text box?). I want this number to be saved on the form until the user changes it (so when they close or open the database, their number is still there). I then want to be able to grab this number they input in a number of queries where it will be used for calculations.

Can someone help?

For example's purposes, let's say the text box is named "box1" (if that is the way to do it).

Thanks so much!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
You can accomplish this by attaching your form to a table, so this value is stored in a one record table. Be sure to disable the "Add" buttons on the form, so they can only change the one record in the table and not add new records.

You can then use/reference this field in this table for whatever purpose you need.
 

cterjesen

Board Regular
Joined
Jun 23, 2003
Messages
119
Thank you so much for responding! I'm struggling a bit.

I aplogize for being a bit of a novice.

Can you tell me how to attach the form to a table? I have three or four different user inputs that I would like to have on the form and then have saved in the table.

Also, every time I close the form or the database and open it back up, I would like the value in the table to appear in the text box and be saved until the user decides to change it.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
The easiest way is to first create a new table. Let's say you have three user inputs. Then in your table, create three fields, something like:

UserInput1
UserInput2
UserInput3

Save your table. Then from this table, select the AutoForm button, which should automatically create a form with these three fields on it. You can then edit the Form to your liking.

On the Form, go to Design View and view the Properties. On the Data tab, change the values for Allow Additions and Allow Deletions to "No". This will prevent any new records from being entered through the form.

So then your table will only have one record, so your form will always open to this record and any changes will only change the current record and not create any new records.

Essentially, what you have done is created a table to store these values that you want.
 

cterjesen

Board Regular
Joined
Jun 23, 2003
Messages
119

ADVERTISEMENT

This works perfectly!

This is going to help with so many things.

Thanks so much!
 

cterjesen

Board Regular
Joined
Jun 23, 2003
Messages
119
If I tab too many times in the form, the text boxes go to zero. I think it is because it is going to the second line in the table that the form is linked to. If I arrow back up, I get to the original input items.

How can I get it to not go to this second line of the table. I've already set it up to not allow new entries, but there is always one line below with nothing in it.

Thanks.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,159
Office Version
  1. 365
Platform
  1. Windows
Sorry for the delay, but I just saw your reply today. I tried to recreate your situation, but was unable to do so. If I tabe through four times, it just goes back to my first field (and doesn't blank anything out).

Two things to verify:

1. Make sure BOTH the Allow Additions and Allow Deletions are set to "No" on the Properties of the Form (NOT on the properties of each field, but of the form itself -- it should say "Form" up in the title bar of the Properties box).

2. Make sure there is really only one record in your table.
 

cterjesen

Board Regular
Joined
Jun 23, 2003
Messages
119
That worked. Thanks!

I had the allow additions selected to "Yes". This must have been causing the problem.

Thanks again.
 

Forum statistics

Threads
1,148,397
Messages
5,746,460
Members
424,021
Latest member
naimathulla

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
Top