Set Default Value of one control to another control on the same form

[access-novice]

I am trying to set the default value of one control on a form to the value of another control on the same form.

The form's data source is a query which links two tables (Employees & Status) together.

In the Employee table there is a field called "Function" and in the Status Table there are fields called "Function1" and "Function2". Currently, all 3 of these fields are combo boxes pulling their data from a "Functions" table.

On the Status Report Form, I want the value of "Function1" and "Function2" to default to the value of "Function" (from the Employee table). However, I want the user to be able to change "Function1" and/or "Function2" if necessary without changing the value of "Function".

 

[boblarson]

Welcome to Access World Forums!

Sorry to give you advice you won't like hearing, but having Function1 and Function2 violates normalization rules and is something you should learn how to avoid. You should not have repeating fields. If you have multiple data for an entity then you should add at least one more table to handle the one-to-many relationship and then use a subform to enter the data.

 

[maxmangion]

Go to the default value property of function1 and put the following:

[Forms]![FormName]![FunctionControlName]

Replace the the above with the actual names of your form and control.

 

[access-novice]

In response to BOBLARSON,
Thanks for your quick response. Unfortunately, this is an inherited DB that I am trying to make work for the time being until I can re-create it the right way.

In response to maxmangion,
I have tried to input the code you provided into the default value property of function1 but it still doesn't work. Could it be because the data source of the form is a query instead of the tables themselves (a select statement)? Aren't they (query vs Select statement) essentially the same?

 

[Eljefegeneo]

Go to the default value property of function1 and put the following:

[Forms]![FormName]![FunctionControlName]

Replace the the above with the actual names of your form and control.

I too had the same problem. But when I tried your suggestion of [Forms]![FormName]![FunctionControlName], it wouldn't enter the value until after I saved the record. Furthermore, it won't permit me to change the data in the file. It always reverts to the data of [Forms]![FormName]![FunctionControlName]. So unlike setting a default in the field in the table which can be manually overriden, this doesn't seem to be able to accomplish what I would like it to do. In my case I have fields Name and Name2 which in 95% of the cases are the same. So I don't want to have to type in the data in the second field unless I have to do so. Any suggestions?