Data entry from unbound control

[Ron]

I have a textbox whose control source is a string expression parsed from
(other) bound controls on the form. After the user enters new values in the
bound controls, it is intended that the constructed value in the unbound
control will be added to its "corresponding" field upon data entry. What is
the proper way to accomplish this?

Put another way: How to effectively have a form's textbox be bound when
browsing existing records, yet set to an expression when in data entry mode?

I have prior relational db experience, but starting my first Access (2007)
project. Thanks for any advice, RonL

[techrat]

You need to bind the control to the field that the value exists in/
will be saved in. To handle the automatic generation of the data in
the field, you need to latch into the after update event of each of
the fields that the generated field is comprised of. If there are
several fields that make up the generated field, you may consider
having a general procedure in your forms code to generate the value
and call it after update of each field that makes up the generated
field. ie:

Private Sub UpdateGeneratedField()
Me.GeneratedFieldName = Me.Field1 & "-" & Me.Field2 & "-" & Me.Field3

In the afterupdate event for each of the fields that make up the
generated field, do the following:
Call UpdateGeneratedField

I would recommend some data validation in the UpdateGeneratedField
procedure and/or the after update events for each field.

One other note, if you only want this field to be automatically filled
out and never edited directly by the user, you can set the form fields
locked property to true.

HTH

[Salad]

Ron wrote:

If you had
=fld1 & fld2 & fld3
as the control source, and fld2 was not filled in, it might not look/be
correct. You could have a function
=GetCombined()
and have
Function GetCombined()
IF not isnull(fld1) and not isnull(fld2) And not isnull(fld3) then
GetCombined = fld1 & fld2 & fld3
endif
End Function

It should automatically update. However, if it doesn't, do something
like is the field name is CalcedField
Me.CalcedField.Requery
in the afterupdate events of fld1..fld3.

[Ron]

Sounds like the thing to do is bind the control and work the expression from
code. Glad I asked cause I was thinking the other way - use code to enter
the control's expression into the new record. Good to know the proper event
is the change.

Yes I'll do some data validation and disable the (bound) control derived
from the expression.

Thank you both!

(FWIW, the derived field is to be used as a unique "catalog id," [hopefully
eventually to appear on a website if my wife's hobby-business takes off.
This db is for her startup, and as a prototype for a content driven website
if that becomes warranted.] The uniqueness of the value should be
guaranteed because the PK is part of the string. I still gotta think though
whether it should change at all if she edits an existing record. If I
understand my reading so far, it is possible to distinguish in code whether
the current record is "existing" or "new.")

-RonL