Eval function question doesn't work as I expacted

[janz]

I'm trying to update a form using the eval function in a program.
However there is a part that I can't get to work
It will not change the back ground color to white.
I use the eval function since I will do this many times and the form automatically add textboxes when more data is available

Function test()
a = "Forms![INFORM]!LabelIt7.backcolor= 16777215"
Eval (a)
Debug.Print a
End Function

Also tried "Forms![INFORM]!LabelIt7.backcolor= " & 16777215

It doesn't change the color and it doesn't give an error message.
If I change the a string into an command that is not possible it will give an error message.

If I give an enter in the immediate window at the place where it printed the 'a' string the function works perfectley.

Can anybody tell me where I go wrong?

 

[vbaInet]

I don't think you can use an Eval function that way. The result of the eval string should be a string or a number I think. You can run the function using Eval though (because a function returns something.

Eval("test()")

Function test() as String
Forms!INFORM!LabelIt7.backcolor = vbWhite
End Function

 

[dkinley]

Not sure why you are using Eval to test a property ... ?

To my knowledge, Eval will only evaluate a function or something that performs some calculation, test a condition such as the IN expression in SQL, or to simulate an event which will cause the instructions in the event to execute.

I've used a condition to test the property or simply forced the change. I don't think the way you're are testing your logic will work. If you used Eval to call your function outside its scope, it might cause the background to change; however, I have never used it to modify a background color.

You might try using Eval(Test()) outside the function using the correct statement, but I wouldn't guarentee the results.

-dK

 

[vbaInet]

You might try using Eval(Test()) outside the function using the correct statement, but I wouldn't guarentee the results.

-dK

In quotes though.

 

[gemma-the-husky]

why would you want to use eval to change a colour

simply

myfield.backcolor = vbwhite etc does it.

 

[vbaInet]

why would you want to use eval to change a colour

simply

myfield.backcolor = vbwhite etc does it.

Not sure Dave. Seems rather odd.

 

[janz]

Thanks for all your help but it did not solve my problem yet.

I'm creating a form for dummies. Every time when my info db growth I'm translating the info into colored boxes. These boxes can be clicked by the user. A click will take the box name and jump to a new routine. This routine will show the details and resets the original box color so the user can see it's been clicked.

However since I reconstruct the form every time there are not always the same amount of boxes. setting this function on everybox would be too time consuming.

so with the box number in the routine I can create a string like
"Forms![INFORM]!LabelIt7.backcolor= 16777215"
now all I have to do is execute this string.

I have done stuff like it in the past with Eval. (calling functions from a table)
Worked fine. Eval("function name")

So I thought this might work as well.
And in a way it does. since when I create a wrong string it give me the correct error message and when I put in the correct string it doesn't
But it doesn't change the color either.

So my question in a nutshell how can I execute a string like
"Forms![INFORM]!LabelIt7.backcolor= 16777215"
as a command.

 

[vbaInet]

Myself, dkinley and gemma-the-husky have explained how it can be done and why it wouldn't work with an Eval() function.

If you search the vba help files for EVAL you will find that it compounds upon what we've said.

You would need to explain exactly what you're trying to do and why you don't want to set the back color as gemma-the-husky advised?

 

[SOS]

Create your own function to set the property and then pass it the parameters.

Function SetFrmProp(strForm As String, strControl As String, strProp As String, strValue As String)
    Dim frm As Form
    Dim ctl As Control
    
Set frm = Forms(strForm)
Set ctl = frm.Controls(strControl)
 
' now we set the property
ctl.Properties(strProp).Value = strValue
 
End Function

(untested Air Code)

 

[dkinley]

If I read the post right, you can try something I have used in the past. Granted this is to give an effect for clicking and has a reset feature - but it might give you some idea and jumping off point ....

Function BackgroundEffect(iMouseAction As Integer)
On Error GoTo Err_BackgroundEffect_Function
 
    Dim frmName As Form
    Dim ctlActive As Control
    Set ctlActive = Screen.ActiveControl
    Set frmName = ctlActive.Parent
 
    Select Case iMouseAction
    Case 0 'down
        frmName.Form(ctlActive.Name).BackColor = 13553356 'gray
    Case 1 'up
        frmName.Form(ctlActive.Name).BackColor = 16777215 'white
    End Select
 
Exit_BackgroundEffect_Function:
    Exit Function
Err_BackgroundEffect_Function:
 
    MsgBox Err.Description
    Resume Exit_BackgroundEffect_Function
End Function

Create this function and then in the control properties for the MouseDown event use:

=BackgroundEffect(0)

And in the MouseUp event use:

=BackgroundEffect(1)

If the controls will always jump to the same control each time, you can either put that action in another event of the control or add that to the function. For example, add the next control in the Tag property of the control and then use that in the Case 0 part of the function.

This will only give you the background color change while the mouse button is down, but you can remove the call on the MouseUp event. Anyhow, just a jumping off place ...

Just an idea,
-dK

 

[janz]

Thanks SOS this is what I mend. It works fine.
One little issue left. The control is on a subform how do I address that in the "frm" string?

Thanks DK keep yours in mind as well.

 

[janz]

I found it myself Thanks you all for your help.

for the people interested on how to add a subform.

Dim frm As Form
Dim frmS As SubForm
Dim ctl As Control

Set frm = Forms(strForm)
Set frmS = frm.Form(strsubform)
Set ctl = frmS.Controls(strControl)

' now we set the property
ctl.Properties(strProp).Value = strValue

 

[dkinley]

Great! Thanks for posting your final solution and apologize for missing the mark.

Cudos to SOS ....

-dK