Solved

MS Access Custom Menu using macros

Posted on 2010-09-19
15
361 Views
Last Modified: 2013-11-27
i have taken all the default menus in ms access database off and made my own menu using macros instead of a switchboard, what i am trying to do is make it when a user logs on the database (custom log on form) it only allows them to use certain tabs on the menu for example admin level 3 manager level 2 employee level 1, have a tab called tools then drops down to some options how after log on can i have that menu item be visible but not able to be used level 1 users. i am using A2007 with A2003 format database
0
Comment
Question by:Blueice13085
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 10

Expert Comment

by:t_hungate
ID: 33712359
Could you post a sample of your DB here?  It would make it much easier to see what you are trying to do.

I have done something similar to this but I had to use forms that had different user accessible controls, based on user level.  I am not sure if you can do it for a menu, unless you have three different menus, one for each level, then load the appropriate menus base on level.

If you get a chance to load your DB, I'll take a look at it and see what I can come up with.

TLH
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33712800
There are many ways that this can be done depending of your exact requirements.
Here is a very basic example of one one those ways...

UN: Bill
PW: Bill
Bill's role is "Admin" and he should be able to see the "Protected info" tab

UN: Tina
PW: Tina
Tina' role is "User" and he should NOT be able to see the "Protected info" tab

Study it carefully
I am sure you can adapt this to work in your database

;-)

JeffCoachman
Access-EEQ26484086Basic-LoginLog.mdb
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 33718882
Are you talking about actual menus or Ribbons?  Access 2007 uses Ribbons, which are only minimally editable.  Old menus (created in earlier versions of Access) are carried over to the misleadingly named Addins Ribbon, but that is an awkward interface.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33718990
Helen,

Sounds like the OP is really talking about a multi-function switchboard, not actually menus or ribbons.  I think what they have is a tab control with one tab for each role, and command buttons that are specific to the role, where only one tab should be visible, based upon the users login role.
0
 

Author Comment

by:Blueice13085
ID: 33721978
no i am using a menu which i am using macros to create them and think it is placed in the add in spot Genius that is a form i will upload an example what what i need thanks
0
 

Author Comment

by:Blueice13085
ID: 33722076
alright after log on i dont want the user level to be able to access the Expenses tab on the top want it to be visible but grayed out only admin can access it and manager can only access certain tab from expenses like the first 2 and the rest will be grayed out? any way this was all made in A2007 i didnt put any codes behind the logon form just took some sample data from ms access samples and added the menu and log on form and table must press shift when open to by pass the start up options that is left open but first time must not use shift to see how it works i have shift disabled in my current db
Menu-Sample.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33722241

1. You cannot "Grey out" a tab.
If you set the enabled property of a tab to No, the "Controls" on the tab will be greyed out, not the tab (or the tab caption) itself.
2. You cannot easily change the Tab caption color either.
3. There is no direct event to detect what tab is/was clicked.

If you want this effect then the easiest way might be to "Hide" the control on this tab.
Unless you want to add even more code to change the tab color...
see here for some tips on changing a tab's color:
http://www.fontstuff.com/access/acctut12.htm
http://www.lebans.com/tabcolors.htm

You can also Hide the secured tab altogether, as in my first sample

Here is a revised sample, with both the Hidden tab and Hidden controls option.
Again, logging in as Bill will give you full access.
Logging in as tina will NOT alloy you access to the info on the Protected Info tab.

Study it carefully.
I am sure you will be able to adapt this to work in your database.

;-)

JeffCoachman

Access-EEQ26484086Basic-LoginLog.mdb
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Blueice13085
ID: 33722414
ah no this is way off from what i am looking for plz review my db
0
 
LVL 10

Expert Comment

by:t_hungate
ID: 33722449
Just so I am clear here.  You are wanting to set some controls on the "Expense List" form so that only Admin can access them?  Are you talking about the four buttons at the top for New Vehicle, New Expense, Future Expense and Email?

Also, you have no code or procedure to control you login process.  At least not in the sample you supplied.  Where are you storing the login credentials?
How are you passing them to other forms?

From what I am seeing on the "Expense List" form you could easily disable the controls based on user access type or level.

Need more info.  Exactly what forms you need these features on, and what controls on the form that you want disabled or "grayed out".

TLH
0
 

Author Comment

by:Blueice13085
ID: 33722459
none i need these codes on the menu that i made please review the macros that i created and when the database starts up there are some tabs at the top of the scree that i used the macros to make thats what i want it to block is the custom menu that i made??
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33722614
<here are some tabs at the top of the scree>
Your terminology is a bit confusing, ...those are not "Tabs", they are "Menu Items".

Although you can use code to disable Menu Items, IMHO a Form would be an easier interface to use.
You can disable "buttons" quite easily.

Sample 3

UN: Bill
PW: Bill
Bill's role is "Admin" and he should be able to see all the buttons.

UN: Tina
PW: Tina
Tina's role is "User" and for her, the "Movies Button will be Greyed out.

JeffCoachman
Access-EEQ26484086Basic-LoginLog.mdb
0
 

Author Comment

by:Blueice13085
ID: 33724206
I have a custom menu bar in MSAccess  and this menu bar contains several submenu bars using Macros. I also have a user login screen where the users are defined by level of access such as '1' for admin, '2' for supervisor, '3' for regular users. How do i set selected menu bars to be "read-only" if the user access level is "3".

0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 33725556
Then first, you should be creating your menus with VBA, not Macros.

1. A macro (in the Access2003 format) cannot access variables, so it is difficult to have code that knows what the user's Name/Role/Security Level is.
(The Access 2007 format can do this, but that is another topic altogether)
2. With a Macro "Menu", the menu the Menu does not really exist as an object per se, thus you cannot manipulate it easily.
3. Most Access developers do not use macros beyond "Autokeys" and "Autoexec" macros.
4. You still need code like I posted to detect the user anyway, and you still need the login table to do this.
So you can see how the sample I posted still contains relevant data...

Here is a popular tutorial on creating standard menus:
http://www.jamiessoftware.tk/articles/menubars.html

If you create your menus in the traditional manner, this will be easy with code like this:


;-)

JeffCoachman

Private Sub cmdLogin_Click()
Dim rst             As New ADODB.Recordset
Dim strAttUserName  As String
Dim strAttPword     As String
Dim mnuMenu         As CommandBar

Set mnuYourMenu = CommandBars("YourMenuBarName")
strAttUserName = Nz(Me.txtUserName, "")
strAttPword = Nz(Me.txtPassword, "")

    rst.Open "SELECT UserName FROM tblLogin WHERE UserName = '" & strAttUserName & "'" & " AND Password = '" & strAttPword & "'", CurrentProject.Connection

    If Not (rst.EOF And rst.BOF) Then
        strpubLogInRole = DLookup("Role", "tblLogin", "UserName=" & "'" & strAttUserName & "'" & " AND " & "Password=" & "'" & strAttPword & "'")
        If strpubLogInRole = "Admin" Then
            'Enable the "Expenses" MenuItem.
            mnuYourMenu.Controls("Expenses").Enabled = True
        ElseIf strpubLogInRole = "User" Then
            'Disable the "Expenses" MenuItem.
            mnuYourMenu.Controls("Expenses").Enabled = False
        End If
        
        'Do something if they are a valid user, regardless of the users Role
    Else
        MsgBox "Login Falied"
        Exit Sub
    End If

rst.Close
Set rst = Nothing

End Sub

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33725669
^Obviously you will have to change your login table to reflect numeric access levels.
(and adjust the public variable as well)

;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33780200
OK, great.

;-)

jeff
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now