Access - Eval function

[dodo47]

Hi to all


this is my problem
i need to use <EVAL> function for my db, but it does not run.
Example: if i execute this routines (sure enough i build my string)

Sub ChangeIcon()
Dim mStr As String
mStr = "CommandBars(18).Controls(1).FaceId"
Debug.Print Eval(mStr)
End Sub

all works well and i see the result (67 for example)
But if i want to change the FaceId, the routines don't show any error, but che FaceId is non changed

Sub ChangeIcon()
Dim mStr As String
mStr = "CommandBars(18).Controls(1).FaceId"
Eval (mStr & "= 3")
Debug.Print Eval(mStr)
End Sub

value returned always 67

can you help me?

tks, regards

[June7]

I guess it always returns 67 because that is the value of "CommandBars(18).Controls(1).FaceId"

The line Eval (mStr & "= 3") accomplishes nothing so your second code example is essentially the same as the first.

Debug.Print Eval(mStr & "= 3") would be something different from the first example.

What is it you are trying to do?

[dodo47]

Hi
tks for your answer
The first routine is only to see the faceId number stocked in my menu item.

With the second routines i try to change the faceId to my menu item. Instead of faceId 67, i would like have another faceId (3 in the example).

If i write:

sub Test()
CommandBars(18).controls(1).FaceId=3
End Sub

i will see the FaceId changed to 3.

But using Eval(....) the FaceId remains as is (67).

I need to use Eval, because i "build" my string with parameters.

regards

[June7]

I have never seen anything like this and need more understanding.

What is FaceID? Why do you need to change it? You build what string? How will you build? Show examples.

[dodo47]

Ok (i'm italian so my english is... )

is not simple..
Every item of access menu has a icon (ie: the save button in file menu has a icon like a disk).

The FaceId is the number of the icon.

If you build a custom bar ie: MyMenu (CommandBar) and add a item called "AsYouWant" (controls of commandBar) you can assign to this item a Icon.
Click write >> Change Icon...But Access shows only few "Faces".

In my routine i get all faces (DoCmd.ShowToolbar "web", acToolbarNo) in a form (about 1000).

IE:
your custom bar: MyMenu
Item................: AsYouWant >>>assign a Icon ie:disk


Now if you run the command:

CommandBars("MyMenu").ShowPopup

you will see the popUp menu.

CommandsBar, like sub-menu, has not icon, but the items yes.

So, if you run:
I=CommandBars("MyMenu").Controls("AsYouWant").Face Id

you will see the number of the icon, ie: 3

Of course CommandBars and Controls have caption and numbers

In My DB the commandBar MyMenu has number 18 and the item AsYouWant has number 1

so, if i write
I=CommandBars(18).Controls(1).FaceId
it returns same result...3.

Now i need to let custom can change the FaceId (icon)
So i get all variables in a string, like:
myStr="CommandBars(" & NumberOfCommandBar & ").Controls(" & NumberOfControls & ")"

Finally
if you run command:
CommandBars(18).controls(1).FaceId=67
i will see the icon changed

but if i use EVAL the icon does not change.

I think is all...
regards

(FaceId and not Face Id as i see in the preview)

[June7]

Starting to understand. You want to allow user to select the command bar and a control and change the icon?

You already tried (no Eval):
CommandBars(NumberOfCommandBar).Controls(NumberOfC ontrols) = 3

Would be helpful to work with project if you want to provide. I don't have Access 2003 available anymore but hopefully 2007 can handle the code.

I have customized ribbon/QAT in Access 2007 so maybe I will be able to figure out your 2003 setup.

[dodo47]

Starting to understand. You want to allow user to select the command bar and a control and change the icon?
yes..is it

You already tried (no Eval):
CommandBars(NumberOfCommandBar).Controls(NumberOfC ontrols) = 3
this is the reason i would like to use Eval function but, as told, the instruction do not returns any error and do not effect the change.
So, for the moment, i do a twisted procedure in which i "create" via vba a module, passing it my string, run the new module and then cancel the new module.
This is what about

 ' in the main procedure build the string with a loop
Sub xx()
....
....
M = M & "." & CmdCtr & "(" & mItem & ").FaceId=" & mFace
'call module creating
MakeCode (M)
DoCmd.DeleteObject acModule, "ModToCanc"
....
End Sub

Function MakeCode(M1 As String)
Dim strText As String
strText = "Function ChangeFace()" & vbCrLf
strText = strText & M1 & vbCrLf
strText = strText & "End Function"
Application.RunCommand acCmdNewObjectModule
Set MyModule = Application.Modules.Item(Application.Modules.count - 1)
MyModule.InsertText strText
DoCmd.Save acModule, MyModule
DoCmd.Close acModule, MyModule, acSaveYes
DoCmd.Rename "ModToCanc", acModule, MyModule
End Function

So i obtain (ie) this new module:

Function ChangeFace()
CommandBars(18).Controls(1).FaceId = 3
End Function

I run it and when returns to main procedure it will be deleted.

Would be helpful to work with project if you want to provide. I don't have Access 2003 available anymore but hopefully 2007 can handle the code.

I have cusomized ribbon/QAT in Access 2007 so maybe I will be able to figure out your 2003 setup.

Yes, i know...but have not 2007

regards

[June7]

I have never done code that creates code. This method is working? You want to replace it and that's why you tried the Eval?

I know your project is 2003 but I might still be able to run it under 2007 if you want to provide it.

[dodo47]

I have never done code that creates code. This method is working? You want to replace it and that's why you tried the Eval?

I know your project is 2003 but I might still be able to run it under 2007 if you want to provide it.

Yes, the method is working and i would like replace it with eval function.

Here is a little example attached that has not the function construction/delete module (is not necessary here).

When you open the frm "MyFrm" you will see the custom menu that has only one item (This is a test). Click on it and you will se a msg (of course is not so, click will open the relative procedure in my db...)

Click Ok btn and the frm will appear.
On the open event i had to insert a routine to get the references of custom cmdBar MyMenu and control because i dont know in your access what number will be assigned, so i search it.

In the form you will see:
. Number and name of custom cmdBar
- Number and name of item
- number of actually icon

In the colored field insert a number (i think you can input values also > of 100...but is an example)

Then click btn "Without EVAL" and you will see the icon changed in the popUp and in the relative field Icon number.

If you change number of icon and then press EVAL the icon do not change.
(You will see also the string passed to Eval.)

Now is not so simple as you see, because My custom bar has menu and sub-menu of sub-menu, so i cannot stock all references in fields as in this example, but i build a string like:
CommandBars(18).Controls(2).Controls(3).Controls(1 ).etc...etc...FacedId = xx


Regards

Edit In the Icon label there is "Number of actually Icon and name"... of course there is only the number..

[June7]

This is quite a learning experience for me. Might be easier if I still had 2003 available. Apparently there is a custom toolbar in this project called MyMenu but I have no idea where it is and how it is built. It doesn't display anywhere either. That aside, I can run the code and see the behavior you describe.

Now about use of the Eval function. You must build a string of the CommandBar and subcontrols because this is a dynamic process? User must be allowed to select an item at any level of the command bar and the item must be referenced through the heirarchy of the menu structure? Okay, quite a dilemma here. Is what you want to modify a button on the toolbar I can't view and the 'popup' is just for testing convenience?

An expression like Eval("8/2") is recognized by VBA but Eval with the CommandBar string errors.

I tried declaring a button object variable and setting that variable to the CommandBar string with and without Eval. No luck. If what you want is possible, it is beyond my expertise level.

Why is this functionality needed by users?

[dodo47]

Tks of your time...i have understood that you could run it with a copy of 2003...but if you dont have it...
In my DB structure main form instead of a schedule structure there are 8 labels and clicking each one you can see the relative menu-sub_menu-items.

This is quite a learning experience for me. Might be easier if I still had 2003 available. Apparently there is a custom toolbar in this project called MyMenu but I have no idea where it is and how it is built. It doesn't display anywhere either. That aside, I can run the code and see the behavior you describe.
You can see the MyMenu custom bar clicking Tools > customize >...now i dont know what you see in english..in italian i click In the voice named "Menu di scelta rapida" such like "quick choose menu" (?).
Do This you will see a tool bar in which at the end you have to find "personalized...i think". There is MyMenu.

Now about use of the Eval function. You must build a string of the CommandBar and subcontrols because this is a dynamic process?
Yes. Really in my procedure i build a tbl with all heirarchy reference of menu-sub_menu and item (index...faceId etc)

User must be allowed to select an item at any level of the command bar and the item must be referenced through the heirarchy of the menu structure?
Yes, he can see the current Icon (not number, but the "Face") and can choose which face of about over 1000 assign to that item.

Okay, quite a dilemma here. Is what you want to modify a button on the toolbar I can't view and the 'popup' is just for testing convenience?
as told before

An expression like Eval("8/2") is recognized by VBA but Eval with the CommandBar string errors.
Not so... the proble is that EVAL does not return any error, simply does not work...(bug of microsoft ?? ...i dont know.

I tried declaring a button object variable and setting that variable to the CommandBar string with and without Eval. No luck. If what you want is possible, it is beyond my expertise level.

Why is this functionality needed by users?
"vagary" of customs..eh..eh

best regards

[June7]

I did say from the beginning did not have 2003 and would be viewing in 2007/2010 which don't have toolbars like in 2003. There is no Tools>Customize menu selection (customize is in different place). If client is considering upgrade to 2007/2010, need to keep in mind this custom toolbar might not work, although it is supposed to in 2007, not sure about 2010. Can only say I can't see it in either.

I test Eval("8/2=4") in VBA and that works - it evaluates to 'True'. When I try the Eval with CommandBars string I get error message that VBA can't find function. My conclusion is this is not correct use of Eval because your expression is to set a property, not evaluate a math operation or a function.

[dodo47]

hi and tks again

would you say that if in your 2007 run this procedure, you get error and not see anything of debug print?

Function MenuPopupX()
    Dim CBR As CommandBar
    Dim lngIDX As Long
    For Each CBR In Application.CommandBars
        If CBR.Type = msoBarTypePopup Then
            x = x + 1 ' only to print first x menù
            Debug.Print "0 (" & CBR.Index & ") Menù=" & CBR.Name & " "
            ControlCommandBar CBR, , lngIDX
            Debug.Print vbNewLine
        End If
        If x = 5 Then Exit Function ' only to print first x menù
    Next
End Function

Function ControlCommandBar(CBR As CommandBar, Optional Level As Integer = 1, Optional Idx As Long = 0)
    On Error Resume Next
    Dim ctl         As CommandBarControl
    Dim strPrint    As String
    Dim lngIDX      As Long
    Dim MenuIndex As Integer
    Dim SubMenuIndex As Integer
    Dim ItemIndex As Integer

    For Each ctl In CBR.Controls
        If ctl.Type = msoControlPopup Then
            strPrint = String$(Level * 2, " ") & Level & "(" & ctl.Index & ") SubMenù=[" & ctl.CommandBar.Name & "]"
            strPrint = strPrint & String$(80 - Len(strPrint), ".") & "Tipo = " & ctl.Type & " <--- Recursive (" & Level & ")"
            Debug.Print strPrint
            Call ControlCommandBar(ctl.CommandBar, Level + 1, lngIDX)
        Else
            strPrint = String$(Level * 2, " ") & Level & "(" & ctl.Index & ") Item=[" & ctl.Caption & "]"
            strPrint = strPrint & String$(80 - Len(strPrint), ".") & "Tipo = " & ctl.Type
            Debug.Print strPrint
        End If
    Next
End Function

regards

[June7]

You mean run the functions by calling with Eval function?

This works:
Eval("MenuPopupX()")

My conclusion is not altered.

[dodo47]

Ciao, i refer to

..... If client is considering upgrade to 2007/2010, need to keep in mind this custom toolbar might not work, although it is supposed to in 2007, not sure about 2010. Can only say I can't see it in either....

I would like, if you want, to know if pasting in an empty db the above code in a module and running function MenuPopupX you see your menus..(there are some Debug.print...)

tks