Access: editing a field that is bound to an expression rather than a field

Question

Hello

On a continouos form in ms-access, I have a record source similar to

select
  col_1,
  col_2 & " " & col_3 as col_expr
from
  tblFoo

and then I have bound col_expr to a text box on that form.

When I open the form, it displays the value of col_2 & " " & col_3 correctly in the textbox. Obviously, editing the value within the text box doesn't work because its value is derived from an expression rather than from a field in the table. Accordingly, access prohibits me from doing so: when I click into the field, I cannot alter anything in the field.

I understand that behaviour. Yet, I am looking for a method to override that behaviour. That is, I want to be able to enter a value into that field and (for example) in its BeforeUpdate event retrieve the entered value and do a custom-insert/update and then set cancel to true.

Is this possible?

Answer 1

It may suit to use a work around, for example, display a hidden textbox for the update and dynamically rewrite the sql using the new data.

EDIT re Comment Alternatively you could show a small form for editing any record in the continuous form.

Answer 2

You could fire off an update query on the “After Update” event of that text box. After validating the data of course.