Solved

Acces security

Posted on 2013-10-31
7
152 Views
Last Modified: 2013-11-22
hello experts, I have a form which records the set up of a new process. I also a login table. after the login an Index form opens and the user can open the form from there, what I want is to have the form open and show only certain field based on the login ID, if needed the db can be uploaded.  I'm fairly new at this.
0
Comment
Question by:Vitoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614116
There are many valid ways to do this.

One is to store he login ID in a Public variable
Create a Public variable in a module:

    Public lngpubLogInID as long

Then also create a public function to retrieve it:

    Public Function GetLogInID() as long
        GetLogInID=lngpubLogInID
End Function

Then use this variable to filter the form:
    DoCmd.OpenForm "YourFormName", , , "LoginIDFieldOnTheForm=" & GetLogInID()

But ultimately you will have to either try applying this to your specific database, or post a simple sample of your database for review.
But what I posted should get you going.
;-)

JeffCoachman
0
 

Author Comment

by:Vitoe
ID: 39614379
it seems to work but when the form opens it still displays all, what I need is to enable only certain field and the rest are disabled based on the logon ID
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614449
Oh....
This is a "Field" based restriction..

Then the "proper" way to do this would be to create another "many-to-many" table to store the allowed/disallowed LogInID's

The "easy" approach is to simply only load the allowed fields in the form.

On the Open event of the form do something like this:
Dim strRecSrc as string
If GetLogInID()=1 then
    'Here you would list the fields you want LoginID1 to see, for example: fld2, fld3
    strRecSrc ="SELECT fld2, fld3"
elseif  GetLogInID()=2 then
     'Here you would list the fields you want LoginID2 to see
    strRecSrc ="SELECT fld1, fld3"
elseif  GetLogInID()=3 then
     'Here you would list the fields you want LoginID3 to see
    strRecSrc ="SELECT fld1, fld2"
....
....
End if

strRecSrc =strRecSrc & " FROM YourTable"

Me.recordsource=strRecSrc
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39614456
...and to be clear, you can use code to open the form normally:
 DoCmd.OpenForm "YourFormName"

And this "field filter" code should still work
0
 

Author Comment

by:Vitoe
ID: 39614473
how do I do the "many-to-many" table to store the allowed/disallowed LogInID's?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39615899
<how do I do the "many-to-many" table to store the allowed/disallowed LogInID's? >
This will be to much to explain if you are new to database design.
What I posted above should work in the mean time...

But you can research the concept here:
http://en.tekstenuitleg.net/articles/software/create-a-many-to-many-relationship-in-access
You can also see this to make the process a bit simpler:
http://office.microsoft.com/en-us/access-help/guide-to-multivalued-fields-HA001233722.aspx

JeffCoachman
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39623826
You could also show/hide the controls on the form, based on the login of the user. I'm not sure how you're storing those values, but in general I use a hidden form to store things like this. The process is something like:

1) User opens database and logs in through my custom form
2) After login, run code to determine the "level" of the user, and place that value in a textbox on the login form named "txUserLevel"
2) That custom form is then HIDDEN (not closed), and I can then refer to it from other objects in my database.

In a form where I want to hide certain controls, I use the Tag method:

1) For each control I wish to hide, I add a value to the Tag property which denotes the lowest "level" of user who can interact with it. In your case, since you will apparently have multiple "levels" of users, you might want to devise a strategy that defines the highest "level" of user (for example, and Admin) down to the lowest "level" of user (for example, a Readonly user). Perhaps an Admin user would have a level of 5, and a Readonly user would have a level of 1
2) I then loop through my controls and examine the Tag property, and take action based on that:

Dim ctl As Control

On Error Resume Next
For each ctl in Me.Controls
  If CInt(ctl.Tag) < Forms("MyHiddenForm").txUserLevel Then
      ctl.Enabled = False
      ctl.Locked = True
  Else
    ctl.Enabled = False
    ctl.Locked = True
  End If
Next ctl

This code would be placed in the Open or Load event of your form.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 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