[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 529
  • 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.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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