Copy a value without linking?

[P]

I am trying to automatically set the value of a text box within a subform
when the user creates a new record in that subform. I want the value to be
pulled from
another form (the main form which the subform is linked to) in the database,
but I do not want the value to ever be updated.

In other words, I want to just copy the value from the main from to the test
box on the subform-- once and only once. Example:

MainForm: field="Name" (field is a combo box)

Subform: field="NameAtTimeRecordWasCreated" (field is a text box)

The "Name" field on MainForm will continue to be updated-- records will be
written over, not added or deleted.

As new records are added to Subform, I want the
"NameAtTimeRecordWasCreated" text box to be populated with the value in the
"Name" field on MainForm at that particular moment in time.

I want the "NameAtTimeRecordWasCreated" text box entry to never change, even
when the "Name" field in MainForm is altered.

Is this possible?

 

[BruceM]

The only way to save the name of the person who created the record is to
save that person's name. A typical main form is based on a main table, and
the subform is based on a related table. Each main table record may have
several related records in the other table.
Why not just add the person's name to the subform record? You can have a
field in the main table for the name of the person who created the initial
record, and a field in the related table for the name of the person who
created that record.

 

[P]

Thanks for your reply Bruce. I think my initial description of the issue was
misleading-- I do not want to capture the name of the person creating the
record.

I just want to "dump" the data from one Field1 to Field2, and never allow
Field2 to be changed. Even if the original Field1 is altered in the future.

Does that make sense?

 

[BruceM]

No, it doesn't make sense to me. To which subform record would you "dump"
the data? Why not just add the name directly to the subform record? To
guard against changes you could add code like the following to the subform's
Current event:

If Me.NewRecord Then
Me.SomeTextBox.Locked = False
Else
Me.SomeTextBox.Locked = True
End If

If you want the subform record to be protected from change after it is
created you could set its Allow Edits and Allow Deletions properties to No.

If there is some reason you have to add a name to the main form just so it
can be copied to the subform you could do something like add the name to an
unbound text box (or select it from a combo box) on the main form. Then, go
to a subform record and initiate code (click a button, or update a field, or
start a new record, or something) to copy the information to the subform:
Me.Field2 = Me.Parent.SomeTextBox

You still need to do something to protect the subform record from change.