Access vba pass variable into code

Question

I'm in Access 07 trying to pass a variable to a line of code. The purpose is to make an array of field names and then loop over the array performing the same action on each field. I've simplified it down to avoid any potential problems with the array or the loop. In any other language I am familiar with it would be pretty simple, but I can't seem to format it in VB.

Me.FeildName.Locked = True

would be the static code, and I think the variable code would look something like this:

Dim Temp as String
Temp="FieldName"
Me.[Temp].Locked = True

but it keeps giving me an error that says "can't find the field '|' referred to in your expression", so it's not reading the value of the variable.
How do I get it to read the variable in the command?

Alternatively, I've tried to concatenate strings into a line of code:

Dim CodeLine As String
Dim TestName As String
TestName = "FieldName"
CodeLine = "Me.[" & TestName & "].Locked = True"

This creates a string that looks like functional code but how would I run it?

Thanks

possible duplicate of How To Get Control Property by "String Name"? — Ravi Yenugu
– Ravi Yenugu, Commented Jul 15, 2014 at 19:36

Dick Kusleika · Accepted Answer · 2014-07-15 19:33:23Z

2

If Me is a form, you need

Temp = "FieldName"
Me.Controls(Temp).Locked = True

answered Jul 15, 2014 at 19:33

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

charles_m80 Over a year ago

"Controls()"... that's what I needed! I knew there was just some nomenclature I didn't know. I had tried "Fields()" and a couple others I found with no success. Thank you!

VBlades · Accepted Answer · 2014-07-15 21:27:58Z

0

Dick Kusleika's answer is the way to go, but for completeness' sake, you can do also something like this:

For i = LBound(strControlNames) To UBound(strControlNames)
    CallByName Forms![MyFormName].Controls(strControlNames(i)), "Locked", VbLet, "True"
Next

strControlNames would be the array with your control names.

answered Jul 15, 2014 at 21:27

VBlades

2,2511 gold badge14 silver badges8 bronze badges

2 Comments

charles_m80 Over a year ago

This looks similar to my code but I want to experiment with the differences to see if I can learn anything. What does the "VbLet" do? I can't find that in the book I have or even a web search. Thanks

VBlades Over a year ago

@charles_m80: CallByName can run a method or sets a property value (msdn.microsoft.com/en-us/library/office/…). vbLet is a constant which is telling the function that we want the property in question, Locked, in this case, to be set to True (as opposed to vbGet, which would indicate we want to extract the value, for example). If you have done any Class programming in VB or VBA, Let/Get is how you set/get custom you custom class properties.

Collectives™ on Stack Overflow

Access vba pass variable into code

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related