set properties of control programmatically

Applek
Applek used Ask the Experts™
on
Hi.

I'm quite new to VB programming. By the way, how to code the properties of a menu control programatically? For instance, i extract the menu name from a Database where this user is only allowed to access this menu component,say menu1. So inside the program, i exract the menu1 and set the Enabled properties to be true.
snippet code:

Do While Not rs.EOF
       frmMain.Control = rs.Fields("AccessibleMenu")
       'frmMain.Menu1.Properties("Enabled") = True
       'frmMain.rs.Fields(0).Enabled = True
Loop

I think the above code was wrong since the type of fields cannot be match. Is there any way to cast the data type to be control type? I'm not sure VB can cast or not. Pls advise.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Put each menu Item into a collection:
Dim colMenuItems As VBA.Collection

Private Sub form_load()
   
    Dim oMenu As Menu
    Set colMenuItems = New VBA.Collection
   
    'Add each menu item to the collection
    Set oMenu = mnuFile
    colMenuItems.Add oMenu, oMenu.Caption
   
    Set oMenu = mnuFileOpen
    colMenuItems.Add oMenu, oMenu.Caption
   
End Sub

Private Sub Command1_Click()
    Dim oMenu As Menu

    Do While Not rs.EOF
      'NOTE Error handling must be in here in case the item isn't in the collection.  Also note that the Menu caption can contain & and this will work ONLY if the & is included in the DB, if not, strip it out
      set oMenu =colMenuItems(rs.Fields("AccessibleMenu"))
      oMenu.Enabled = True

    Loop

End Sub

Author

Commented:
The mnuFile and mnuFileOpen is PopUpMenu data type?

Author

Commented:
The mnuFile and mnuFileOpen is PopUpMenu data type?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
ok. Thanks twalgrave.

can i create my own collection and inserting menu caption during the while loop? By the way, there is another error:

Object variable or with block variable not set when (1)

Dim oMenu As Menu
Set colMenuItems = New VBA.Collection
Set oMenu = mnuFileOpen   --- I'm not sure about this work?
colMenuItems.Add oMenu, oMenu.Caption   --- (1)

Do While Not rs.EOF
     set oMenu = colMenuItems(rs.Fields("AccessibleMenu"))
     oMenu.Enabled = True
loop

Pls help. Thank you very much!

Author

Commented:
ok. Thanks twalgrave.

can i create my own collection and inserting menu caption during the while loop? By the way, there is another error:

Object variable or with block variable not set when (1)

Dim oMenu As Menu
Set colMenuItems = New VBA.Collection
Set oMenu = mnuFileOpen   --- I'm not sure about this work?
colMenuItems.Add oMenu, oMenu.Caption   --- (1)

Do While Not rs.EOF
     set oMenu = colMenuItems(rs.Fields("AccessibleMenu"))
     oMenu.Enabled = True
loop

Pls help. Thank you very much!

Author

Commented:
ok. Thanks twalgrave.

can i create my own collection and inserting menu caption during the while loop? By the way, there is another error:

Object variable or with block variable not set when (1)

Dim oMenu As Menu
Set colMenuItems = New VBA.Collection
Set oMenu = mnuFileOpen   --- I'm not sure about this work?
colMenuItems.Add oMenu, oMenu.Caption   --- (1)

Do While Not rs.EOF
     set oMenu = colMenuItems(rs.Fields("AccessibleMenu"))
     oMenu.Enabled = True
loop

Pls help. Thank you very much!
Applek,

replace the mnuFileOpen in the code with the name of the menu in YOUR application.

Object variable or with block variable not set when (1)

Dim oMenu As Menu
Set colMenuItems = New VBA.Collection
Set oMenu = mnuFileOpen   **** change mnuFileOpen with your menu name
'You should also test for object is nothing
if not oMenu is nothing then
    colMenuItems.Add oMenu, oMenu.Caption   --- (1)
endif

Do While Not rs.EOF
    set oMenu = colMenuItems(rs.Fields("AccessibleMenu"))
    oMenu.Enabled = True
loop


You should establish the menus before the while loop.  Its not impossible to do it in the while loop, just makes things more difficult.

Author

Commented:
Well, thanks again, twalgrave .

I have tried it out several times. it's failure and there is another error on (1): 'Invalid Procedure Call/Argument

Do While Not rs.EOF
  set oMenu = colMenuItems(rs.Fields("AccessibleMenu"))-(1)
  oMenu.Enabled = True
loop

Just to let you know that the "AccessibleMenu" is menu name for the form(instead of menu.caption). In that case, i change it to menu.name for the collection:
 
    colMenuItems.Add oMenu, oMenu.Name

However, it's failure too. Apologize for my stupid question since it was my first time to use this collection object. Pls help. Thank you.
   

Seems right to me.  However, if rs.Fields("AccessibleMenu") returns an empty string or a value that is not in the menu list (which I suspect it is)...You will get that error.  So Change to:
Do While Not rs.EOF
 
 if sAccessibleMenu > "" then
     on error resume next

     set oMenu = colMenuItems(rs.Fields("AccessibleMenu"))
     if err.number = 5 then
        msgbox rs.Fields("AccessibleMenu") & "Is not found!"
        err.clear
     else
        oMenu.Enabled = True

     endif
 endif
  rs.Movenext
loop

Also, make sure you have the "Break on unhandled errors" option selected in Tools...Options...General...Error Trapping.

Author

Commented:
ok. twalgrave, I have tried it again. It does go through the error No.5, by the way, what is the error No.5?

During the running of the program, it does retrieve the menu name from the database but there is an empty string followed by the menu name. The
colMenuItems(rs.Fields("AccessibleMenu")) is an item index for this collection, right?

Anyway, do you have any idea of it?
So if I'm understanding you correctly, you are saying the when the menu comes back from the DB it looks like:

" MyMenuName"  with the space in front of it right?

If that's the case you need to change this:
set oMenu = colMenuItems(rs.Fields("AccessibleMenu"))

to

set oMenu = colMenuItems(trim$(rs.Fields("AccessibleMenu")))

Author

Commented:
sorry. It is a space after the menu name:

Msgbox : 'MenuItem1     is Not Found'

I have tried to use TRIM$ or TRIM...but it's failure.

By the way, twalgrave. Can you provide me some good web site to look through the collection type. I have surfed several times for the good tutorial from web site, but it's hardly to get it. How do you know the Error No.5?

Pls guide me and do appreciate with your help. Thank you.

Author

Commented:
Hi, twalgrave. It should be ok for the
rs.Fields("AccessibleMenu") after i put the TRM$. However, it still goes through the error No.5 and this time, the msgbox was out: 'MenuItem1is not found'

Pls help me.
For info on the collection object, see the VB help file and search for "The Visual Basic Collection Object"


Let me see what you have for code.  I need to see where you are creating the collection, adding the menus to the collection and the code you are running.  

I need to go within the next half hour for the night.  If you can get me the info requested within the next 15 minutes I can help tonight, if not, then tomorrow.

Author

Commented:
ok. Just try it out:

All code are in the same function:

 Dim colMenuItems As VBA.Collection
 Dim oMenu As Menu
 Set colMenuItems = New VBA.Collection
 Set oMenu = Menu1
 
 If Not (oMenu Is Nothing) Then
      colMenuItems.Add oMenu, oMenu.Name
 End If
 
 Set rs = New ADODB.Recordset
   
 strSQL = "SELECT AccessibleMenu FROM TblAccessRight WHERE  UserID = '" & gUserID & "'"
 rs.Open strSQL, cn
 Do While Not rs.EOF
   If (rs.Fields("AccessibleMenu")) > "" Then
   Set oMenu = colMenuItems(Trim$(rs.Fields
    ("AccessibleMenu")))
 
  If Err.Number = 5 Then
       MsgBox (Trim$(rs.Fields("AccessibleMenu"))) & "Is
       not found!"
       Err.Clear
  Else
       oMenu.Enabled = True
    End If
  End If
 rs.MoveNext
 Loop
 
rs.Close
 
   
Thank you  in advance!
OK, here's your problem...You have only loaded a single menu item into the collection so when the db comes back with 15 items, 14 of them will not find the menu item, and 1 will

All code are in the same function:


Here is where you need to add ALL of your menus.

Set oMenu = Menu1

If Not (oMenu Is Nothing) Then
     colMenuItems.Add oMenu, oMenu.Name
End If


Set oMenu = Menu2

If Not (oMenu Is Nothing) Then
     colMenuItems.Add oMenu, oMenu.Name
End If


Set oMenu = Menu3

If Not (oMenu Is Nothing) Then
     colMenuItems.Add oMenu, oMenu.Name
End If
 

Set oMenu = Menu4

If Not (oMenu Is Nothing) Then
     colMenuItems.Add oMenu, oMenu.Name
End If

Set oMenu = Menu5

If Not (oMenu Is Nothing) Then
     colMenuItems.Add oMenu, oMenu.Name
End If

and so on.

Also don't forget to include all sub-menu items.

Author

Commented:
Yeah, thanks again twalgrave. I know the problem. I realize that this is not so flexible for the program too. Everytime i create a menu, i have to add the menu name to the oMenu. Instead, i retrieve the menu name from the database and set it to oMenu, it's failure since the menu name can not be assigned in run time. Do you have any idea so that it make the program more flexible? or either we may choose another alternative solution?
First, let me correct you. <Everytime i create a menu, i have to add the menu name to the oMenu>  is incorrect.  Everytime you create a menu, you add it to the collection, not to oMenu.  oMenu is only a temporary variable used to load a menu into the collection.  This technique is VERY flexible.  You can add items to the collection and remove them at run-time as well as in design time.  It doesn't get any more flexible than that.

If you have 10 menu items to start with at design time, add them in statically as shown.

If you add 50 or 100 more menu items while your program is running, just make sure you add them to the collection as you add the menu item.

Now, let's say you wanted to fetch from the database all the names of the menus at startup, but your menus will change during processing.  What you could do is to put those menu names in their own collection (a different one than I showed). Then when you draft up a new menu item at run time, check to see if that menu name is in the collection of menu names that can be enabled.  If not, disable it, if so, enable it.

The least flexible part of how you are handling the menuing is the fact that you have the menus in the database.  The database now has a finite set of menus that can be used.

I understand you are struggling with the concept of a collection, so I suggest reading the VB help file on the issue.  Search for collection.

I can give you a quick breakdown of what a collection is here.  A collection is nothing more than a place to keep objects all together.  They don't have to be the same type of object, but they generally should be.  For example: if I had 5 marbles each with a different color (0r 3 with 1 color and 2 with another), a collection would be like the shoe-box that I keep the marbles in.  It is a convenient container for those items.  Just like with the shoebox, I can add items to the collection, remove them, and name them.  I can retrieve a count of the items in the collection as well.



Author

Commented:
Thanks twalgrave. I solve the problem already. You did help me alot. Do appreciate with your help. Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial