Field to be updated automatically in database from a form in Access

Question

I am using a table which has fields like OppNo, Material, OppMat along with couple of others.

i had linked the access database to a form in Access itself. The OppMat field is a concatenation of OppNo and Material fields. I had concatenated these two fields into OppMat in the form under the "Control Source" property of the OppMat Text Box in the form.

The form if run shows the concatenated value but it is not getting saved into the same field of the table. Same is when a new record is created. All other fields are saved for the record and not the OppMat field.

Please advise as to how I could get this field saved into the database.

Answer 1

When you write "=[txtA] & [txtB]" in the control source the control becomes calculated rather than bound to the underlying recordsource. You would need to select OppMat as the controlsource and use code or a macro to update the value after either source value changed.

On the other hand, saving a calculated value in the database is bad practice. It's better to concat it when you need it, as you are already doing, rather than store it.

You can create a query with all the fields from the table in question and add the concatenated value as a new query field. This mimics having the concatenated value in the table when using the values for display in forms and reports.