Search code examples
vbams-accesstextboxms-access-2007

Setting the Control Source of a textbox VBA

In my Access 2007 Form, I was previously successful in setting the controlSource of a textbox, directly in the properties window, using this code

=UCase(Left([txtLName],6) & "_" & Left([TxtFName],1))

However, in an attempt to hard-code this into the form, I'm trying to use VBA to set the controlSource property using this code:

Me.txtCodePersonal.ControlSource = "=UCase(Left([txtLName],6) & "_" & Left([TxtFName],1))"

In my debug, it address my problem to the "_" section of this line.

I don't know how the controlSource property work in VBA, so I don't know how to correct this. Thank for all your help in advance.

Solution

  • You're attempting to assign a string value to the .ControlSource property. However, that string includes quotes within it. Similarly, in the Immediate window, this will throw an error:

    Debug.Print "=UCase(Left([txtLName],6) & "_" & Left([TxtFName],1))"

    Double up the quotes inside the string to avoid that problem.

    Debug.Print "=UCase(Left([txtLName],6) & ""_"" & Left([TxtFName],1))"
=UCase(Left([txtLName],6) & "_" & Left([TxtFName],1))