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

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

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.

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

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

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.

• vba
• ms-access
• text
• textfield

2 Answers 2

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.

Use the OnLoad event and Nz to return a valid, though non-existing, value for ID in case txtID is empty:

Private Sub Form_Load()

    Me!txtField2.Value = DLookup("[Reason_1]", "[Escalations]", "[ID] = " & Nz([txtID], 0) & "")

End Sub

or use the table field (bound to txtID) itself:

Private Sub Form_Load()

    Me!txtField2.Value = DLookup("[Reason_1]", "[Escalations]", "[ID] = " & Nz([ID], 0) & "")

End Sub

Also, do rename your controls to something meaningful.