MS access: Variable is the name of a text box

Posted on 2007-07-30
Last Modified: 2013-11-28
Is it possible to use the name of a text box as a variable in an expression in the same text box. I'm building a floor plan Database, and have a Dlookup function that searches the cubicle name in a table and shows the associated name. Ideally I'd like to be able to copy and paste the boxes, and just change the 'name' of the text box without having to change the formula. I'm trying to make this as easy to update as possible so less technical people can modify layout easily.

My current expression
="1406-347      " & DLookUp("Expr1","FloorPlanQuery","CubeNumber='1406-347'")
I'd like to replace he cube number (1406-347) with an expression that will look up the name of the text box it is in.
Question by:CanadaDeskside
    LVL 61

    Expert Comment

    DLookUp("Expr1","FloorPlanQuery","CubeNumber='" & MyTextBoxName & "'")
    LVL 44

    Expert Comment

    When ever you copy and paste a textbox, it is given a default name Textnn.  As the control source for the textbox uses the textbox name,  I do not see how you can get away with changing just the name.  I think you will have to change the 'expression' also.

    Author Comment

    sorry does not work, tried several variations of MyTextBoxName, Nothing works and the build in help does not reconize the command.
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)

    If Cube Number is a String:

    ="1406-347      " & DLookUp("Expr1","FloorPlanQuery","[CubeNumber] = " Chr(34) & Forms.YourFormName.YourTextBoxName & Chr(34))

    If Cube Number is a Numeric

    ="1406-347      " & DLookUp("Expr1","FloorPlanQuery","[CubeNumber] = "  & CLng(Forms.YourFormName.YourTextBoxName))

    LVL 58

    Accepted Solution

    > an expression that will look up the name of the text box it is in.

    I don't think there is a way to do that.

    For events, you can often rely on Screen.ActiveControl to return the control you want. But for calculated expressions, the controls don't have the focus.

    You are looking for something similar to the Me keyword, always returning the form itself. There is nothing even close for controls. One way or another, you will have to repeat the information '1406-347' in the expression, even if it can be derived from the text boxes' names.

    Or perhaps similar to Excel's relative references. Access doesn't address controls like that, so you basically always have to use "absolute references" or more to the point, the control names.

    You can however write a loop to *write* the expressions based on the control names, but that would be used only once at design time. If you have 100 cubicles, it might be just a bit faster than doing it by hand, but not much (depending on your VB skills).

    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now