Is there way to execute a string in VBA

I want to do something very easy.
I just want to place some code in a string and execute it.
Tried Eval, but no luck.

Example:

Dim strVal As String
strVal = "Forms!frm_000_010_REFERENCE_MAIN.txtpending.visible=true"
Eval (strVal)
askolitsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
This isn't quite evaluating an expression, isn't it? This is property assignment or executing a code, which on underlying level might be two different things.

For example, eval of "5 * 2", could be evaluated to but an assignment won't. Here is a quick script and it's output.

dim x, y, z
x = 5
y = 2
z = 0
MsgBox(x * y)		'10
MsgBox(Eval("x * y"))	'10
Eval("z = x * y")	
MsgBox(z)		'0
z = x * y
MsgBox(z)		'10

Open in new window


If you could tell what exactly you want to achieve, may be, we can think of the solution.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
In order to make a Form 'Visible' ... you  need to do this:

Dim frm As Form
Set frm = Form_frm_000_010_REFERENCE_MAIN
frm!txtpending.Visible = True

For this case, be SURE you have the Form's 'Has Module' property set to Yes.


Or ...
DoCmd.OpenForm "frm_000_010_REFERENCE_MAIN"
Forms!frm_000_010_REFERENCE_MAIN.txtpending.visible=true

Not sure you can get that into an Eval() statement
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi,

as mentioned by Nitin and Joe, Eval is simply to evaluate an expression. The result is a string or numeric value.  You cannot have an assignment to a variable there

Application.Eval Method (Access)
https://msdn.microsoft.com/en-us/vba/access-vba/articles/application-eval-method-access

What is your reason for wanting to do this with Eval? Do you have a list of controls to make visible or not?

have a day that matters,
crystal
0
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.

ste5anSenior DeveloperCommented:
What's your use-case?
0
askolitsAuthor Commented:
Thanks for all your input. I have many ways I could use to set the value, I was just trying to optimize my code.
 
I was adding a bunch of values to an array and then iterating through the array.
Using those array values, determining if I need to display a control. All that part is easy.
What makes it tough is the control to display may be in the main form, a sub form of that main form, or a control in a sub form of that subform.

So if I could store the '.visible' action in an array value then execute it, it makes my code cleaner.

It's kind of hard to explain this in any more detail. But, l have a many options. Just trying to optimize my code.

Thanks all.

John
0
ste5anSenior DeveloperCommented:
"Dynamic" meta code like this is not necessarily optimized code.

Using those array values, determining if I need to display a control. All that part is easy.
The correct approach is having a method which does this. E.g.

Public ApplyControlRules(AForm As Access.Form, ARules As Object)

  Dim Control As Access.Control

  For Each Control in AForm.Controls
    If [Control.Name IN ARules] Then
	  If TypeOf Control Is Access.ComboBox Then        
	    'Set specific control typ rules.
      End If

	  If TypeOf Control Is Access.CommandButton Then        
	    'Set specific control typ rules.
      End If

	  If TypeOf Control Is Access.TextBox Then        
	    'Set specific control typ rules.
      End If
	  
	  'Set general control type rules.
    End If
  Next Control
  
End Sub

Open in new window


where AValues is your array. The concrete implementation depends on your kind of array. Where I need to say, using a record set and storing these rules in a table makes more sense.
1
askolitsAuthor Commented:
@ste5an Yes, but I need to do subforms as as well, and subforms within the subforms.  You would need to then call this function within itself to iterate through the main form, it's subforms and any of those subforms.  

Not sure why I would want to iterate through all the control when I already know which one I want to change. I have done this type of iteration before and found that forms with many controls take time to do and slows down the code.

I already have a solution, so I'm good. Thanks.
0
ste5anSenior DeveloperCommented:
Well, when it works for you..

Not sure why I would want to iterate through all the control when I already know which one I want to change
The problem is that I don't know, why you know that. The reason here is important for the correct approach. Coding is sometimes less obvious than expected.

Caveat: Is just it's not an easy task and you may get "working" code. But working code is not necessarily good code. It maybe even incorrect.
1
askolitsAuthor Commented:
@Ste5an "The problem is that I don't know, why you know that."  
Because, I've done it. I had a form with about 100+ controls and tried iterating through them changing some properties.
You may question why I had that many controls, and it was a long time ago.

Just so you know, I've been developing Access apps since 1.0. So, certainly not a novice.
Not that I couldn't always learn more about optimizing code. Feel free to check out my product which is 100% Access, including the touch screen. It'll give you some idea of what I'm working on.

Check-4-It
0
Gustav BrockCIOCommented:
Use CallByName:

CallByName Forms!frm_000_010_REFERENCE_MAIN!txtpending, "Visible", VbLet, True

Open in new window

1
askolitsAuthor Commented:
@Gustav Oh.. that's real close. Cool idea. But I need the object to be a variable.

Something like:
Dim varObject
varObject = "Forms!frm_000_010_REFERENCE_MAIN!txtpending"
CallByName varObject , "Visible", VbLet, True
0
Fabrice LambertFabrice LambertCommented:
Or try the following:
public setControlProperty(ByRef frm As Access.form, ByVal ctrlName as String, ByVal propertyName As String, ByVal value As Variant)
    If(ExistInCollection(frm.Controls, ctrlName)) Then
        Dim ctrl As Access.Control
        Set ctrl = frm.Controls(ctrlName)
        If(ExistInCollection(ctrl.Properties, propertyName)) Then
            ctrl.Properties(propertyName) = value
        End If
        Set ctrl = Nothing
    End If
End Sub

Open in new window

Along with these 3 helpers functions:
Public Function existInCollection(ByVal key As String, ByRef col As Collection) As Boolean
    existInCollection = existInCollectionByVal(key, col) Or existInCollectionByRef(key, col)
End Function

Private Function existInCollectionByVal(ByVal key As String, ByRef col As Collection) As Boolean
On Error GoTo Error
    Dim item As Variant
    item = col(key)
    existInCollectionByVal = True
Exit Function
Error:
    existInCollectionByVal = False
End Function

Private Function existInCollectionByRef(ByVal key As String, ByRef col As Collection) As Boolean
On Error GoTo Error
    Dim item As Variant
    Set item = col(key)
    existInCollectionByRef = True
Exit Function
Error:
    existInCollectionByRef = False
End Function

Open in new window

0
Gustav BrockCIOCommented:
Or like this:

    Dim Form            As Form
    
    Dim FormName        As String
    Dim ControlName     As String
    Dim PropertyName    As String
    Dim Value           As Variant
    
    FormName = "frm_000_010_REFERENCE_MAIN"
    ControlName = "txtpending"
    PropertyName = "Visible"
    Value = False
    
    Set Form = Forms(FormName)

    CallByName Form(ControlName), PropertyName, VbLet, Value

Open in new window

0
askolitsAuthor Commented:
@Gustav  -  I though that did it, but I couldn't use the Set Form to point to the lower subforms.

This works
               Dim Form as form
               Dim FormName As String

                FormName = "frm_000_010_REFERENCE_MAIN.sbf03.form"
                Set Form = Forms!frm_000_010_REFERENCE_MAIN.sbf03.Form
               
But this does not, with the subform reference.
               Set Form = Forms(FormName )
0
Gustav BrockCIOCommented:
That would be something like this, using yet a string variable:

Form(SubformControlName).Form(ControlName)

Open in new window

0
askolitsAuthor Commented:
I'll look at this later tonight. But I still need to go down one more subform level.
But not always. Sometimes main form, sometimes one level, sometimes 2.
So, now we are getting the the solution I'm currently doing, which is essential different CallByName types, for each level.
I was really looking for a catch-all for all the different subform combinations. But it looks like I'll have to build a different one for each level.

Forms!frm_000_010_REFERENCE_MAIN
Forms!frm_000_010_REFERENCE_MAIN.sbf03.Form
Forms!frm_000_010_REFERENCE_MAIN.sbf03.Form.sbf1.form
0
Gustav BrockCIOCommented:
Or you could have an array of subform control names:

SubformControlNames = Array("SubformControlName1", "SubformControlName2", "SubformControlName3")

Open in new window

Then cancatenate this array to identify the object. If three:

Set Form = Forms(FormName).Form(SubformControlNames(0)).Form(SubformControlNames(1)).Form(SubformControlNames(2)).Form(ControlName)

Open in new window

If one only:

Set Form = Forms(FormName).Form(SubformControlNames(0)).Form(ControlName)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
askolitsAuthor Commented:
@Gustav  Well, that's pretty much what I ended up doing.  As always, you've been a great help!

Thanks!!
(And to everyone else that contributed, as well)
0
Gustav BrockCIOCommented:
You are welcome!
0
askolitsAuthor Commented:
Thanks to all who responded
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.