Text box contents as field name vba

[matthewnsarah07]

Hi

Quick one, if [Field1] contains text that is equal to the name of another field, how do i use [Field1] in vba as the field it contains rather than as Field 1.

Basically I want to do some on screen calculations and [Field1] will say which field to place the answer in

Many Thanks

 

[dbDamo]

Something along the lines of:-

If Field1 = Field2 Then
MsgBox "Please provide answer in Field3"
Me.Field3.SetFocus
Else
MsgBox "Please provide answer in Field4"
Me.Field4.SetFocus
End If

And you want to place this in the after update event of the control

EDIT - Then if you only require an answer to be provided in the appropriate field I would go a step further and hide the control that isn't required. Therefore the code would be:-

If Field1 = Field2 Then
MsgBox "Please provide answer in Field3"
Me.Field4.Visible = False
Me.Field3.SetFocus
Else
MsgBox "Please provide answer in Field4"
Me.Field3.Visible = False
Me.Field4.SetFocus
End If

 

[matthewnsarah07]

Thanks for your reply but I'm not looking for user input just to get a text box name from the field

For example if the text box [Field1] = "Field2", then I would want the calculation (answer) in [Field2] text box, this will be done via vba so how do I get vba to check which textbox to enter the result in based on the content of [Field1]

 

[DCrake]

By using Field1, Field2 in your examples is not very helful actual examples would be more helpfull

 

[dbDamo]

As DCrake says it would be more helpful if you provided an actual example. Please correct me if I am wrong, but here is my understanding of your request:-

You have 2 controls, Field1 and Field2

If the value entered into Field1 is equal to the value of the label of Field2 then you wish to perform a calculation and place the answer in Field2

If the value entered into Field1 is not equal to the value of the label of Field2 then you want to do what????

 

[matthewnsarah07]

Sorry, here goes

frmmain contains an unbound text box called [JunctionAhead], this is populated from another field and might look like "J18-J19"

Now another form frmmap will contain an unbound textbox called [J18-J19] in which a calculation needs to go.

The calculation itself remains the same but the text box it should be shown in changes. So I would like part of the vba to take the value from frmmain![junctionahead] and use it as the destination for the resulting calc.

Hope this makes more sense

 

[DCrake]

Cannot understand the logic of your code but you can refer to fields on a form like this

StrFieldName = "J18-J19"

Forms("FrmMap")(StrFieldName) = ????

 

[matthewnsarah07]

Would StrFieldName = [Forms]![frmmain]![JunctionAhead] work

Basically I have over 400 locations the map in frmmap and rather than write an individual line of code for each calcualtion for every unbound textbox I just wanted to use the value in [JunctionAhead] to determine which textbox would receive the answer, and so limiting the number of lines code

 

[DCrake]

It should do, why not give it a try.