• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 392
  • Last Modified:

MS Access Custom Menu using macros

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
Blueice13085
Asked:
Blueice13085
  • 6
  • 5
  • 2
  • +2
1 Solution
 
t_hungateCommented:
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
 
Jeffrey CoachmanCommented:
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
 
Helen FeddemaCommented:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
Dale FyeCommented:
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
 
Blueice13085Author Commented:
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
 
Blueice13085Author Commented:
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
 
Jeffrey CoachmanCommented:

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
 
Blueice13085Author Commented:
ah no this is way off from what i am looking for plz review my db
0
 
t_hungateCommented:
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
 
Blueice13085Author Commented:
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
 
Jeffrey CoachmanCommented:
<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
 
Blueice13085Author Commented:
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
 
Jeffrey CoachmanCommented:
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
 
Jeffrey CoachmanCommented:
^Obviously you will have to change your login table to reflect numeric access levels.
(and adjust the public variable as well)

;-)
0
 
Jeffrey CoachmanCommented:
OK, great.

;-)

jeff
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now