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.
Share Improve this question asked Jan 18 at 6:43 ShielaShiela 6931 gold badge9 silver badges23 bronze badges2 Answers
Reset to default 2This 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.