• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

MS access: Variable is the name of a text box

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.
1 Solution
DLookUp("Expr1","FloorPlanQuery","CubeNumber='" & MyTextBoxName & "'")
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.
CanadaDesksideAuthor Commented:
sorry does not work, tried several variations of MyTextBoxName, Nothing works and the build in help does not reconize the command.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

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))

> 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).

Forced accept.

EE Admin
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now