Search code examples
vbams-accesstexttextfield

How to copy a text value from a textfield with Control Source to another unbound textfield of the same form during load?

This is my textbox bound to a table field.

txtField1 = Control Source Code in Properties works fine showing the complete text value in that field:

=IIf(IsNull([txtID]),"",DLookUp("[Reason_1]","[Escalations]","[ID]=" & [txtID] & ""))

txtField1

img1

txtField2 This is my second textbox not bound to a table field but would like to copy the textvalue from txtField1 upon form load. I would like to copy the textvalue from txtField1 because txtField1 won't save in the table when I try to use insert query in a submit button due to apostrophes. txtField2 value (for now thru manual input with apostrophes) however, during submit is submitted.

txtField2

img2

I tried to use this code below upon load:

txtField2.value = txtField1.value

But gives me blank value for txtField2. But if I put it like this:

Private Sub txtField2_Click()
    Me.txtField2.Value = Me.txtField21.Value
End Sub

It works and copies properly during click of txtField2.

But how to make it work during form load or open.

Help is greatly appreciated.

Solution

  • This is the event sequence when you open a form:

    Open --> Load --> Resize --> Activate --> Current

    Loading the data is not yet accomplished during the Load event. Move the code to the Current event.

    Even better: Set the control source of the second TextBox to

    =[txtField1]

    This will automatically update the second TextBox when the value of the first one changes and no VBA code is required for this to happen.