Build dynamic menu with VBA in MS Access?

Question

I am building a database in MS Access which will be used by multiple users. Because of the sensitivity of the data, some users will have more access than others (higher / lower user privileges)

What I am trying to do is dynamically build the menu on the switchboard depending on the current users rights.

I've built a function thus far which takes the current users rights as a string seperated by commas. The function then splits the string into an array and iterrates through each value to make menu items visible. This portion works great...

However, what I need to do now is re-position the menu items on the switchboard and resize the menu itself dependent on:

a) How many menu items there are, and;
b) Which menu items are present.

I'm really stuck on this one, and I could use some help from you guys!

Thank you in advance for your advice.

Answer 1

As you iterate through the array to determine which items are going to be visible, keep a counter that increments only when a visible item is found;



Then, use that counter to calculate the Top property of the item(s) to be displayed (I'm assuming a small square command button with label)



Remember in Access, the unit of distance on a form is the twip, 1440 twips to the inch, 567 twips to the centimeter.



(Put the code in the OnLoad event handler for the Switchboard)



That should give you a contiguous list with no gaps, no matter which items are visible or invisible.



Hope that helps

Answer 2

Depends on what you are doing and what format you want the information to be in once you have analyzed it. If you want to use the information in Access, that means converting everything from a spreadsheet to a relational database. Without known what kind of data you are look at, I have no clue which is the better format for you. Usually a database is used for things like names, address, inventories, and similar "non-numeric" information. If your information is of that type, Access may be the better choice. If your information is "numbers" (sells reports, bank accounts, etc) then Excel is probably the better option. It has more math features included. If your information is already in Excel, and you want the final results to be an Excel spreadsheet, then the VBA in Excel is probably the better way to go. If you want to information to be become a relational database instead of a spreadsheet, then Access is the answer.

Answer 3

I think it's easier to simulate the menu using a listbox. It's very easy to populate via SQL or VBA.



You can also resize the Form, me.detail.height = lb.top + (cItemSize * lb.ListCount). I would obtain the cItemSize "distance" by temporarily aligning two labels with the listbox and calc. label2.top - label1.top.



I can't help you with the switchboard, I had a problem with it in 1997.

Answer 4

The easiest thing to do is create multiple switchboards based on user privileges and open the appropriate switchboard via VBA on startup.



http://msdn.microsoft.com/en-us/library/aa196599(office.11).aspx